problem tracking concurrency error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

problem tracking concurrency error

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2009-01-04 : 22:39:05

I have a update sp that traps various errors but does not trap the concurrency error. Can you tell me what the problem is. I have a status number that goes back to the vb.net code that calls the sp. I use the number -1111 for concurrency errors. Regular error numbers and text come back ok, but the concurrency error is ignored.

Here is the sp. And thanks very much. By the way, I have seen try/catch with transactions coded with the try first and others with the begin trans first. Does it matter?

PROCEDURE [dbo].[uspUpdate_tblProductPurchase]
(
@ProductPurchasingID int,
@ProductID int,
@Sys_Ts TimeStamp,
@Sys_TsOut TimeStamp OUTPUT)
AS

DECLARE @tran_status int

SET NOCOUNT OFF;
BEGIN TRY

BEGIN TRANSACTION

UPDATE [tblProductPurchase] SET [ProductID] = @ProductID
WHERE (ProductPurchasingID = @ProductPurchasingID AND Tstamp = @Sys_Ts);

/* gets new timestamp for .net form */
select @sys_TSOut = TStamp from tblProductPurchase where ProductPurchasingID = @ProductPurchasingID

COMMIT TRANSACTION
select @sys_TSOut Newsys_TS
return(0)
END TRY

BEGIN CATCH
if (@@ROWCOUNT=0)
select @tran_status = -11111

if (@@error <> 0)
select @tran_status =@@error

if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION

/* Log the error
insert dbo.Application_Error_Log (UserName, tableName,
errorNumber, errorSeverity, errorState, errorMessage)
values (suser_sname(), @tableName, ERROR_NUMBER(),
ERROR_SEVERITY(), ERROR_STATE(), ERROR_MESSAGE()) */
DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,
@ErrorProcedure NVARCHAR(200);

