November 10, 2011 at 12:22 pm
Hi
I have this SP where the table is name used is wrong which is intentional
CREATE PROC test_delete
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO table1
SELECT 1
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
I got this error
Invalid object name 'table1'.
Msg 266, Level 16, State 2, Procedure test_delete, Line 7
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
thanks
ek
November 10, 2011 at 12:23 pm
is there a trigger on the real table represented by "table1"?
Lowell
November 10, 2011 at 12:28 pm
No trigger on the table, in fact no table exists as such.
I don't understand the transaction error, shouldn't it directly jump to the catch block and rollback the transaction as soon the error encountered ?
November 10, 2011 at 1:32 pm
Can anybody pls help on this issue ?
November 10, 2011 at 2:13 pm
ek-822869 (11/10/2011)
Can anybody pls help on this issue ?
The full error message you are getting is this one:
Msg 208, Level 16, State 1, Procedure test_delete, Line 6
Invalid object name 'table1'.
Msg 266, Level 16, State 2, Procedure test_delete, Line 6
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1.
Try/catch doesn't work with error 208 (Invalid object name). Actually nothing does. Even if you try to check for @@ERROR variable after the error line - it will show you nothing. You can run this:
CREATE PROC test_delete
AS
BEGIN
BEGIN TRY
BEGIN TRAN
INSERT INTO table1
SELECT 1
PRINT 'TRY'
COMMIT TRAN
END TRY
BEGIN CATCH
PRINT 'CATCH'
ROLLBACK TRAN
END CATCH
END
You will never see 'TRY' or 'CATCH' printed. This is because an execution inside a stored procedure immediately stops when error 208 is raised. The next command which will be executed is the one after your 'EXEC test_delete' statement.
And you can do nothing to change this behaviour. So my advices would be:
1. Don't try to use objects if they are not exist
2. In your application after each SQL batch check @@TRANCOUNT and if it's greater than 0 do ROLLBACK
November 10, 2011 at 2:54 pm
Thank you very much :-). Its interesting to know that the Try catch doesn't work with all the errors. Is there any specific list of error numbers that Try Catch cannot handle ?
November 10, 2011 at 2:55 pm
Thank you very much :-). Its interesting to know that the Try catch doesn't work with all the errors. Is there any specific list of error numbers that Try Catch cannot handle ?
November 11, 2011 at 1:05 am
'INSERT INTO' calsue expect table to EXIST but 'SELECT INTO' doesn't. Try the code below.
CREATE PROC test_delete
AS
BEGIN
BEGIN TRY
BEGIN TRAN
--INSERT INTO table1
SELECT 1
INTO table1
COMMIT TRAN
END TRY
BEGIN CATCH
ROLLBACK TRAN
END CATCH
END
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply