Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>sql

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
KayAre you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegroups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

Backing up Stored Procedure

Hi,
I am using the Backup Wizard with following statement to backup entire DB
from Development Server.
BACKUP DATABASE [Dev_DB] TO [Dev_DB_Backup] WITH INIT , NOUNLOAD ,
NAME = N'Dev_DB Backup',
SKIP , STATS = 10,
NOFORMAT
It doesnot backup the stored procedures developed.
Is there a way to backup the stored procedures as well through scheduled
task or some script to be run at specific time.
TIA
Kay
Are you sure, the command above store all the data and the object
definitions (which is actually data stored in the system tables) ?
Please make sure and confirm this.
HTH, jens Suessmeyer.
|||Systems table data is not required for the time being. But yes it is.
"Jens" <Jens@.sqlserver2005.de> wrote in message
news:1132850534.307481.196070@.g49g2000cwa.googlegr oups.com...
> Are you sure, the command above store all the data and the object
> definitions (which is actually data stored in the system tables) ?
> Please make sure and confirm this.
> HTH, jens Suessmeyer.
>

Avoiding deadlock

I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.

So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.

Instead I'm now getting deadlock errors.

If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?

Thanks.

Nate Hekman

You can change your code to the following:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)

WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT

The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.

Another approach is to do the following:

BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0

BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

COMMIT

|||

This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)

BEGIN TRANSACTION

IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...

Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.

--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs

create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)

then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.

|||

I didn't think that:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)

Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.

(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)

Thanks!

|||You are right. This should be XLOCK instead.|||

Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.

Thanks again.

Nate

|||

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

|||

This problem's interesting and I'm sure lots of people have encountered before.

There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?

Question 2>
How does it solve the deadlock problem?

|||

Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.

Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.

Avoiding deadlock

I have a stored procedure spUpdateClient, which takes as params a number of properties of a client application that wants to register its existence with the database. The sp just needs to add a new row or update an existing row with this data.

I tried to accomplish this with code somethign like this. (The table I'm updating is called Client, and its primary key is ClientId, which is a value passed into the sp from the client.)

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

But the client apps call this every second or so, so soon enough I started getting primary key violations. It looks like one client would make two calls nearly at the same time, both would get a 0 value on the SELECT line, so both would try to insert a new row with the same ClientId. No good.

So then I added

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

at the top, and a COMMIT at the bottom. I thought the first one in would get to run the whole sp, and the next one in would have to wait for the first to be done.

Instead I'm now getting deadlock errors.

If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

I could catch the deadlock in my client app after SQL Server kills one of the transactions, but it seems to me there should be some way to set a lock at the top of the sp that says "nobody else can enter this sp until I exit it". Any such thing?

Thanks.

Nate Hekman

You can change your code to the following:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, XLOCK)

WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT

The XLOCK hint in the SELECT statement will ensure that if the rows exists you lock it exclusively so the update will work fine. And the SERIALIZABLE hint will ensure that if the row doesn't exist you lock the key range for the new row. This will prevent the deadlock from happening.

Another approach is to do the following:

BEGIN TRANSACTION
UPDATE Clients WITH(SERIALIZABLE)
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
IF @.@.ROWCOUNT = 0

BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

COMMIT

|||

This is a very good understanding of why you are getting a deadlock :) You can cause it to single thread by adding an LOCK hint to the existence check. Also, change from using a count to exists, it will be better as it just needs to see a single row, rather than counting them all. No need for serializable here now, but make sure ClientId is indexed right (and if it is the declare primary key that should be fine)

BEGIN TRANSACTION

IF EXISTS (SELECT 1
FROM Clients WITH (XLOCK)
WHERE ClientId=@.ClientId)
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END
ELSE
BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

An alternative is to just ignore the deadlock as you know why it occurs, and since you are only keeping a single copy, it is just as good and will be fast enough...

Another alternative would be to switch to an insert only methodology and just pump rows into a table. It would give you counts of visits, actual times of visits, etc. You could also glean the same information as you have now with no locking problems at all.

