multiple rows insert: running

Showing posts with label running. Show all posts

Wednesday, March 28, 2012

How to estimate query governor cost setting?

To prevent a test (which can poorly written) query for running too long that
exhausts my desktop, how I can prevent it from happening? I am thinking of
setting a limit, which I can tolerate a 4 minutes window but not longer than
that.
If this helps: On the properties of "Use query governor to prevent queries
exceeding specified cost", I need to set the cost, but don't know how much.
Thanks.Hi
From BOL:
query governor cost limit Option
Use the query governor cost limit option to specify an upper limit for the
time in which a query can run. Query cost refers to the estimated elapsed
time, in seconds, required to execute a query on a specific hardware
configuration.
Regards
Mike
"C TO" wrote:
> To prevent a test (which can poorly written) query for running too long that
> exhausts my desktop, how I can prevent it from happening? I am thinking of
> setting a limit, which I can tolerate a 4 minutes window but not longer than
> that.
> If this helps: On the properties of "Use query governor to prevent queries
> exceeding specified cost", I need to set the cost, but don't know how much.
> Thanks.|||Dear Mike,
Thanks but sorry for my confusing question. I still don't know what the BOL
means by that. I do not know how to estimate the cost limitation I need to
set.
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> From BOL:
> query governor cost limit Option
> Use the query governor cost limit option to specify an upper limit for the
> time in which a query can run. Query cost refers to the estimated elapsed
> time, in seconds, required to execute a query on a specific hardware
> configuration.
> Regards
> Mike
> "C TO" wrote:
> > To prevent a test (which can poorly written) query for running too long that
> > exhausts my desktop, how I can prevent it from happening? I am thinking of
> > setting a limit, which I can tolerate a 4 minutes window but not longer than
> > that.
> >
> > If this helps: On the properties of "Use query governor to prevent queries
> > exceeding specified cost", I need to set the cost, but don't know how much.
> >
> > Thanks.|||Hi
If you want to limit the query to 4 minutes, (4x60 = 240), so put 240 in the
setting box.
Instead of aborting the query, rather look at why it is not performaing well.
Look at http://www.sql-server-performance.com
Regards
Mike
"C TO" wrote:
> Dear Mike,
> Thanks but sorry for my confusing question. I still don't know what the BOL
> means by that. I do not know how to estimate the cost limitation I need to
> set.
> "Mike Epprecht (SQL MVP)" wrote:
> > Hi
> >
> > From BOL:
> >
> > query governor cost limit Option
> > Use the query governor cost limit option to specify an upper limit for the
> > time in which a query can run. Query cost refers to the estimated elapsed
> > time, in seconds, required to execute a query on a specific hardware
> > configuration.
> >
> > Regards
> > Mike
> >
> > "C TO" wrote:
> >
> > > To prevent a test (which can poorly written) query for running too long that
> > > exhausts my desktop, how I can prevent it from happening? I am thinking of
> > > setting a limit, which I can tolerate a 4 minutes window but not longer than
> > > that.
> > >
> > > If this helps: On the properties of "Use query governor to prevent queries
> > > exceeding specified cost", I need to set the cost, but don't know how much.
> > >
> > > Thanks.|||C TO wrote:
> To prevent a test (which can poorly written) query for running too
> long that exhausts my desktop, how I can prevent it from happening? I
> am thinking of setting a limit, which I can tolerate a 4 minutes
> window but not longer than that.
> If this helps: On the properties of "Use query governor to prevent
> queries exceeding specified cost", I need to set the cost, but don't
> know how much.
> Thanks.
You could also set a query timeout from Query Analyzer or your
application for 4,000ms which forces the command to abort if it's not
completed within the allotted time.
--
David Gugick
Imceda Software
www.imceda.com

How to estimate query governor cost setting?

Wednesday, March 21, 2012

How to enable a domain account to change service account?

Hi,
We're running two SQL server 2000, SP3 A and B. On A, the service account
for both the SQL server and agent is a domain account. On B, the service
account is local system account. On A in enterprise manager, my personal
domain account can change the service account to whatever I want. But on B,
in security of enterprise manager, the 'Startup service account' grays out
for my domain account. How should I allow my domain account to modify
startup service account? Is it something that needs to be done in the active
directory or on the database server? How?
Thanks much in advance for any help.
BingHi
"bing" wrote:
> Hi,
> We're running two SQL server 2000, SP3 A and B. On A, the service account
> for both the SQL server and agent is a domain account. On B, the service
> account is local system account. On A in enterprise manager, my personal
> domain account can change the service account to whatever I want. But on B,
> in security of enterprise manager, the 'Startup service account' grays out
> for my domain account. How should I allow my domain account to modify
> startup service account? Is it something that needs to be done in the active
> directory or on the database server? How?
> Thanks much in advance for any help.
> Bing
Are you a local administrator on Server B?
John|||"John Bell" wrote:
> Hi
> "bing" wrote:
> > Hi,
> >
> > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > for both the SQL server and agent is a domain account. On B, the service
> > account is local system account. On A in enterprise manager, my personal
> > domain account can change the service account to whatever I want. But on B,
> > in security of enterprise manager, the 'Startup service account' grays out
> > for my domain account. How should I allow my domain account to modify
> > startup service account? Is it something that needs to be done in the active
> > directory or on the database server? How?
> >
> > Thanks much in advance for any help.
> >
> > Bing
> Are you a local administrator on Server B?
> John
How should I tell?
Bing|||"bing" wrote:
>
> "John Bell" wrote:
> > Hi
> >
> > "bing" wrote:
> >
> > > Hi,
> > >
> > > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > > for both the SQL server and agent is a domain account. On B, the service
> > > account is local system account. On A in enterprise manager, my personal
> > > domain account can change the service account to whatever I want. But on B,
> > > in security of enterprise manager, the 'Startup service account' grays out
> > > for my domain account. How should I allow my domain account to modify
> > > startup service account? Is it something that needs to be done in the active
> > > directory or on the database server? How?
> > >
> > > Thanks much in advance for any help.
> > >
> > > Bing
> >
> > Are you a local administrator on Server B?
> >
> > John
> How should I tell?
> Bing
Never mind. I figured it out.|||"bing" wrote:
>
> "bing" wrote:
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > "bing" wrote:
> > >
> > > > Hi,
> > > >
> > > > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > > > for both the SQL server and agent is a domain account. On B, the service
> > > > account is local system account. On A in enterprise manager, my personal
> > > > domain account can change the service account to whatever I want. But on B,
> > > > in security of enterprise manager, the 'Startup service account' grays out
> > > > for my domain account. How should I allow my domain account to modify
> > > > startup service account? Is it something that needs to be done in the active
> > > > directory or on the database server? How?
> > > >
> > > > Thanks much in advance for any help.
> > > >
> > > > Bing
> > >
> > > Are you a local administrator on Server B?
> > >
> > > John
> >
> > How should I tell?
> >
> > Bing
> Never mind. I figured it out.
Well, I figured out how to figure out if my personal domain account is in
the local Administrators group on the database server. Yes, it is. But my
original question still remains. Now that, my domain account is a local
administrator on both the SQL server 2000 A and B, why I can change the
service account on A but not on B (service account section in EM->Security
grays out). What else should I check?
Thanks,
Bing|||Hi
"bing" wrote:
>
> "bing" wrote:
> >
> >
> > "bing" wrote:
> >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > "bing" wrote:
> > > >
> > > > > Hi,
> > > > >
> > > > > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > > > > for both the SQL server and agent is a domain account. On B, the service
> > > > > account is local system account. On A in enterprise manager, my personal
> > > > > domain account can change the service account to whatever I want. But on B,
> > > > > in security of enterprise manager, the 'Startup service account' grays out
> > > > > for my domain account. How should I allow my domain account to modify
> > > > > startup service account? Is it something that needs to be done in the active
> > > > > directory or on the database server? How?
> > > > >
> > > > > Thanks much in advance for any help.
> > > > >
> > > > > Bing
> > > >
> > > > Are you a local administrator on Server B?
> > > >
> > > > John
> > >
> > > How should I tell?
> > >
> > > Bing
> >
> > Never mind. I figured it out.
> Well, I figured out how to figure out if my personal domain account is in
> the local Administrators group on the database server. Yes, it is. But my
> original question still remains. Now that, my domain account is a local
> administrator on both the SQL server 2000 A and B, why I can change the
> service account on A but not on B (service account section in EM->Security
> grays out). What else should I check?
> Thanks,
> Bing
As a local administrator you should have enough permissions. Were you
connected to the server itself?
John|||> As a local administrator you should have enough permissions. Were you
> connected to the server itself?
> John
I mean logged on locally to the server in question!
John|||"John Bell" wrote:
> Hi
> "bing" wrote:
> >
> >
> > "bing" wrote:
> >
> > >
> > >
> > > "bing" wrote:
> > >
> > > >
> > > >
> > > > "John Bell" wrote:
> > > >
> > > > > Hi
> > > > >
> > > > > "bing" wrote:
> > > > >
> > > > > > Hi,
> > > > > >
> > > > > > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > > > > > for both the SQL server and agent is a domain account. On B, the service
> > > > > > account is local system account. On A in enterprise manager, my personal
> > > > > > domain account can change the service account to whatever I want. But on B,
> > > > > > in security of enterprise manager, the 'Startup service account' grays out
> > > > > > for my domain account. How should I allow my domain account to modify
> > > > > > startup service account? Is it something that needs to be done in the active
> > > > > > directory or on the database server? How?
> > > > > >
> > > > > > Thanks much in advance for any help.
> > > > > >
> > > > > > Bing
> > > > >
> > > > > Are you a local administrator on Server B?
> > > > >
> > > > > John
> > > >
> > > > How should I tell?
> > > >
> > > > Bing
> > >
> > > Never mind. I figured it out.
> >
> > Well, I figured out how to figure out if my personal domain account is in
> > the local Administrators group on the database server. Yes, it is. But my
> > original question still remains. Now that, my domain account is a local
> > administrator on both the SQL server 2000 A and B, why I can change the
> > service account on A but not on B (service account section in EM->Security
> > grays out). What else should I check?
> >
> > Thanks,
> >
> > Bing
> As a local administrator you should have enough permissions. Were you
> connected to the server itself?
> John
Thanks much for taking time helping.
Yes, I logged on both A and B using my domain account via remote desktop
connection. I can change the service account in control panel->services.
But seems like that method is discouraged. From the advices I've gathered so
far, changing service account in EM should be the right way to go.
Bing|||Hi
"bing" wrote:
>
> "John Bell" wrote:
> > Hi
> >
> > "bing" wrote:
> >
> > >
> > >
> > > "bing" wrote:
> > >
> > > >
> > > >
> > > > "bing" wrote:
> > > >
> > > > >
> > > > >
> > > > > "John Bell" wrote:
> > > > >
> > > > > > Hi
> > > > > >
> > > > > > "bing" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > >
> > > > > > > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > > > > > > for both the SQL server and agent is a domain account. On B, the service
> > > > > > > account is local system account. On A in enterprise manager, my personal
> > > > > > > domain account can change the service account to whatever I want. But on B,
> > > > > > > in security of enterprise manager, the 'Startup service account' grays out
> > > > > > > for my domain account. How should I allow my domain account to modify
> > > > > > > startup service account? Is it something that needs to be done in the active
> > > > > > > directory or on the database server? How?
> > > > > > >
> > > > > > > Thanks much in advance for any help.
> > > > > > >
> > > > > > > Bing
> > > > > >
> > > > > > Are you a local administrator on Server B?
> > > > > >
> > > > > > John
> > > > >
> > > > > How should I tell?
> > > > >
> > > > > Bing
> > > >
> > > > Never mind. I figured it out.
> > >
> > > Well, I figured out how to figure out if my personal domain account is in
> > > the local Administrators group on the database server. Yes, it is. But my
> > > original question still remains. Now that, my domain account is a local
> > > administrator on both the SQL server 2000 A and B, why I can change the
> > > service account on A but not on B (service account section in EM->Security
> > > grays out). What else should I check?
> > >
> > > Thanks,
> > >
> > > Bing
> >
> > As a local administrator you should have enough permissions. Were you
> > connected to the server itself?
> >
> > John
> Thanks much for taking time helping.
> Yes, I logged on both A and B using my domain account via remote desktop
> connection. I can change the service account in control panel->services.
> But seems like that method is discouraged. From the advices I've gathered so
> far, changing service account in EM should be the right way to go.
> Bing
If the account you are logged into the server with is a local windows
administrator then it should be fine and you should be able to change the
service account. To access the property pages you need to be a SQL Server
sysadmin. I assume that SELECT @.@.SERVERNAME will return the same name the
machine, you don't have any aliases set up and the server is registered as
(LOCAL) ?
John|||"John Bell" wrote:
> Hi
> "bing" wrote:
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > "bing" wrote:
> > >
> > > >
> > > >
> > > > "bing" wrote:
> > > >
> > > > >
> > > > >
> > > > > "bing" wrote:
> > > > >
> > > > > >
> > > > > >
> > > > > > "John Bell" wrote:
> > > > > >
> > > > > > > Hi
> > > > > > >
> > > > > > > "bing" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > >
> > > > > > > > We're running two SQL server 2000, SP3 A and B. On A, the service account
> > > > > > > > for both the SQL server and agent is a domain account. On B, the service
> > > > > > > > account is local system account. On A in enterprise manager, my personal
> > > > > > > > domain account can change the service account to whatever I want. But on B,
> > > > > > > > in security of enterprise manager, the 'Startup service account' grays out
> > > > > > > > for my domain account. How should I allow my domain account to modify
> > > > > > > > startup service account? Is it something that needs to be done in the active
> > > > > > > > directory or on the database server? How?
> > > > > > > >
> > > > > > > > Thanks much in advance for any help.
> > > > > > > >
> > > > > > > > Bing
> > > > > > >
> > > > > > > Are you a local administrator on Server B?
> > > > > > >
> > > > > > > John
> > > > > >
> > > > > > How should I tell?
> > > > > >
> > > > > > Bing
> > > > >
> > > > > Never mind. I figured it out.
> > > >
> > > > Well, I figured out how to figure out if my personal domain account is in
> > > > the local Administrators group on the database server. Yes, it is. But my
> > > > original question still remains. Now that, my domain account is a local
> > > > administrator on both the SQL server 2000 A and B, why I can change the
> > > > service account on A but not on B (service account section in EM->Security
> > > > grays out). What else should I check?
> > > >
> > > > Thanks,
> > > >
> > > > Bing
> > >
> > > As a local administrator you should have enough permissions. Were you
> > > connected to the server itself?
> > >
> > > John
> >
> > Thanks much for taking time helping.
> > Yes, I logged on both A and B using my domain account via remote desktop
> > connection. I can change the service account in control panel->services.
> > But seems like that method is discouraged. From the advices I've gathered so
> > far, changing service account in EM should be the right way to go.
> >
> > Bing
> If the account you are logged into the server with is a local windows
> administrator then it should be fine and you should be able to change the
> service account. To access the property pages you need to be a SQL Server
> sysadmin. I assume that SELECT @.@.SERVERNAME will return the same name the
> machine, you don't have any aliases set up and the server is registered as
> (LOCAL) ?
> John
Hm. Sorry I don't quite understand the question. Here is what SELECT
@.@.SERVERNAME returns on A and B respectively.
A. TECUMSEH
B. HAMMERHEAD
And here are A's and B's host name in DNS:
A. pc200.dept.some.edu
B. hammerhead.dept.some.edu
Any problems with that?
Bing|||Hi
> > If the account you are logged into the server with is a local windows
> > administrator then it should be fine and you should be able to change the
> > service account. To access the property pages you need to be a SQL Server
> > sysadmin. I assume that SELECT @.@.SERVERNAME will return the same name the
> > machine, you don't have any aliases set up and the server is registered as
> > (LOCAL) ?
> >
> > John
> Hm. Sorry I don't quite understand the question. Here is what SELECT
> @.@.SERVERNAME returns on A and B respectively.
> A. TECUMSEH
> B. HAMMERHEAD
> And here are A's and B's host name in DNS:
> A. pc200.dept.some.edu
> B. hammerhead.dept.some.edu
> Any problems with that?
> Bing
It looks like Server a was renamed from TECUMSEH to PC200, but that is the
one that works? If you type SET COMPUTERNAME at a command prompt does it come
up with the same name as SELECT @.@.SERVERNAME?
If the server was renamed you would need to change the SQL Server's name
using the instructions in http://support.microsoft.com/default.aspx/kb/303774
John|||"John Bell" wrote:
> Hi
> >
> > > If the account you are logged into the server with is a local windows
> > > administrator then it should be fine and you should be able to change the
> > > service account. To access the property pages you need to be a SQL Server
> > > sysadmin. I assume that SELECT @.@.SERVERNAME will return the same name the
> > > machine, you don't have any aliases set up and the server is registered as
> > > (LOCAL) ?
> > >
> > > John
> >
> > Hm. Sorry I don't quite understand the question. Here is what SELECT
> > @.@.SERVERNAME returns on A and B respectively.
> >
> > A. TECUMSEH
> > B. HAMMERHEAD
> >
> > And here are A's and B's host name in DNS:
> >
> > A. pc200.dept.some.edu
> > B. hammerhead.dept.some.edu
> >
> > Any problems with that?
> >
> > Bing
> It looks like Server a was renamed from TECUMSEH to PC200, but that is the
> one that works? If you type SET COMPUTERNAME at a command prompt does it come
> up with the same name as SELECT @.@.SERVERNAME?
> If the server was renamed you would need to change the SQL Server's name
> using the instructions in http://support.microsoft.com/default.aspx/kb/303774
> John
Yeah, A works. B doesn't.
On A:
'set computername' returns pc200.
'SELECT @.@.SERVERNAME' returns TECUMSEH.
'select @.@.version" returns
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
5.2 (Build 3790: Service Pack 1)
On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
'select @.@.version' returns
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.2 (Build 3790: Service Pack 1)
Bing|||Hi
> Yeah, A works. B doesn't.
> On A:
> 'set computername' returns pc200.
> 'SELECT @.@.SERVERNAME' returns TECUMSEH.
> 'select @.@.version" returns
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
> 5.2 (Build 3790: Service Pack 1)
> On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
> 'select @.@.version' returns
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> NT 5.2 (Build 3790: Service Pack 1)
> Bing
>
So that does not get you any further with the original issue! Have you tried
changing the service account in services to localsystem for server B and then
see if you can change it to a domain account in EM?
John|||"John Bell" wrote:
> Hi
> > Yeah, A works. B doesn't.
> >
> > On A:
> > 'set computername' returns pc200.
> > 'SELECT @.@.SERVERNAME' returns TECUMSEH.
> > 'select @.@.version" returns
> > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
> > 5.2 (Build 3790: Service Pack 1)
> >
> > On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
> > 'select @.@.version' returns
> > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> > NT 5.2 (Build 3790: Service Pack 1)
> >
> > Bing
> >
> So that does not get you any further with the original issue! Have you tried
> changing the service account in services to localsystem for server B and then
> see if you can change it to a domain account in EM?
> John
I just tried, but it didn't make any difference.
Actually, that put the issue back to its original state. In the very
beginning, I noticed my personal domain account (in the local administrators
group) could not change the service account in EM on B. But I was able to
change the service account to a domain account in services in control panel.
This domain account is also in the local administrators group. I tried to
log on B using both my personal domain account and the desired service
account I wanted to change to, but neither works. In services, the 'Log on
As' is local system for both MSSQLSERVER and SQLSERVERAGENT now.
Bing|||Hi
"bing" wrote:
>
> "John Bell" wrote:
> > Hi
> >
> > > Yeah, A works. B doesn't.
> > >
> > > On A:
> > > 'set computername' returns pc200.
> > > 'SELECT @.@.SERVERNAME' returns TECUMSEH.
> > > 'select @.@.version" returns
> > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
> > > 5.2 (Build 3790: Service Pack 1)
> > >
> > > On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
> > > 'select @.@.version' returns
> > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> > > NT 5.2 (Build 3790: Service Pack 1)
> > >
> > > Bing
> > >
> > So that does not get you any further with the original issue! Have you tried
> > changing the service account in services to localsystem for server B and then
> > see if you can change it to a domain account in EM?
> >
> > John
> I just tried, but it didn't make any difference.
> Actually, that put the issue back to its original state. In the very
> beginning, I noticed my personal domain account (in the local administrators
> group) could not change the service account in EM on B. But I was able to
> change the service account to a domain account in services in control panel.
> This domain account is also in the local administrators group. I tried to
> log on B using both my personal domain account and the desired service
> account I wanted to change to, but neither works. In services, the 'Log on
> As' is local system for both MSSQLSERVER and SQLSERVERAGENT now.
> Bing
If you are able to log on as the local administrator or domain administrator
do these work?
John|||"John Bell" wrote:
> Hi
> "bing" wrote:
> >
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > > Yeah, A works. B doesn't.
> > > >
> > > > On A:
> > > > 'set computername' returns pc200.
> > > > 'SELECT @.@.SERVERNAME' returns TECUMSEH.
> > > > 'select @.@.version" returns
> > > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > > > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
> > > > 5.2 (Build 3790: Service Pack 1)
> > > >
> > > > On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
> > > > 'select @.@.version' returns
> > > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > > > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> > > > NT 5.2 (Build 3790: Service Pack 1)
> > > >
> > > > Bing
> > > >
> > > So that does not get you any further with the original issue! Have you tried
> > > changing the service account in services to localsystem for server B and then
> > > see if you can change it to a domain account in EM?
> > >
> > > John
> >
> > I just tried, but it didn't make any difference.
> > Actually, that put the issue back to its original state. In the very
> > beginning, I noticed my personal domain account (in the local administrators
> > group) could not change the service account in EM on B. But I was able to
> > change the service account to a domain account in services in control panel.
> > This domain account is also in the local administrators group. I tried to
> > log on B using both my personal domain account and the desired service
> > account I wanted to change to, but neither works. In services, the 'Log on
> > As' is local system for both MSSQLSERVER and SQLSERVERAGENT now.
> >
> > Bing
> If you are able to log on as the local administrator or domain administrator
> do these work?
> John
OK, I tried to log on 'Administrator' on B, Startup service account in EM
still grays out. What the heck!
Bing|||Hi
"bing" wrote:
>
> "John Bell" wrote:
> > Hi
> >
> > "bing" wrote:
> >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > > Yeah, A works. B doesn't.
> > > > >
> > > > > On A:
> > > > > 'set computername' returns pc200.
> > > > > 'SELECT @.@.SERVERNAME' returns TECUMSEH.
> > > > > 'select @.@.version" returns
> > > > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > > > > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
> > > > > 5.2 (Build 3790: Service Pack 1)
> > > > >
> > > > > On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
> > > > > 'select @.@.version' returns
> > > > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > > > > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> > > > > NT 5.2 (Build 3790: Service Pack 1)
> > > > >
> > > > > Bing
> > > > >
> > > > So that does not get you any further with the original issue! Have you tried
> > > > changing the service account in services to localsystem for server B and then
> > > > see if you can change it to a domain account in EM?
> > > >
> > > > John
> > >
> > > I just tried, but it didn't make any difference.
> > > Actually, that put the issue back to its original state. In the very
> > > beginning, I noticed my personal domain account (in the local administrators
> > > group) could not change the service account in EM on B. But I was able to
> > > change the service account to a domain account in services in control panel.
> > > This domain account is also in the local administrators group. I tried to
> > > log on B using both my personal domain account and the desired service
> > > account I wanted to change to, but neither works. In services, the 'Log on
> > > As' is local system for both MSSQLSERVER and SQLSERVERAGENT now.
> > >
> > > Bing
> >
> > If you are able to log on as the local administrator or domain administrator
> > do these work?
> >
> > John
> OK, I tried to log on 'Administrator' on B, Startup service account in EM
> still grays out. What the heck!
> Bing
If the administrator is not allowed then there is something seriously wrong!
It isn't something daft like you are not checking the This Account radio
button.
John|||Hi
"bing" wrote:
>
> "John Bell" wrote:
> > Hi
> >
> > "bing" wrote:
> >
> > >
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > > Yeah, A works. B doesn't.
> > > > >
> > > > > On A:
> > > > > 'set computername' returns pc200.
> > > > > 'SELECT @.@.SERVERNAME' returns TECUMSEH.
> > > > > 'select @.@.version" returns
> > > > > Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> > > > > Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows NT
> > > > > 5.2 (Build 3790: Service Pack 1)
> > > > >
> > > > > On B, both 'set computername' and 'SELECT @.@.SERVERNAME' hammerhead.
> > > > > 'select @.@.version' returns
> > > > > Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> > > > > Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
> > > > > NT 5.2 (Build 3790: Service Pack 1)
> > > > >
> > > > > Bing
> > > > >
> > > > So that does not get you any further with the original issue! Have you tried
> > > > changing the service account in services to localsystem for server B and then
> > > > see if you can change it to a domain account in EM?
> > > >
> > > > John
> > >
> > > I just tried, but it didn't make any difference.
> > > Actually, that put the issue back to its original state. In the very
> > > beginning, I noticed my personal domain account (in the local administrators
> > > group) could not change the service account in EM on B. But I was able to
> > > change the service account to a domain account in services in control panel.
> > > This domain account is also in the local administrators group. I tried to
> > > log on B using both my personal domain account and the desired service
> > > account I wanted to change to, but neither works. In services, the 'Log on
> > > As' is local system for both MSSQLSERVER and SQLSERVERAGENT now.
> > >
> > > Bing
> >
> > If you are able to log on as the local administrator or domain administrator
> > do these work?
> >
> > John
> OK, I tried to log on 'Administrator' on B, Startup service account in EM
> still grays out. What the heck!
> Bing
If the administrator is not allowed then there is something seriously wrong!
It isn't something daft like you are not checking the This Account radio
button?
John

How to enable a domain account to change service account?

Monday, March 12, 2012

How to Duplicate a SQL Instance

Is there a way to duplicate a SQL instance existing on one machine to
another machine ? This is for debugging purpose of some applications
running against the databases on such instance without disturbing the
production machine. I need the exact SQL instance environment with all the
databases contained. If there is no such tool available, then what do you
recommend to accomplish this ?
TIA
MacThis article will give you some pointers:
http://vyaskn.tripod.com/moving_sql_server.htm
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
news:bql5al$kqa$1@.si05.rsvl.unisys.com...
Is there a way to duplicate a SQL instance existing on one machine to
another machine ? This is for debugging purpose of some applications
running against the databases on such instance without disturbing the
production machine. I need the exact SQL instance environment with all the
databases contained. If there is no such tool available, then what do you
recommend to accomplish this ?
TIA
Mac|||Thanks Vyas. Lots of good info in there..
// Mac
"Narayana Vyas Kondreddi" <answer_me@.hotmail.com> wrote in message
news:ehEWyDcuDHA.536@.tk2msftngp13.phx.gbl...
> This article will give you some pointers:
> http://vyaskn.tripod.com/moving_sql_server.htm
> --
> HTH,
> Vyas, MVP (SQL Server)
> http://vyaskn.tripod.com/
> What hardware is your SQL Server running on?
> http://vyaskn.tripod.com/poll.htm
>
> "Mac Vazehgoo" <mahmood.vazehgoo@.unisys.com> wrote in message
> news:bql5al$kqa$1@.si05.rsvl.unisys.com...
> Is there a way to duplicate a SQL instance existing on one machine to
> another machine ? This is for debugging purpose of some applications
> running against the databases on such instance without disturbing the
> production machine. I need the exact SQL instance environment with all
the
> databases contained. If there is no such tool available, then what do
you
> recommend to accomplish this ?
> TIA
> Mac
>
>

Friday, March 9, 2012

how to drop and create Triggers from an other DB?

Hi,
I am running a stored procedure on the database DB01,
that SP got some statement looking like:
use DB02
DROP Trigger DB02_USER.BOOK_DELETE
It does not run and says:
a USE database statement is not allowed in a procedure or trigger.
If I replace the code by
DROP Trigger DB02.DB02_USER.BOOK_DELETE
then it says:
'DROP TRIGGER' does not allow specifying the database name as a prefix to
the object name.
Then how is it possible to drop a trigger in an other DB than the one the SP
is stored in?
Best regards,
Francois Malgreve.> Then how is it possible to drop a trigger in an other DB than the one the
> SP
> is stored in?
EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
A|||Thanks a lot for the information.
It works very well.
But I am still faced with a related problem. Creating triggers from an SP.
When I try to execute:
EXEC('USE DB02; CREATE Trigger CSDB_TR_SELLRATE_DELETE
On DB02_USER.BOOK_DELETE
For Delete As
Begin
--code goes here
End
')
I have an error saying:
'CREATE TRIGGER' must be the first statement in a query batch.
Do you have any trick to go around this one?
Thanks,
Francois
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OPFJxy36FHA.2524@.TK2MSFTNGP10.phx.gbl...
the
> EXEC('USE DB02; DROP TRIGGER DB02_USER.BOOK_DELETE')
> A
>

Friday, February 24, 2012

How to do sizing on Memory requirement of SQL Server 2000 ?

Hi All,
We have custom application running on SQL Server 2000, about 100
users, on WIn Server 2003. The RAM installed is 1GB, very often that
the server the client getting kicked when trying to connect to the
server from custom app. Then we add to 2GB, now its getting better.
SO, I need a formula about how to determine the memory requirement of
our SQL Server 2000 on Windows Server 2003 ?
Thank you for your info,
xtantokrislioe@.gmail.com wrote:
> Hi All,
> We have custom application running on SQL Server 2000, about 100
> users, on WIn Server 2003. The RAM installed is 1GB, very often that
> the server the client getting kicked when trying to connect to the
> server from custom app. Then we add to 2GB, now its getting better.
> SO, I need a formula about how to determine the memory requirement of
> our SQL Server 2000 on Windows Server 2003 ?
> Thank you for your info,
> xtanto
You get an error when trying to connect for the first time?
I would start by addressing your connection timeout in the application
and if you are using 1GB-2GB of RAM look at the design of your database
and make sure you have good indexes. If you need to do table scans and
load everything into memory every time you do a select you will be in
trouble RAM and CPU wise.|||Marc S wrote:
> krislioe@.gmail.com wrote:
>
> You get an error when trying to connect for the first time?
> I would start by addressing your connection timeout in the application
> and if you are using 1GB-2GB of RAM look at the design of your database
> and make sure you have good indexes. If you need to do table scans and
> load everything into memory every time you do a select you will be in
> trouble RAM and CPU wise.
Looking at the design will help. But you should run performance
monitor. One thing you could do is look at Buffer Cache and Cache hit
ratios. If they are not adequate, then you could have poor written
queries and missing or improper indexes. Possible unnecessary
voluminous pages could be filling up the Buffer Cache causing it to
flush necessary pages. Then run profiler to identify processes and sql
statements unless you have a good idea what this could already be. What
I'm getting at here is there is no formula. Environments differ. That's
why real-world multi-user database applications typically need real
DBAs. Also, what I've stated above is not all you can look at. I would
do a google search on SQL Memory and start reading. Sorry there is no
easy formula.
Good Luck!!!|||Under SQL 2000 the most SQL will be able to see is 2 Gigs when running on
Win 2003 Standard. On Windows 2003 Enterprise Edition it can see more (3
Gigs with the /3 Gig switch, and the data cache can access more memory with
AWE enabled).
SQL will utilize all available memory for its caches.
There is no formula to predict how much memory your load will require as
there are many variables.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<krislioe@.gmail.com> wrote in message
news:1157559010.101762.108420@.e3g2000cwe.googlegroups.com...
> Hi All,
> We have custom application running on SQL Server 2000, about 100
> users, on WIn Server 2003. The RAM installed is 1GB, very often that
> the server the client getting kicked when trying to connect to the
> server from custom app. Then we add to 2GB, now its getting better.
> SO, I need a formula about how to determine the memory requirement of
> our SQL Server 2000 on Windows Server 2003 ?
> Thank you for your info,
> xtanto
>|||Hi Hillary,
is there any official URLS / docs about this limitation ?
Thank you,
xtanto
Hilary Cotter menuliskan:[vbcol=seagreen]
> Under SQL 2000 the most SQL will be able to see is 2 Gigs when running on
> Win 2003 Standard. On Windows 2003 Enterprise Edition it can see more (3
> Gigs with the /3 Gig switch, and the data cache can access more memory wit
h
> AWE enabled).
> SQL will utilize all available memory for its caches.
> There is no formula to predict how much memory your load will require as
> there are many variables.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <krislioe@.gmail.com> wrote in message
> news:1157559010.101762.108420@.e3g2000cwe.googlegroups.com...

How to do sizing on Memory requirement of SQL Server 2000 ?

Hi All,
We have custom application running on SQL Server 2000, about 100
users, on WIn Server 2003. The RAM installed is 1GB, very often that
the server the client getting kicked when trying to connect to the
server from custom app. Then we add to 2GB, now its getting better.
SO, I need a formula about how to determine the memory requirement of
our SQL Server 2000 on Windows Server 2003 ?
Thank you for your info,
xtantokrislioe@.gmail.com wrote:
> Hi All,
> We have custom application running on SQL Server 2000, about 100
> users, on WIn Server 2003. The RAM installed is 1GB, very often that
> the server the client getting kicked when trying to connect to the
> server from custom app. Then we add to 2GB, now its getting better.
> SO, I need a formula about how to determine the memory requirement of
> our SQL Server 2000 on Windows Server 2003 ?
> Thank you for your info,
> xtanto
You get an error when trying to connect for the first time?
I would start by addressing your connection timeout in the application
and if you are using 1GB-2GB of RAM look at the design of your database
and make sure you have good indexes. If you need to do table scans and
load everything into memory every time you do a select you will be in
trouble RAM and CPU wise.|||Marc S wrote:
> krislioe@.gmail.com wrote:
> > Hi All,
> >
> > We have custom application running on SQL Server 2000, about 100
> > users, on WIn Server 2003. The RAM installed is 1GB, very often that
> > the server the client getting kicked when trying to connect to the
> > server from custom app. Then we add to 2GB, now its getting better.
> >
> > SO, I need a formula about how to determine the memory requirement of
> > our SQL Server 2000 on Windows Server 2003 ?
> >
> > Thank you for your info,
> > xtanto
>
> You get an error when trying to connect for the first time?
> I would start by addressing your connection timeout in the application
> and if you are using 1GB-2GB of RAM look at the design of your database
> and make sure you have good indexes. If you need to do table scans and
> load everything into memory every time you do a select you will be in
> trouble RAM and CPU wise.
Looking at the design will help. But you should run performance
monitor. One thing you could do is look at Buffer Cache and Cache hit
ratios. If they are not adequate, then you could have poor written
queries and missing or improper indexes. Possible unnecessary
voluminous pages could be filling up the Buffer Cache causing it to
flush necessary pages. Then run profiler to identify processes and sql
statements unless you have a good idea what this could already be. What
I'm getting at here is there is no formula. Environments differ. That's
why real-world multi-user database applications typically need real
DBAs. Also, what I've stated above is not all you can look at. I would
do a google search on SQL Memory and start reading. Sorry there is no
easy formula.
Good Luck!!!|||Under SQL 2000 the most SQL will be able to see is 2 Gigs when running on
Win 2003 Standard. On Windows 2003 Enterprise Edition it can see more (3
Gigs with the /3 Gig switch, and the data cache can access more memory with
AWE enabled).
SQL will utilize all available memory for its caches.
There is no formula to predict how much memory your load will require as
there are many variables.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<krislioe@.gmail.com> wrote in message
news:1157559010.101762.108420@.e3g2000cwe.googlegroups.com...
> Hi All,
> We have custom application running on SQL Server 2000, about 100
> users, on WIn Server 2003. The RAM installed is 1GB, very often that
> the server the client getting kicked when trying to connect to the
> server from custom app. Then we add to 2GB, now its getting better.
> SO, I need a formula about how to determine the memory requirement of
> our SQL Server 2000 on Windows Server 2003 ?
> Thank you for your info,
> xtanto
>|||Hi Hillary,
is there any official URLS / docs about this limitation ?
Thank you,
xtanto
Hilary Cotter menuliskan:
> Under SQL 2000 the most SQL will be able to see is 2 Gigs when running on
> Win 2003 Standard. On Windows 2003 Enterprise Edition it can see more (3
> Gigs with the /3 Gig switch, and the data cache can access more memory with
> AWE enabled).
> SQL will utilize all available memory for its caches.
> There is no formula to predict how much memory your load will require as
> there are many variables.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> <krislioe@.gmail.com> wrote in message
> news:1157559010.101762.108420@.e3g2000cwe.googlegroups.com...
> > Hi All,
> >
> > We have custom application running on SQL Server 2000, about 100
> > users, on WIn Server 2003. The RAM installed is 1GB, very often that
> > the server the client getting kicked when trying to connect to the
> > server from custom app. Then we add to 2GB, now its getting better.
> >
> > SO, I need a formula about how to determine the memory requirement of
> > our SQL Server 2000 on Windows Server 2003 ?
> >
> > Thank you for your info,
> > xtanto
> >

how to do running totals?

I know this is probably best left to the client side but I was wondering how
to do this on the SQL side too.
If the data is as follows:
CheckDate CheckNum PmtAmount
10-1-05 DEP 15.00
10-1-05 1 -5.00
10-2-05 2 -3.00
10-2-05 3 -2.00
10-2-05 5 -10.00
10-3-05 4 -1.00
10-4-05 DEP 100.00
I'd like a resultant output as follows
CheckDate CheckNum PmtAmount Running Total
10-1-05 DEP 15.00 15.00
10-1-05 1 -5.00
10.00
10-2-05 2 -3.00
7.00
10-2-05 3 -2.00 5.00
10-2-05 5 -10.00 -5.00
10-3-05 4 -1.00 -6.00
10-4-05 DEP 100 94.00
The check numbers are out of sequence on purpose to show that sorting is by
date and then by check number. The ID field in the table might be way out of
order if the user enters transactions out of sequence so it can't be
sorting.
I don't want to do this in a stored procedure but in a select statement of
some kind. Is there a way to acheive this in SQL 2k? If so, how?
Thanks,
KeithWARNING!!!! THIS IS A KLUDGE! USE ONLY FOR ACADEMIC PURPOSES!
Now, that's out of the way :)
SELECT r1.CheckDate, r1.CheckNum, r1.PmtAmount,
(SELECT SUM(PmtAmount)
FROM RunningTotal r2
WHERE (CONVERT(varchar(8), r2.CheckDate, 112) +
CASE WHEN ISNUMERIC(r2.CheckNum)=1 THEN 'Z' + r2.CheckNum
ELSE r2.CheckNum END) <= (CONVERT(varchar(8), r1.CheckDate, 112) +
CASE WHEN ISNUMERIC(r1.CheckNum)=1 THEN 'Z' + r1.CheckNum
ELSE r1.CheckNum END) ) as Total
FROM RunningTotal r1
ORDER BY (CONVERT(varchar(8), r1.CheckDate, 112) +
CASE WHEN ISNUMERIC(r1.CheckNum)=1 THEN 'Z' + r1.CheckNum
ELSE r1.CheckNum END)
Obviously, the join and the running total is dependent on the order
specified, which is why it's MUCH better to let your client application
handle this. However, if you're using something with limited
capability (say, text based emails) this will work.
Stu|||On Fri, 14 Oct 2005 20:58:17 -0400, Keith G Hicks wrote:

>I know this is probably best left to the client side but I was wondering ho
w
>to do this on the SQL side too.
>If the data is as follows:
>CheckDate CheckNum PmtAmount
>10-1-05 DEP 15.00
>10-1-05 1 -5.00
>10-2-05 2 -3.00
>10-2-05 3 -2.00
>10-2-05 5 -10.00
>10-3-05 4 -1.00
>10-4-05 DEP 100.00
>I'd like a resultant output as follows
>CheckDate CheckNum PmtAmount Running Total
>10-1-05 DEP 15.00 15.00
>10-1-05 1 -5.00
>10.00
>10-2-05 2 -3.00
>7.00
>10-2-05 3 -2.00 5.0
0
>10-2-05 5 -10.00 -5.0
0
>10-3-05 4 -1.00 -6.0
0
>10-4-05 DEP 100 94.00
>The check numbers are out of sequence on purpose to show that sorting is by
>date and then by check number. The ID field in the table might be way out o
f
>order if the user enters transactions out of sequence so it can't be
>sorting.
>I don't want to do this in a stored procedure but in a select statement of
>some kind. Is there a way to acheive this in SQL 2k? If so, how?
Hi Keith,
Here's another possibility. Untested, since you didn't post CREATE TABLE
and INSERT statements with sample data and expected output. Check out
www.aspfaq.com/5006 if you prefer a tested reply.
SELECT a.CheckDate, a.CheckNum, a.PmtAmount,
SUM(b.PmtAmount) AS "Running Total"
FROM YourTable AS a
INNER JOIN YourTable AS b
ON b.CheckDate <= a.CheckDate
AND (b.CheckDate < a.CheckDate OR b.CheckNum < a.CheckNum)
GROUP BY a.CheckDate, a.CheckNum, a.PmtAmount
ORDER BY a.CheckDate, a.CheckNum
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hi Keith G Hicks ,
I assume that the follwing will solve the problem.
Create Table tmpData
(
CheckDate DATETIME,
CheckNum INT, -- It can also be varchar and I assume that the check
number is in increasing order So it may be an identity Column
PmtAmount NUMERIC(30,5)
)
Insert into tmpData
SELECT '20050110',0,15.00
UNION ALL
SELECT '20050110',1,-5.00
UNION ALL
SELECT '20050210',2,-3.00
UNION ALL
SELECT '20050210', 3, -2.00
UNION ALL
SELECT '20050210', 5, -10.00
UNION ALL
SELECT '20050310', 4 ,-1.00
UNION ALL
SELECT '20050410',6, 100.00
SELECT * , (SELECT SUM(PMTAMOUNT) FROM tmpData Where CheckDate <=
A.CheckDate And CheckNum <= A.CheckNum )
>From tmpData A
Drop Table tmpData
With Warm regards
Jatinder Singh

How to do Running Total on a field

I am creating a Summary report and on one of the fields "Resume Exists" has values of Yes or No. I am trying to do a summary by BU on this field to say how many resumes don't exist. So I would like it to total how many No's are in the Resume Exists field.

I was thinking of having the field on the detail row, but hide it, then do a summary on the footer.

I have not been very successful and was wondering if anyone has done this and if you can provide me an example. Any help would be greatly appreciated.

If you just want to get a total per group (BU), you don't need to use running total. Just add an aggregate on the group level like this: =Sum(IIF(Fields!ResumeExists.Value="No", 1, 0)). However, if you want to have a running total in the detail row to show how many No's you've encountered so far in each BU, you need to use the running value function: =RunningValue(IIF(Fields!ResumeExists.Value="No", 1, 0), Sum, "BUGroup").

Sunday, February 19, 2012

How to do a SELECT ROW LOCK or READPAST

I have an application which use a table to figure out the next work it need to process. Application is running from multiple machines & multi-threaded also. I am using a database table, let say WorkQueue to track all works.

WorkID, WorkName, Status are the column name of the table.

Status='NEW' is a new work

Status='INPROCESS' is in process

Status='COMPLETE' is complete

I am trying to write a stored procedure, which will update single record status from NEW to INPROCESS and return WorkID. I'm trying to prevent more than one instances of the process grabbing the same record where the status = 'NEW'

Help....

I have 2 suggestions:

First Suggestion:

Create a stored procedure that will return the next work item to be processed. In the case that there has been a conflict, the stored procedure will return a certain value (I've assumed this to be -2). From the code itself you will manage that if a -2 value returns, you need to re-execute the stored procedure. If a -1 value is retuned then there are no new items. Here is the procedure I wrote:

CREATE PROCEDURE GetNextWorkItem
AS
BEGIN

-- @.WorkID will contain either the next work item to be processed
-- or -1 if there is no available 'New' work items
-- or -2 if there was a conflict and it needs to be executed again
DECLARE @.WorkID AS INT

-- Get the next 'New' work item and put the ID in @.WorkID
SELECT TOP 1 @.WorkID = WorkID
FROM WorkQueue
WHERE Status = 'New'

-- If @.@.RowCount is 0 then there are no 'New' work items
-- Return -1
IF @.@.RowCount = 0
BEGIN
SELECT -1 AS WorkID
RETURN
END

-- Update the status of the work item you've selected
-- making sure that its status is still 'New'
UPDATE WorkQueue
SET Status = 'In Progress'
WHERE WorkID = @.WorkID
AND Status = 'New'

-- If @.@.RowCount is 0 then the status of this work item was changed before you could update it
-- A -2 value will be returned and the code should execute this stored procedure again
IF @.@.RowCount = 0
SELECT -2 AS WorkID
ELSE
SELECT @.WorkID AS WorkID

END
GO

Second Suggestion:

Add a new column called WorkKey (in my example its datatype is UniqueIdenitfier). You will randomly generate a value in the stored procedure and update the WorkKey field of the first 'New' work item with this value. A certain value will indicate that there are no new work items (-1 in my procedure). Here is the procedure I wrote:

CREATE PROCEDURE GetNextWorkItem
AS
BEGIN

-- @.WorkKey is a temporary variable that you will use to select the work item that you will process next
DECLARE @.WorkKey AS UniqueIdentifier
SET @.WorkKey = NewID()

-- Update the first 'New' available work item with the key you have just generated
UPDATE WorkQueue
SET WorkKey = @.WorkKey, Status = 'In Progress'
WHERE WorkID IN
(SELECT TOP 1 WorkID
FROM WorkQueue
WHERE Status = 'New')

-- If @.@.RowCount is 0 then there are no 'New' work items. Re-execute this stored procedure later
IF @.@.RowCount = 0
BEGIN
SELECT -1 AS WorkID
RETURN
END

-- Get the WorkID that you have just updated
SELECT WorkID
FROM WorkQueue
WHERE WorkKey = @.WorkKey

END
GO

Please tell me if this answers your question.

Best regards,
Sami Samir

|||

You could make use of the OUTPUT clause that's available in SQL Server 2005, see the example below. This would avoid you having to use table hints.

Chris

SET NOCOUNT ON
DECLARE @.WorkQueue TABLE (WorkID INT, WorkName VARCHAR(100), Status VARCHAR(10))
INSERT INTO @.WorkQueue VALUES (1, 'Answer a forum question.', 'NEW')
INSERT INTO @.WorkQueue VALUES (2, 'Wash the car.', 'NEW')
INSERT INTO @.WorkQueue VALUES (3, 'Have a beer.', 'LATER')
INSERT INTO @.WorkQueue VALUES (4, 'Feed the cat.', 'COMPLETE')

DECLARE @.Output TABLE (WorkID INT)

UPDATE w
SET w.Status = 'INPROCESS'
OUTPUT inserted.WorkID INTO @.Output
FROM @.WorkQueue w
WHERE w.WorkID = (SELECT TOP 1 w2.WorkID FROM @.WorkQueue w2 WHERE Status = 'NEW' ORDER BY w2.WorkID)

DECLARE @.WorkID INT
SELECT @.WorkID = WorkID FROM @.Output

SELECT *
FROM @.WorkQueue
WHERE WorkID = @.WorkID

|||

Below is a code sample that you could put in a procedure. The sample starts a transaction a does a select to get the workid and workstatus. I used 3 lock hints readpast, rowlock, holdlock and updlock. The readpast will allow better concurrency in a multiple user system, however it is possible that a row may not be returned in all pages or rows are locked at the time the procedure is invoked. The rowlock hint makes sure that a pagelock isn't used so more users can access the data. The holdlock and updlock will lock the rows for the duration of the transaction and make sure that no other user can acquire the data. I have used this technique before for "work queue" and it works well. HTH.

DECLARE @.WorkID INT

,@.WorkName VARCHAR(20)

BEGIN TRAN

SELECT @.WorkID = WorkID, @.WorkName = WorkName

FROM WorkQueue(HOLDLOCK,UPDLOCK,READPAST,ROWLOCK)

UPDATE WorkQueue SET status = 'In Process'

WHERE

WorkID = @.WorkID;

COMMIT TRAN

|||

we are planning to sql server 2000. Sorry I forget to mention it.

I see different way do it. any suggestion which one I should use

|||

I think your answer is correct one. does this Guarantee uniqueness between concurrent & multiple user system. Do anyone see a problem in using it

|||

I AM GETTING FOLLOWING ERROR WITH YOUR SQL

You can only specify the READPAST lock in the READ COMMITTED or REPEATABLE READ isolation levels

|||I added WITH before the locking hints and it worked fine. Here is the updated query:

SELECT @.WorkID = WorkID, @.WorkName = WorkName
FROM WorkQueue WITH (HOLDLOCK, UPDLOCK, READPAST, ROWLOCK)

Best regards,
Sami Samir

|||

Sorry about forgetting the with clause. Yes it will guarantee concurrency and uniqueness. The readpast technique is great for a work queue. Haven't found it to be helpful in many other situations. Since this is a work queue make sure you do not put any uncessary indexes on it and/or have connections access it via different paths (i.e. indexes). Since work queues by nature are "busy" tables you do not want to introduce deadlocking by having multiple procedures access the data down different retrieval paths. HTH.

|||

Look at the below sql and tell me what is wrong with it. it doesn't have any transaction before it. does this works

CREATE PROC dbo.GetNextWork @.WorkItem BIGINT=0 OUTPUT as

UPDATE dbo.WorkQueue

SET Status='InProcess',@.WorkItem=WorkItemID

WHERE WorkItemID = ( SELECT TOP 1 WorkItemID

FROM dbo.WorkQueue WITH (ROWLOCK,UPDLOCK,READPAST)
WHERE Status = 'NEW' ORDER BY WorkItemID )

Variable @.WorkItem will be returned from stored procedure

|||

That would work, I created some sample code below for the forum. Learned something new today, never tried variable assignment in an update statement.

create table #workqueue (

workitemid int,

status varchar(30)

);

insert into #workqueue (workitemid,status) values (1,'new')

insert into #workqueue (workitemid,status) values (2,'new')

Declare @.WorkItem int;

UPDATE #WorkQueue

SET Status='InProcess',@.WorkItem=WorkItemID

WHERE WorkItemID = ( SELECT TOP 1 WorkItemID

FROM #WorkQueue WITH (ROWLOCK,UPDLOCK,READPAST)

WHERE Status = 'NEW' ORDER BY WorkItemID )

select @.workitem

Wednesday, March 28, 2012

How to estimate query governor cost setting?

How to estimate query governor cost setting?

How to estimate query governor cost setting?

Wednesday, March 21, 2012

How to enable a domain account to change service account?

How to enable a domain account to change service account?

How to enable a domain account to change service account?

Monday, March 12, 2012

How to Duplicate a SQL Instance

Friday, March 9, 2012

how to drop and create Triggers from an other DB?

Friday, February 24, 2012

How to do sizing on Memory requirement of SQL Server 2000 ?

How to do sizing on Memory requirement of SQL Server 2000 ?

how to do running totals?

How to do Running Total on a field

Sunday, February 19, 2012

How to do a SELECT ROW LOCK or READPAST

multiple rows insert

Blog Archive

About Me