QPR
asked on
Transaction not rolling back
I have this stored procedure (greatly simplified for the purposes of the question)
All works fine.
But so I can test the rollback part I alter the SP and intentionally reference a non-existant table.
When I exec the SP it errors - but it also holds open the transaction and I have to kill the spid to release it.
Is the syntax incorrect? The invaid object reference not "caught" or other?
Basically I drop a table so I can recreate it, but I want to rollback the DROP table if there is an issue with the SELECT INTO part of the SP
All works fine.
But so I can test the rollback part I alter the SP and intentionally reference a non-existant table.
When I exec the SP it errors - but it also holds open the transaction and I have to kill the spid to release it.
Is the syntax incorrect? The invaid object reference not "caught" or other?
Basically I drop a table so I can recreate it, but I want to rollback the DROP table if there is an issue with the SELECT INTO part of the SP
ALTER PROCEDURE [dbo].[abc]
as
BEGIN TRANSACTION
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[xxx]') AND type in (N'U'))
DROP TABLE [dbo].[xxx]
BEGIN TRY
select SomeStuff
into xxx
from zzz
COMMIT TRANSACTION
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
END CATCH
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SQL can't trap certain errors, and a non-existent table is one of them.
SQL does verify the schema of existing objects in CATCH blocks, but allows undefined objects to be accepted ("deferred resolution"). That is, if you reference a non-existent column in an existing table, you will get a proc alter/create error, but not if the table does not exist at all.
I think you can specify:
SET XACT_ABORT ON
then divide by 0:
SELECT 1/0
to force an error.
Btw, you should add:
SET NOCOUNT ON
immediately at the start of the proc:
ALTER PROCEDURE [dbo].[abc]
as
SET NOCOUNT ON;
...rest_of_code_as_before. ..
SQL does verify the schema of existing objects in CATCH blocks, but allows undefined objects to be accepted ("deferred resolution"). That is, if you reference a non-existent column in an existing table, you will get a proc alter/create error, but not if the table does not exist at all.
I think you can specify:
SET XACT_ABORT ON
then divide by 0:
SELECT 1/0
to force an error.
Btw, you should add:
SET NOCOUNT ON
immediately at the start of the proc:
ALTER PROCEDURE [dbo].[abc]
as
SET NOCOUNT ON;
...rest_of_code_as_before.
ASKER
Thanks Scott, that's what I suspected (not caught) and yep set nocount is on, I just trimmed the sp for readability
ASKER
Parent (so to speak) sproc did the trick - thanks
To be able to CATCH such errors, we should wrap the statements in dynamic SQL in TRY block.
Open in new window
Thanks to http://www.codeproject.com/Articles/12080/Using-TRY-CATCH-in-Transact-SQL