-- Assign variables to error-handling functions that
-- capture information for RAISERROR.
SELECT
@ErrorNumber = ERROR_NUMBER(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

-- Build the message string that will contain original
-- error information.
SELECT @ErrorMessage =
N'Error %d, Level %d, State %d, Procedure %s, Line %d, ' +
'Message: '+ ERROR_MESSAGE();

-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.

RAISERROR
(
@ErrorMessage,
@ErrorSeverity,
1,
@ErrorNumber, -- parameter: original error number.
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);

return @tran_status
END CATCH

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-04 : 23:45:48

Please post the exact error message that you are talking about.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

smh
Yak Posting Veteran

94 Posts

Posted - 2009-01-05 : 00:48:58

Well, the problem is that this sp does not trap the following situation. (If I edit a record in this table from one location (running another instance of the application, or editing the row directly from the table in the database) and then save another change to the same row from another instance of the application, I do not capture this error. Note that I use timestamps and compare the current timestamp on the application form with the timestamp for that row in the table. If they are not identical then I do not make the changes to the record and post an error.

I would, in earlier sql server versions not using try/catch, be able to capture the error code -1111 that I use in the sp to indicate this situation. But now, when I go to save the record that has already been changed and whose timestamp has been changed, the sp does not catch this situation.

smh
Yak Posting Veteran

94 Posts

Posted - 2009-01-05 : 10:04:12

I have shortened the example to make it easier to see. Essentially, what do I need to change in order to return a code indicating that the timestamps do not match (that a change has already been made to the record prior to this update. Currently this error returns 0 (the error is not caught) instead of -1111. Other errors (divide by zero, improper casting are caught).

PROCEDURE [dbo].[uspUpdate_tblProductPurchase]
(
@ID int,/*record key */
@Sys_Ts TimeStamp /* timestamp */
...
)
AS

DECLARE @tran_status int

SET NOCOUNT OFF;
BEGIN TRY

BEGIN TRANSACTION

UPDATE table set...
WHERE (ID = @ID AND Timestamp = @Sys_Ts); /* if timestamps do not match this should be caught */

COMMIT TRANSACTION
return(0)
END TRY

BEGIN CATCH
if (@@ROWCOUNT=0)
select @tran_status = -11111 /* this is the code I send if timestamps don't match */

if (@@error <> 0)
select @tran_status =@@error

if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION

--I have removed the code on the error message set up and just left the comments

-- Assign variables to error-handling functions that
-- capture information for RAISERROR
-- Build the message string that will contain original
-- error information.
-- Raise an error: msg_str parameter of RAISERROR will contain
-- the original error information.

RAISERROR

return @tran_status
END CATCH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 10:11:17

include a timestamp type column in your table. for updates retrieve the timestamp value from record you select using id,check before update if timestamp is same and raise error if not. this is explained in article below

http://www.codeproject.com/KB/aspnet/concurrentupdates.aspx?fid=353455&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=1768592

smh
Yak Posting Veteran

94 Posts

Posted - 2009-01-05 : 12:13:44

Hi:

This is exactly what I have done. The question is how to detect this in the sp. In the article you mention, they do not use a try catch. So what I need to do is to add what is needed to check for the timestamp discrepancy. Now I also check the Rowcount as in the article:
BEGIN CATCH
if (@@ROWCOUNT=0)
select @tran_status = -11111 /* this is the code I send if timestamps don't match */

But this is not being checked in my sp. Instead, if there are no other errors, the status code returns a 0, meaning no errors.
So there is something wrong with how I have done the sp.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-05 : 12:23:27

quote:
Originally posted by smh

Hi:

This is exactly what I have done. The question is how to detect this in the sp. In the article you mention, they do not use a try catch. So what I need to do is to add what is needed to check for the timestamp discrepancy. Now I also check the Rowcount as in the article:
BEGIN CATCH
if (@@ROWCOUNT=0)
select @tran_status = -11111 /* this is the code I send if timestamps don't match */

But this is not being checked in my sp. Instead, if there are no other errors, the status code returns a 0, meaning no errors.
So there is something wrong with how I have done the sp.

then change it like thats shown in sp. the control transfers to catach block only if there was an error in try block. in your case there's no error happening. since its a custom error condition that you're trying to generate if you want catch to catch error you need to explicitly raiserror in try block based on condition check and then use error_number(),error_message() .... functions to capture it in catch.

smh
Yak Posting Veteran

94 Posts

Posted - 2009-01-05 : 15:21:39

Hello,

here is my latest attempt. This does return the correct messags for concurrency and other errors, but I am not able to capture any error numbers in the @trans_status return code, although I do get an error message from the exceptiono bject in the .net catch statement. How can I do this. It is hard to see from the article because they are using a goto and @@TRANCOUNT which I am not sure how to use.
Thanks so much. I have had to transfer all the old sp's to using try/catch and having problems with this.

PROCEDURE [dbo].[uspUpdate_tblProductPurchase]
DECLARE @tran_status int

SET NOCOUNT OFF;
BEGIN TRY

BEGIN TRANSACTION
UPDATE ...
WHERE (ProductPurchasingID = @ProductPurchasingID AND Tstamp = @Sys_Ts);
IF @@ROWCOUNT = 0
RAISERROR('ConcurrencyError',16,1,-1111)

COMMIT TRANSACTION
END TRY

BEGIN CATCH

if (@@error <> 0)
select @tran_status =@@error

if (XACT_STATE() = -1)
ROLLBACK TRANSACTION
else
if (XACT_STATE() = 1)
COMMIT TRANSACTION

DECLARE
@ErrorMessage NVARCHAR(4000),
@ErrorNumber INT,
@ErrorSeverity INT,
@ErrorState INT,
@ErrorLine INT,

SELECT @ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE(),
@ErrorLine = ERROR_LINE(),
@ErrorNumber = ERROR_NUMBER(),
@ErrorProcedure = ISNULL(ERROR_PROCEDURE(), '-');

RAISERROR
(
@ErrorMessage,
@ErrorSeverity, -- parameter: original error severity.
@ErrorState, -- parameter: original error state.
@ErrorNumber, -- parameter: original error number.
@ErrorProcedure, -- parameter: original error procedure name.
@ErrorLine -- parameter: original error line number.
);

return @tran_status
END CATCH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-01-06 : 03:37:40

dont use @@Error, use functions like error_number(),... in catch block.

smh
Yak Posting Veteran

94 Posts

Posted - 2009-01-07 : 18:02:00

I realize looking at the samples that what I have asked for is possible only when not using try/catch method of tracking sp errors.

Previous to try/catch, I had tracked errors in an sp by using a goto in the sp and returning appropriate error codes. So the sp always returned a value as though it had run successfully. This does not happen with try/catch in an sp.

If it runs successfully a 0 is returned, otherwise nothing is returned. I had been trying to keep the same logic I had been using but it just won't work and is not the right way to do it.

Instead I am inserting the errnumber and any info which can be gathered from the catch statement code into an error table and returning an error message to the application. I have some questions about that, but it is a separate topic to be posted.

Thanks for helping me realize this.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources