469,344 Members | 6,672 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,344 developers. It's quick & easy.

SQL Server 2005 - Save tran save point name case sensitive?

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.

Feb 10 '06 #1
4 8874
xAvailx (bz**************@gmail.com) writes:
I didn't find any documentation that notes save point names are case
sensitive, but I guess they are...


As with all other identifier, they depend on the collation, more
precisely, the database collation.

This sample demonstrates:

CREATE DATABASE CS COLLATE Finnish_Swedish_CS_AS
go
USE CS
go
BEGIN TRANSACTION
SAVE transaction Nisse
PRINT 'rollback in case-sensitive database'
ROLLBACK TRANSACTION NISSE
rollback transaction
go
CREATE DATABASE CI COLLATE Finnish_Swedish_CI_AS
go
USE CI
go
BEGIN TRANSACTION
SAVE transaction Nisse
PRINT 'rollback in case-insensitive database'
ROLLBACK TRANSACTION NISSE
rollback transaction
go
use master
go
DROP DATABASE CS
DROP DATABASE CI

When you develop, you should also use a case-sensitive database,
since if you develop on case-insensitive collation, and then deploy
on case-sensitive (because the customer so requires), you will have a
nightmare.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 11 '06 #2
Thanks for your time, Erland.

I checked the server and database collations. On both we are using the
English (US) default collation SQL_Latin1_General_CP1_CI_AS which
should be case insensitive.

I tried your sample code, and it worked as advertised. However, as soon
as I wrapped it in Try / Catch block, then I get the error in my
original post. Note that if I change, "Rollback Tran LOCALTRAN" to
"Rollback Tran localtran" I don't get any errors.

/* START SNIPPET */

Create Database dbCSTest Collate SQL_Latin1_General_CP1_CI_AS
Go

Use dbCSTest

Go

Create Procedure dbo.TestSaveTran
As
Begin

Begin Tran localtran

Begin Try

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

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

End

Go

--Execute Stored Proc
Exec dbo.TestSaveTran
Go
Use Master
Drop Database dbCSTest
/* END SNIPPET */

Feb 12 '06 #3
xAvailx (bz**************@gmail.com) writes:
I checked the server and database collations. On both we are using the
English (US) default collation SQL_Latin1_General_CP1_CI_AS which
should be case insensitive.

I tried your sample code, and it worked as advertised. However, as soon
as I wrapped it in Try / Catch block, then I get the error in my
original post. Note that if I change, "Rollback Tran LOCALTRAN" to
"Rollback Tran localtran" I don't get any errors.


It appears that TRY/CATCH has nothing to do with it. Rather, it appears
that the identifier for SAVE TRANSACTION follows the database collation,
whereas the identifier for BEGIN TRANSACTION is always case-sensitive!
And it's not that it has to do with the server collation. I usually run
with a case-sensitive server collation, but I tried this on a case-
insensitive server, and the behaviour appear to be the same. That was an
SQL 2000 instance, but there does not seem to be any difference between
SQL 2000 and SQL 2005.

This is so completely weird that I submitted
http://lab.msdn.microsoft.com/Produc...ckId=FDBK45704
and gave you credit for it. I did not vote it very high though.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Feb 13 '06 #4
>> Rather, it appears
that the identifier for SAVE TRANSACTION follows the database
collation,
whereas the identifier for BEGIN TRANSACTION is always case-sensitive!
<<

That explains why in the original stored procedure I wasn't receiving
an error if a transaction was started (It was falling in the save tran
block of the transaction handling).

Thanks for your help and time in figuring this out.

Thx.

Feb 13 '06 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by sathyashrayan | last post: by
4 posts views Thread by ~john | last post: by
10 posts views Thread by =?Utf-8?B?UHVuaXQgS2F1cg==?= | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.