June 29, 2010 at 11:42 am
I have the following sql using a try catch with multiple statements within the block (see below)
Begin Try
Begin Transaction
Insert Into XXX....
(this inserts 0 rows)
UPDATE YYY...
Insert Into YYY....
UPDATE ZZZ....
Update AAA.....
Update III .....
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
BEGIN
ROLLBACK
(do addtional loging here)
END
END CATCH
For some reason after the first INSERT INTO XXX... statement it jumps to the CATCH block and does the ROLLBACK. Why aren't the other Updates and Inserts within the Transaction getting executed?
Thanks,
John Walker
June 29, 2010 at 11:44 am
Have you tried running the statements individually? Are there any errors?
Thanks...Chris
June 29, 2010 at 12:16 pm
Yes, I have and there are no errors.
June 29, 2010 at 12:17 pm
John Walker-232274 (6/29/2010)
For some reason after the first INSERT INTO XXX... statement it jumps to the CATCH block and does the ROLLBACK. Why aren't the other Updates and Inserts within the Transaction getting executed?
If any part of the code within the TRY block generates an error, the result will be the CATCH, which in this case is the rollback. So the way that you've coded this, either everything will succeed or everything will fail. If you want the inserts and updates to happen independent of each other you should put them in separate TRY ... CATCH blocks.
June 29, 2010 at 12:18 pm
John Walker-232274 (6/29/2010)
Yes, I have and there are no errors.
If your code is terminating in the CATCH block that means you have an error. Without seeing your actual code, however, it is impossible to give you any advice.
June 29, 2010 at 12:43 pm
Ok, I have added the following after the first Insert statement:
print '@@ERROR: '+ CAST(@@ERROR AS VARCHAR)
and @@ERROR comes back as 0
I have added print statements before and after each Insert/Update as well as in the CATCH block.
After the first INSERT within the Transaction the next statement processed is within the CATCH block and @@Error is still 0, the 'After Insert print statment' does not get processed.
It is acting like within the TRY block it is only allowing 1 statement to be executed. Is this a limitation of the try catch? I have seen examples of only one statement (select/Insert/Update/Delete/Exec Stored Proc) is this why I am seeing what I am seeing?
Thanks,
JW
June 29, 2010 at 12:54 pm
Without seeing exactly what you're trying to execute it's very difficult to help you.
TRY blocks work as a block .... you can put as much code in there as you want to. If an error is encountered, then it goes to the CATCH block.
@@ERROR is very limited and is not recommended for use with TRY...CATCH. From Books Online under "TRY...CATCH":
In the scope of a CATCH block, the following system functions can be used to obtain information about the error that caused the CATCH block to be executed:
ERROR_NUMBER() returns the number of the error.
ERROR_SEVERITY() returns the severity.
ERROR_STATE() returns the error state number.
ERROR_PROCEDURE() returns the name of the stored procedure or trigger where the error occurred.
ERROR_LINE() returns the line number inside the routine that caused the error.
ERROR_MESSAGE() returns the complete text of the error message. The text includes the values supplied for any substitutable parameters, such as lengths, object names, or times.
I would highly recommend that you read that article in whole.
June 29, 2010 at 1:01 pm
Thanks, found the error.
June 29, 2010 at 1:09 pm
John Walker-232274 (6/29/2010)
Thanks, found the error.
Glad to hear it.
If you have a chance to post what you found the problem to be, that may be helpful to someone that ends up on this thread because they're having a similar problem.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply