Posted by xAvailx on 10/01/58 11:40

Hello:

I didn't find any documentation that notes save point names are case
sensitive, but I guess they are...

Stored Proc to reproduce:

/* START CODE SNIPPET */

If Exists (Select * From sysobjects Where Type = 'P' and Name =
'TestSaveTran')
Drop Procedure dbo.TestSaveTran
Go

Create Procedure dbo.TestSaveTran
As
Begin
Declare
@tranCount int

--Transaction Handling
Select @tranCount = @@TRANCOUNT
If (@tranCount=0)
Begin Tran localtran
Else
Save Tran localtran

Begin Try
--Simulate Error While Processing
RAISERROR('Something bad happened', 16, 1)

/*
If this proc started transaction then commit it,
otherwise return and let caller handle transaction
*/
IF (@tranCount=0)
Commit Tran localtran

End Try
Begin Catch
--Rollback to save point
Rollback Tran LOCALTRAN --<< NOTE case change
--Log Error
--Reraise Error
End Catch
End

Go

--Execute Stored Proc
Exec dbo.TestSaveTran

/*
Should receive the following message:

Cannot roll back LOCALTRAN. No transaction or savepoint of that name
was found.
*/

/* END CODE SNIPPET */

What is really strange, if there is a transaction open, then no error
is thrown. So if you execute as so:

/* START CODE SNIPPET */

Begin Tran
--Execute Stored Proc
Exec dbo.TestSaveTran

/* END CODE SNIPPET */

There is no "Cannot roll back LOCALTRAN...." message.

Questions:
1-)Can someone confirm save point names are case sensitve and this is
not happening because of a server setting?
2-)Is this a logic error that I am not seeing in the example code
above?

We have changed our code to store the save point name in a variable,
which will hopefully mitigate this "problem".

Thx.

[Back to original message]