--clientId, visitDate would be the likely UNIQUE constraint
--if they are > .003 seconds apart, which I don't know based on your needs

create table clientVisit
(
clientVisitId int identity primary key,
clientId guid,
visitDate datetime default (getdate()),
hostName varchar(?),
etc varchar(?)
)

then just insert... It will take more disk space, but it should be just as fast. You could then pull the data off periodically and get the same information, plus some.

|||

I didn't think that:

BEGIN TRANSACTION
IF NOT EXISTS(SELECT * FROM Clients WITH(SERIALIZABLE, UPDLOCK)
WHERE ClientId=@.ClientId)

Would suffice since a SHARED lock compatible with an UPDATE lock? In this case, the second could still read there to be no rows.

(I did overlook that you need to increase the isolation level just in case READ_COMMITTED_SNAPSHOT is enabled. And I didn't realize you could put lock hints on UPDATE statements :)

Thanks!

|||You are right. This should be XLOCK instead.|||

Wow, thanks for the excellent replies everyone! I'm very new at T-SQL so all this locking stuff is a lot to mull over. But you've given me several good approaches that I think will work just great.

Thanks again.

Nate

|||

IF (SELECT COUNT(ClientId) FROM Clients WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

|||

This problem's interesting and I'm sure lots of people have encountered before.

There are some things I still don't understand.
If we set the isolation level as Serializable and use XLOCK for the SELECT as follows:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION

IF (SELECT COUNT(ClientId) FROM Clients (WITH XLOCK) WHERE ClientId=@.ClientId) = 0
BEGIN
-- client not found, create it
INSERT INTO Clients (ClientId, Hostname, Etc)
VALUES (@.ClientId, @.Hostname, @.Etc)
END

ELSE

BEGIN
-- client was found, update it
UPDATE Clients
SET Hostname=@.Hostname, Etc=@.Etc
WHERE ClientId=@.ClientId
END

COMMIT TRANSACTION

As Nate Hekman first wrote:
If I understand the docs right, that's because the exclusive lock is not placed on the Clients table until the INSERT happens, not at the SELECT. So when two calls to the sp happen at nearly the same time (call them A and B), A does the SELECT and that locks Clients so nobody else can update it. Then B does the SELECT, locking Clients so nobody else (including A) can update it. Now A needs to exclusively lock Clients to do its INSERT, but B still has that read lock on it, and they're deadlocked.

Question 1>
If the row doesn't exist, does the process go like this:
process1 executes SELECT ... WITH XLOCK and holds an exclusive lock on Clients.
Because isolation level Serializable is used, other processes can't insert new rows or update rows within the defined range. Also, because an exclusive lock is held, other processes can't even read until process1 finishes?

Question 2>
How does it solve the deadlock problem?

|||

Question 1:
When a transaction holds Exclusive locks on a range of rows, other transaction cannot read those rows, unless the second transaction's isolation level is not READ UNCOMMITTED. As Nate uses the same sp for inserts, yes, the select command blocks all readers.

Question2:
Deadlocks do not occur because a transaction that executes its select command is guaranteed to be granted all the locks it will needs further, that is, it cannot be blocked, so a deadlock can never occur. This is a sort of pre-declaration of locks, or pessimistic locking. When transaction A executes its select command, the other ones (say trans. B)cannot read. If transaction A takes a long time to commit, B might be eventually aborted. But this will not happen-the insert transaction is a short one, and it will need milliseconds to commit, after that transaction B(or other waiting transaction) will be granted the lock. In this scenario, aborts are very unlikely to occur unless the workload is high.

avoiding cursors how to do this?

OK say I want to execute the following stored procedure for every order that
matches specific criteria
EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
If my set of orders to process is returned by this query:
select lngOrderID from tblOrders where intstatus=3
Is there any way to do this without resorting to a cursor?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>
No. And cursors aren't _that_ slow. Executing the procedure for each row
would not be noticably better without a cursor.
The potential performance problem is that you are executing the logic of the
procedure on a row-wise basis, instead of a set-wise basis. The only way to
avoid the row-wise processing would be to unwrap the guts of
dbo.sprPassUpline and create a version which operated over the entire set of
rows. Something like
EXECUTE @.RC = dbo.sprPassUplineByStatus 3
David|||declare @.loop int
declare @.rowcount int
declare @.lngOrder int
declare @.RC int
create table #OrderList (
ROW_ID int identity ,
lngOrder int not null )
insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
intStatus = 3
declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
while @.loop <= @.rowcount
begin
select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <>
begin
-- do whatever
end
select @.loop = @.loop + 1
-- or the below method if you delete any rows from the #OrderList table
for any reason
-- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
end
drop table #OrderList
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>|||"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:O8e%f.18404$ic1.16436@.newsfe5-win.ntli.net...
> declare @.loop int
> declare @.rowcount int
> declare @.lngOrder int
> declare @.RC int
> create table #OrderList (
> ROW_ID int identity ,
> lngOrder int not null )
> insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
> intStatus = 3
> declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
> while @.loop <= @.rowcount
> begin
> select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
> exec @.RC = dbo.sprPassUpline @.lngOrder
> if @.RC <>
> begin
> -- do whatever
> end
> select @.loop = @.loop + 1
> -- or the below method if you delete any rows from the #OrderList table
> for any reason
> -- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
> end
> drop table #OrderList
>
OK, that is tecnically what the OP asked for, but in what possible way is
that better than using a cursor?
declare cOrders cursor local static for
select lngOrderID from tblOrders
where intStatus = 3
declare @.lngOrder int,
@.RC int
open cOrders
fetch next from cOrders into @.lngOrder
while @.@.fetch_status = 0
begin
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <> 0
begin
raiserror('sprPassUpline failed returning %d',16,1,@.RC)
end
fetch next from cOrders into @.lngOrder
end
close cOrders
?
Davud|||> Is there any way to do this without resorting to a cursor?
Possibly, but since you haven't told us what the SP does we can't tell you.
The question is not "How do I do X once for each row without a cursor?"
The question is "How do I do X for the whole set INSTEAD OF once for each
row?"
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well actually in normal day to day business this SP is only called for one
row at a time when that order enters a given state. But when initializing
new systems it must be called for everything on import. Guess that is a
good enough reason to redo the SP and just make it set based so it'll handle
one or more. We never had this requirement before so it was never an issue.
Thanks for jumping in everybody.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uvSpxznXGHA.5012@.TK2MSFTNGP05.phx.gbl...
>> Is there any way to do this without resorting to a cursor?
> Possibly, but since you haven't told us what the SP does we can't tell
> you.
> The question is not "How do I do X once for each row without a cursor?"
> The question is "How do I do X for the whole set INSTEAD OF once for each
> row?"
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

avoiding cursors how to do this?

OK say I want to execute the following stored procedure for every order that
matches specific criteria
EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
If my set of orders to process is returned by this query:
select lngOrderID from tblOrders where intstatus=3
Is there any way to do this without resorting to a cursor?"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>
No. And cursors aren't _that_ slow. Executing the procedure for each row
would not be noticably better without a cursor.
The potential performance problem is that you are executing the logic of the
procedure on a row-wise basis, instead of a set-wise basis. The only way to
avoid the row-wise processing would be to unwrap the guts of
dbo.sprPassUpline and create a version which operated over the entire set of
rows. Something like
EXECUTE @.RC = dbo.sprPassUplineByStatus 3
David|||declare @.loop int
declare @.rowcount int
declare @.lngOrder int
declare @.RC int
create table #OrderList (
ROW_ID int identity ,
lngOrder int not null )
insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
intStatus = 3
declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
while @.loop <= @.rowcount
begin
select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <>
begin
-- do whatever
end
select @.loop = @.loop + 1
-- or the below method if you delete any rows from the #OrderList table
for any reason
-- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
end
drop table #OrderList
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:%23RYZxTnXGHA.1196@.TK2MSFTNGP03.phx.gbl...
>
> OK say I want to execute the following stored procedure for every order
> that matches specific criteria
>
> EXECUTE @.RC = dbo.sprPassUpline @.lngOrder
>
> If my set of orders to process is returned by this query:
> select lngOrderID from tblOrders where intstatus=3
>
> Is there any way to do this without resorting to a cursor?
>|||"Ben Rum" <bundyrum75@.yahoo.com> wrote in message
news:O8e%f.18404$ic1.16436@.newsfe5-win.ntli.net...
> declare @.loop int
> declare @.rowcount int
> declare @.lngOrder int
> declare @.RC int
> create table #OrderList (
> ROW_ID int identity ,
> lngOrder int not null )
> insert into #OrderList ( lngOrder ) select lngOrderID from tblOrders where
> intStatus = 3
> declare @.loop = min(ROW_ID), @.rowcount = max(ROW_ID) from #OrderList
> while @.loop <= @.rowcount
> begin
> select @.lngOrder = lngOrder from #OrderList where ROW_ID = @.loop
> exec @.RC = dbo.sprPassUpline @.lngOrder
> if @.RC <>
> begin
> -- do whatever
> end
> select @.loop = @.loop + 1
> -- or the below method if you delete any rows from the #OrderList table
> for any reason
> -- select @.loop= min(ROW_ID) from #OrderList where ROW_ID > @.loop
> end
> drop table #OrderList
>
OK, that is tecnically what the OP asked for, but in what possible way is
that better than using a cursor?
declare cOrders cursor local static for
select lngOrderID from tblOrders
where intStatus = 3
declare @.lngOrder int,
@.RC int
open cOrders
fetch next from cOrders into @.lngOrder
while @.@.fetch_status = 0
begin
exec @.RC = dbo.sprPassUpline @.lngOrder
if @.RC <> 0
begin
raiserror('sprPassUpline failed returning %d',16,1,@.RC)
end
fetch next from cOrders into @.lngOrder
end
close cOrders
?
Davud|||> Is there any way to do this without resorting to a cursor?
Possibly, but since you haven't told us what the SP does we can't tell you.
The question is not "How do I do X once for each row without a cursor?"
The question is "How do I do X for the whole set INSTEAD OF once for each
row?"
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Well actually in normal day to day business this SP is only called for one
row at a time when that order enters a given state. But when initializing
new systems it must be called for everything on import. Guess that is a
good enough reason to redo the SP and just make it set based so it'll handle
one or more. We never had this requirement before so it was never an issue.
Thanks for jumping in everybody.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:uvSpxznXGHA.5012@.TK2MSFTNGP05.phx.gbl...
> Possibly, but since you haven't told us what the SP does we can't tell
> you.
> The question is not "How do I do X once for each row without a cursor?"
> The question is "How do I do X for the whole set INSTEAD OF once for each
> row?"
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

avoid repeating "if...end" in a stored procedure

I would like to avoid repeating 4 time the procedure if end and instead use someting like "switch case", but I can not find the equivalent.
Is there any command that could do that?

thanks

[code]
CREATE PROCEDURE sa_default

@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = null

AS
if @.choix=0
begin
........
end

if @.choix=1
begin
.........
end

if @.choix=2
begin
.........
end

if @.choix=3
begin
......
end

RETURN
GO

[/code]What is it you are trying to accomplish in your condition?|||I am selection a list of project which change depending on the level of right of the user

CREATE PROCEDURE sa_default
@.etriduser int= null,
@.locator int = null,
@.choix int = null,
@.login varchar(50) = null
 AS
if @.choix=0
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
 ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0 )) /
 (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
 WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tproject.locator=isnull(@.locator,locator) ORDER BY idproject
end
if @.choix=1
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
 (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1
 FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany
 INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
 WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and (Tuserproject.etridperson=@.etriduser or Tproject.locator=@.locator) ORDER BY idproject
end
if @.choix=2
begin
SELECT DISTINCT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service,Tproject.location, Tproject.ponumber, Tproject.state,
 Tcompany.company, Tproject.datestart, ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1,Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
 FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
 WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) and Tuserproject.etridperson=@.etriduser ORDER BY idproject
end
if @.choix=3
begin
SELECT Tproject.idproject, Tproject.provider, Tproject.pnumber, Tproject.pdescription, Tproject.service, Tproject.location, Tproject.ponumber, Tproject.state, Tcompany.company, Tproject.datestart,
 ROUND(CONVERT(float, CONVERT(float, (SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC <> 0)) /
(SELECT SUM(NB) FROM TEChart WHERE Project = tproject.idproject AND QC = 0)), 5) * 100 AS Expr1, Tuserproject.mc, Tuserproject.type, Tuserproject.wechart
 FROM Tproject INNER JOIN Tcompanyproject ON Tproject.idproject = Tcompanyproject.etridproject
INNER JOIN Tcompany ON Tcompanyproject.etridcompany = Tcompany.idcompany INNER JOIN Tuserproject ON Tproject.idproject = Tuserproject.etridproject
WHERE (Tcompanyproject.type = 2) AND (Tproject.state IN (1, 2, 3)) AND
 (Tuserproject.etridperson IN (SELECT idperson FROM Tperson WHERE login LIKE @.login))
ORDER BY Tproject.idproject
end
RETURN
GO

|||You can use a CASE statement in some situations, but it seems like you want to execute code within your BEGIN... ENDs. It won't work in those cases. I don't believe there is anything like Javascript's switch you can use to execute code like that. Depending on what you're trying to do, dynamic SQL might be an answer. Really though, more information is needed on what will happen based on each IF to really give a good answer.|||A CASE statement may work when you just need to change the WHERE clause, but it looks like you change the query as well. It looks like IF times 4 is the way to go.|||I second trying the CASE, but if you can't make it work then I'd make turn those inner batches into their own procs you'll prb' get better performance cause you won't suffer from the selective query plan issues of using IFs.|||Thanks for your comments.

Can you tell me more about your idea about "turn those inner batches into their own procs "
I never hear about that, do you have any sample or link for documentation?

Thanks|||There's no great mystery to it. Problem 1: One of the publicised problems with stored procedure plans is they can get "confused" by IF statements. I believe the problem is because it originally stores only one plan that represents only one path through the code. So the other IF conditions may not be ready to run. I've not tested it this myself but sounds plausable.

Problem 2: The more complicated the plan, the more chance the query optimiser has of making the wrong choices. I've certainly seen this. The more conditions in the proc seems to increase the likelyhood of the query plan having performance issues. Not suprising really because there is a slider between compiling optimal code and compiling the code quickly. So the longer it takes to compile the code the more likely the optimiser will throw its hands in the air.

Both of these problems can be helped by converting some of the query into other stored procedures. These have their own plans so the optimiser can, typically, do a better job.|||I agree with that post 100% and it's true that 4 significantly different queries will have a very good chance of not being run optimally. Create 4 procs and within your application code, decide which one to call based on what that parameter is at run time. The 4 procs will run more efficiently.

Avoid index scan with LIKE and a variable

Hi,
Here's my problem: I want to write a stored procedure that returns all
records from a table that have a certain column starting with given
text. I however find that using LIKE and a variable always causes an
index scan... which is causing performance issues. My table has about
3.5M records.

Below is a test. In query analyser if I look at the execution plan for
the following it will come up as in index scan. However, if i just
hard-code the text it all works fine (index seek).

How can I do this with reasonable speed?
Thanks Greg

DECLARE @.find varchar(50)
SET @.find = 'start'

SELECT TOP 100
*
FROM Test
WHERE
Col1 LIKE @.find + '%'
--Col1 LIKE 'start%'gregbacchus (greg.bacchus@.gmail.com) writes:
> Here's my problem: I want to write a stored procedure that returns all
> records from a table that have a certain column starting with given
> text. I however find that using LIKE and a variable always causes an
> index scan... which is causing performance issues. My table has about
> 3.5M records.
> Below is a test. In query analyser if I look at the execution plan for
> the following it will come up as in index scan. However, if i just
> hard-code the text it all works fine (index seek).
> How can I do this with reasonable speed?
> Thanks Greg
>
> DECLARE @.find varchar(50)
> SET @.find = 'start'
> SELECT TOP 100
> *
> FROM Test
> WHERE
> Col1 LIKE @.find + '%'
> --Col1 LIKE 'start%'

You don't say whether the index on Test.Col1 is clustered or not, and
whether this is the index that is scanned. I would expect that the index
on Test.Col1 is non-clustered, and the scan you see is a clustered index
scan.

When you have the literal, SQL Server knows about the query than you
have the variable. For the variable, SQL Server can only make a standard
assumption. Had the variable instead been a parameter to a stored procedure,
SQL Server would have looked at that value.

The best way out may be to simply use an index hint. You could also use
sp_executesql and pass the variable as a parameter.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Avoid duplicate index while creating temp table

We are using SQL 2K with sp4.
In our stored procedure, we create a temp table (i.e. #tablename) and
various indexes within the temp table. If mutliple users execute this store
d
procedure at the same time, it would cause duplicate index error. As a
temporary fix, we attach an unique number to the index name. I like to know
what is the best way to solve this problem without extra work?
wingmanI posted a similar query yesterday ( in which I completely mispelled
temporary);
http://groups.google.co.uk/group/mi...b23b0ffd6?hl=en
the basic response was that the indexes can co-exist without naming
conflicts
Cheers
Will|||Wingman,
You can read about this in the BOL. They must be unique within a table or
view but do not need to be unique within a database. Each temporary table
(not global ones ##) is unique. SQL Server create a unique name per user.
AMB
"Wingman" wrote:

> We are using SQL 2K with sp4.
> In our stored procedure, we create a temp table (i.e. #tablename) and
> various indexes within the temp table. If mutliple users execute this sto
red
> procedure at the same time, it would cause duplicate index error. As a
> temporary fix, we attach an unique number to the index name. I like to kn
ow
> what is the best way to solve this problem without extra work?
> wingman|||dude, step back a bit, and think about a solution where you don't have
multipel users accessing the same temp table.
this has GOT to lead to integrity issues.

avoid deadlock at all cost

Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread
/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38[/
url]
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/micr...5a25c38

> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David

avoid deadlock at all cost

Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
[url]http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread
/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38[/
url]
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/micr...5a25c38

> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David

avoid deadlock at all cost

Hi,
Is there a way to avoid deadlock completely, it will be ok for me if it goes
a store procedure with serializable execution (not just result via isolation
level). I can do it in my code but since we have many different servers, we
cannot easily serialize the execution in the code, so I'm looking for a way
to serialize the execution of store proc in the db level. It's only involved
with just one table but with 3 operations: read, update, and delete. I don't
care about performance cost of the serialization. Is that possible? Thanks!You can serialize sections of code in your stored procedures
by using sp_getapplock/sp_releaseapplock. Look in BOL
for more information.|||Based on the conversation that I saw, there is no guarantee to avoid
deadlock via sp_getapplock. See:
http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
Any other idea? Thanks!
<markc600@.hotmail.com> wrote in message
news:1147709999.616489.13200@.v46g2000cwv.googlegroups.com...
> You can serialize sections of code in your stored procedures
> by using sp_getapplock/sp_releaseapplock. Look in BOL
> for more information.
>|||"Zen" <zen@.nononospam.com> wrote in message
news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
> Based on the conversation that I saw, there is no guarantee to avoid
> deadlock via sp_getapplock. See:
> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
> Any other idea? Thanks!
>
It will work. Depending on the scope of your transactions and the other
queries running in other sessions, a procedure using sp_getapplock can still
have locking or deadlocking problems.
However, sp_getapplock will prevent multiple connections invoking the same
stored procedure from deadlocking. If the connections call the procedure in
the scope of a larger transaction, they will acquire and keep other locks
for the duration of the transaction, so there is no absolute guarantee.
So sp_getapplock is a very effective tool for eliminating deadlocks.
Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
deadlocks by eliminating the shared locks.
David|||That sounds great, how do we ensure that the lock is released at the end of
the transaction regardless of what happens? From what I understand some
fatal error can stop sql execution immediately and the next error catching
statement might not be executed, so trapping error and calling
sp_releaseapplock doesn't always work. Any idea? thanks!!
"David Browne" <davidbaxterbrowne no potted meat@.hotmail.com> wrote in
message news:utyiKfEeGHA.2456@.TK2MSFTNGP04.phx.gbl...
> "Zen" <zen@.nononospam.com> wrote in message
> news:ey9swOEeGHA.4576@.TK2MSFTNGP05.phx.gbl...
>> Based on the conversation that I saw, there is no guarantee to avoid
>> deadlock via sp_getapplock. See:
>> http://groups.google.com/group/microsoft.public.sqlserver.server/browse_frm/thread/b5216d42905d2a6/f878db90a5a25c38?lnk=st&q=sp_getapplock&rnum=1#f878db90a5a25c38
>> Any other idea? Thanks!
> It will work. Depending on the scope of your transactions and the other
> queries running in other sessions, a procedure using sp_getapplock can
> still have locking or deadlocking problems.
> However, sp_getapplock will prevent multiple connections invoking the same
> stored procedure from deadlocking. If the connections call the procedure
> in the scope of a larger transaction, they will acquire and keep other
> locks for the duration of the transaction, so there is no absolute
> guarantee.
> So sp_getapplock is a very effective tool for eliminating deadlocks.
> Also in SQL Server 2005 READ COMMITED SNAPSHOT ISOLATION will reduce
> deadlocks by eliminating the shared locks.
> David
>|||"Zen" <zen@.nononospam.com> wrote in message
news:u0yhdnEeGHA.3348@.TK2MSFTNGP03.phx.gbl...
> That sounds great, how do we ensure that the lock is released at the end
> of the transaction regardless of what happens? From what I understand some
> fatal error can stop sql execution immediately and the next error catching
> statement might not be executed, so trapping error and calling
> sp_releaseapplock doesn't always work. Any idea? thanks!!
>
I would never use sp_releaseapplock in the first place.
You can use sp_getapplock to create session-level or transaction-level
application locks. session-level locks are tricky because of the issue you
identified (you must somehow guarantee that the lock gets released). So I
would never use a session-level application lock. Instead use a
transaction-level application lock, which is released automatically when
your transaction ends.
If you start a transaction and use the default value for the @.LockOwner
argument to sp_getapplock, then the app lock will be enlisted into your
transaction like any other lock. It will automatically be released when
your transaction is either commited or rolled back.
David

Average of fields in a record

Hi.
I am looking for a simple way to average fields in a record..not rows, but fields, using a procedure. If one of the fields is null, then it needs to be excluded from the average.
Any ideas?
Help would be really appreciated!!
Thanks.
NickieOriginally posted by ngillis
Hi.

I am looking for a simple way to average fields in a record..not rows, but fields, using a procedure. If one of the fields is null, then it needs to be excluded from the average.

Any ideas?

Help would be really appreciated!!

Thanks.

Nickie

If it is permanent table - you can use system tables (syscolumns,sysobjects) for creating dynamic query and calculating.
But, I afraid, there is something wrong with db design if you need to do things like this.|||Hi Snail.

Please explain. My tables are the results of survey information that is entered online by clients. I need to calculate the averages of groups of questions that they answered. One survey is one record in the database, which includes the groups of records.
What would be a better way to do this? I can't change it now as the survey is live..but it would be helpful for future use.

I don't really know much about sysobjects. and help is not that helpful. Any ideas where I can get more information?

Thanks.

Nickie|||Whoops..where I said "One survey is one record in the database, which includes the groups of records." I meant to say the groups of questions.

Thanks.|||Hello Nickie,

an easy way to calculate averages of certain fields would be

select (coalesce(field_1, 0) + coalesce(field_2, 0))/number_of_fields
from table

BUT, this query will replace every NULL value with 0 (or every other number you put in the COALESCE statement).

Maybe this "workaround" will help out. If not post again!

Greetings,
Carsten

AutoNum + Exception

Hi all,
I created a table with an autonum field, with a stored procedure to
insert new record to the table. however I found that the autonum will keep
increase when some unique constraints is voliated.
I tried to use Begin Transaction, and rollback when there's error during the
insert statement, but fail to do it. Any solution to solve it? or I have
missed out something?
Thanks a lot for helping.This behavior is by design.
Even if you rollback a transaction, the generated identity will not reset.
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||(Windy) writes:
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
There are two main roads to create an surrogate id: 1) Roll your own. 2) Let
the database do it. This is not merely a question of convienence. The
IDENTITY function is designed to be scalable, so that many processes
can insert at the same time without blocking each other. For this reason,
the counter for the IDENTITY is not reset when a transaction rolled back.
Sometimes you have the business requirement that number must be contiguous,
this is typical for accounting applications. In this case, you must roll
your own. But you must then also be prepare to handled a higher degree
of blocking. To wit, process 1 gets a number, and uses it in a longer
transaction. Process 2 also needs a number, but it cannot get one until
Process 1 has completed, for the simple reason that Process 2 cannot
know which is the next number, as that depends on whether Process 1 will
commit or rollback.
The scheme for rolling your own is:
BEGIN TRANSACTION
SELECT @.nextid = coalesce(MAX(id), 0) + 1
FROM tbl WITH (HOLDLOCK, UPDLOCK)
INSERT tbl (id, ...
SELECT @.nextid...
-- More work
COMMIT TRANSACTION
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||This behavior is by design, but isn't documented very well in BOL.
It's not a good practice to rely on the contiguity of IDENTITY values for
permanent or otherwise shared tables. A surrogate key value should not add
any meaning to the row for which it is a surrogate. This means that neither
its magnitude nor its relative position with respect to other rows'
surrogate key values should be relied upon in your data model. All that is
important is that that value be different for each row. Also, since
surrogate key values should not add anything, they also cannot be used to
guarantee entity integrity. A unique constraint must also exist whose
definition doesn't include the surrogate key column.
Many outside influences can affect the values generated by IDENTITY. For
example, the administrator may reset the IDENTITY seed if an overflow is
about to occur. The IDENTITY values on a table may need to be changed in
order to facilitate replication or consolidation with other databases.
Also, gaps can occur due to rollbacks or deletes.
I'm not saying that IDENTITY is a bad thing; on the contrary: it's a
valuable tool, but it's important that it be used correctly.
I have used the IDENTITY property on table variables and local temporary
tables to facilitate sequencing and ordering, but that occurs entirely
within the body of a procedure or trigger, and because the objects are local
to the connection, there cannot be any any interaction with other
transactions.
<Windy> wrote in message news:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>|||The December 2005 issue of SQL Server Magazine has an article by Itzik
Ben-Gan on creating a custom identity generating stored procedure.
http://www.windowsitpro.com/Article...8165/48165.html
You'll need a subscription to access the full article.
"Windy" wrote:

> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during t
he
> insert statement, but fail to do it. Any solution to solve it? or I have
> missed out something?
> Thanks a lot for helping.
>
>|||Many thanks to all of you guys.
<Windy> glsD:ONwFHC2DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Hi all,
> I created a table with an autonum field, with a stored procedure to
> insert new record to the table. however I found that the autonum will keep
> increase when some unique constraints is voliated.
> I tried to use Begin Transaction, and rollback when there's error during
> the insert statement, but fail to do it. Any solution to solve it? or I
> have missed out something?
> Thanks a lot for helping.
>