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.
