ROLLBACK error - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

ROLLBACK error

Author

Topic

MichelleMA
Starting Member

32 Posts

Posted - 2006-12-14 : 12:03:39

Hi,
I am calling a SP 'sp_EmbProcedure' from a main SP 'sp_Procedure'(where I thought of testing rollback by changing the SP name 'sp_EmbProcedure' to 'sp_EmbProc')

as follows:

Create Procedure sp_Procedure
AS
BEGIN TRAN CloseProc
Statement1
Statement 2

/*Executing 'sp_EmbProc' <--- should be 'sp_EmbProcedure', the
transation should rollback*/

DECLARE @Req bit
EXECUTE sp_EmbProc @param1, @param2 = @Req OUTPUT

IF @@ERROR <>0
BEGIN
ROLLBACK TRAN CloseProc
RETURN
END

Statement3
COMMIT TRAN CloseProc
GO

-------------------------------------------
I get the following errors, however the rollback worked fine, statements 1, 2, 3 did not execute.
Anyone has an explanation please?

Server: Msg 2812, Level 16, State 62, Line 745
Could not find stored procedure 'sp_EmbProc'.
Server: Msg 6401, Level 16, State 1, Procedure sp_Procedure, Line 749
Cannot roll back CloseProc. No transaction or savepoint of that name was found.
Server: Msg 266, Level 16, State 2, Procedure sp_Procedure, Line 750
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 2.

Kristen
Test

22859 Posts

Posted - 2006-12-14 : 12:16:45

My understanding is that if you get a "syntax" error that will trigger a rollback (well, I'm a little uncertain on this, but I think it is the NEXT statement that triggers the rollback, rather than the offending statement).

So then you have NO transaction, and anything else runs OUTSIDE a transaction.

At that point your
"ROLLBACK TRAN CloseProc"
will give an error (because the syntax error already caused a rollback, so there is no transaction any more)

And then the Sproc will return, and that will give you the inconsistency on the Transaction Count. However, I would expect it to be lower than expected, rather than higher as your error message indicates (maybe that's a side effect of using a named savepoint).

By-the-by you ought not to name your Stored Procedures with the "SP_" prefix because SQL Server will look in master for them first, then the current database - which is less efficient than if you use a different prefix - unless you WANT to store the procedure in Master of course!

Kristen

MichelleMA
Starting Member

32 Posts

Posted - 2006-12-19 : 13:37:21

Thank you!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources