Link to home
Start Free TrialLog in
Avatar of QPR
QPRFlag for New Zealand

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

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

Open in new window

Avatar of Harish Varghese
Harish Varghese
Flag of India image

The Try/Catch block will not be able to detect any COMPILE errors which includes schema errors.
To be able to CATCH such errors, we should wrap the statements in dynamic SQL in TRY block.

ALTER PROCEDURE [dbo].[abc]
as 
	BEGIN TRANSACTION
	IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zzz]') AND type in (N'U'))
		DROP TABLE [dbo].[zzz]
	BEGIN TRY
		Declare @sql nvarchar(4000)
		SET @sql = 'select SomeStuff into testdrop from zzz'
		EXEC sp_executesql @sql
	COMMIT TRANSACTION
	END TRY
	BEGIN CATCH
		Select 'Rolling back'
		ROLLBACK TRANSACTION
	END CATCH

Open in new window


Thanks to http://www.codeproject.com/Articles/12080/Using-TRY-CATCH-in-Transact-SQL
ASKER CERTIFIED SOLUTION
Avatar of Harish Varghese
Harish Varghese
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Scott Pletcher
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...
Avatar of QPR

ASKER

Thanks Scott, that's what I suspected (not caught) and yep set nocount is on, I just trimmed the sp for readability
Avatar of QPR

ASKER

Parent (so to speak) sproc did the trick - thanks