Can Scope_identity genned in a proc be captured by Profiler.

Question

Can Scope_identity genned in a proc be captured by Profiler.

Bharatvip

Ten Centuries

Points: 1198
More actions
March 2, 2009 at 8:58 pm

#128112

Hi,
I have two questions on the procedure below:
1. If this procedure is called in quick succession say 3 to 4 times by the same user through an application, can he get a wrong @NewOrderID returned to him. From what I understand, if we use SCOPE_IDENTITY() and that is what we are using, then we should never have that problem. The application developers think that that may be a problem and I as database person want to make sure that that can not happen.
2. If we turn the profiler trace on we can still only get the actual procedure call made by the application. e.g.
Exec Add_Order @FileID=10,@OrdereeID=4, @OrdererID=3
But we can not get the value of the @NewOrderID that is passed back to the application, Right?. I don't see any events etc in the profiler that capture the returned data set from the procedure, is there a way to capture this in the profiler.
CREATE PROCEDURE [dbo].[Add_Order] (
@FileID int,
@OrdereeID int,
@OrdererID int
)
AS
SET NOCOUNT ON
Declare @NewOrderID int
Declare @Error int
-- tableOrder has an identity on OrderID, so the next OrderID gets generated that way.
BEGIN TRAN
Insertdbo.tableOrder(
FileID,
OrdereeID,
OrdererID
)
Values (
@FileID,
@OrdereeID,
@OrdererID
)
Select @NewOrderID = SCOPE_IDENTITY(), @Error = @@ERROR
IF @ERROR <> 0
BEGIN ROLLBACK TRANSACTION
RAISERROR ('Error inserting new Order', 16, 1 )
RETURN -1
END
If ( (@NewOrderID is NOT NULL) AND (@NewOrderID > 0) )
Begin
-- Inserts a record to another table one of the columns being the @NewOrderID created above
-- Does error check and if a problem then Rollback as above
End
COMMIT TRAN
SELECT @NewOrderID AS OrderID
Return
Thanks!

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

I’m not aware of any bug the sends the wrong scope_identity to the client, so I don’t think that the problem has to do with the scope_identity function. Also there is no way to get the value of scope_identity in the profiler. One choice that you have is to insert it into a table and then check the table (off course you can do it just for debug. After you finish your debug, drop the table and delete the relevant code). If you think that you are getting the wrong value, then check the code of the SP to see if you modify the value of @newOrderID variable. Another option is that you rollback the transaction. If you do so, this will not modify the value of scop_identity function, but in reality you won’t get the record in the table.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Bharatvip Ten Centuries Points: 1198 More actions · Answer 2

Thanks Adi, it's good to know that there are no known bugs in scope_identity. The trouble with putting a debug is that it is only happening in the production environment and that too sporadically. Just not reproducible in the DEV environment and that's why I was trying to check if there is a way to get result sets from stored procedure calls through a profiler trace. Guess we can't do that then. Thanks again.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 3

There are no issues with SCOPE_IDENTITY that I'm aware of.

One way you can try to debug this is to use the OUTPUT clause. I use it instead of SCOPE_IDENTITY because I usually have to deal with multi-row inserts and when you get there SCOPE_IDENTITY just doesn't provide enough information.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

I'll throw in a third vote for the stability of SCOPE_IDENTITY(). I have had issues with @@Identity (SQL Server 7) but never with SCOPE_IDENTITY in 2000 or 2005.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question