We help IT Professionals succeed at work.

Transaction not rolling back

289 Views
Last Modified: 2014-05-14
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

Comment
Watch Question

Harish VargheseProject Leader

Commented:
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
Project Leader
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Scott PletcherSenior DBA
CERTIFIED EXPERT
Most Valuable Expert 2018
Distinguished Expert 2019

Commented:
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...
QPR

Author

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

Author

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

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.