Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Transaction in Stored Proc
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Starting Member

United Kingdom
3 Posts

Posted - 09/25/2007 :  06:31:41  Show Profile  Reply with Quote
I have the following logic in my stored proc...
ALTER PROC sp_ExecuteAndLog @SQL varchar(8000) AS
Begin Transaction
Insert Into Log (lgSQL) Values (@SQL)
Commit Transaction
...I then Execute this stored proc thus...
Declare @Statement varchar(8000)
SET @Statement = 'bibble bibble bibble'
Exec sp_ExecuteAndLog @Statement

When I execute this it works....

I expected that "bibble bibble bibble" would not be an authentic SQL Statement and would fail. this does indeed fail but does not cause the transaction to fail and therefore "bibble bibble bibble" ends up being placed in my log.

This Log is reapplied to another db for other reasons and this naturally fails.

Can someone explain how I can get this to fail correctly and rollback the transaction?

Thanks in advance.

Rory Becker

Flowing Fount of Yak Knowledge

5581 Posts

Posted - 09/25/2007 :  06:45:10  Show Profile  Visit harsh_athalye's Homepage  Click to see harsh_athalye's MSN Messenger address  Send harsh_athalye a Yahoo! Message  Reply with Quote
This happens because BEGIN TRANSACTION statement written outside EXECUTE does not apply to it. EXECUTE creates its own transaction space. Also you need to rollback transaction by checking @@error value inside EXEC.

SET @Statement = 'begin tran bibble bibble bibble if @@error<>0 rollback tran'

Harsh Athalye
Go to Top of Page


United Kingdom
22859 Posts

Posted - 09/25/2007 :  06:50:58  Show Profile  Reply with Quote
.. or by transferring the Error State from the inner dynamic SQL to the outer - e.g. using sp_ExecuteSQL instead of EXEC with an OUTPUT parameter - so that the Outer code can do the ROLLBACK if it so wishes.

Go to Top of Page
  Previous Topic Topic Next Topic  
 Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.02 seconds. Powered By: Snitz Forums 2000