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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-09-12 : 07:35:32
|
| Richard writes "I have a number of stored procedures in a SQL 2000 SP4 database on Windows 2000 SP4, each coded to a standard which includes a section: if (@@TRANCOUNT = 0) begin transaction sp_xxxxx else begin set @save = TRUE -- true. save transaction sp_xxxxx endwhich allows us to rollback to the savepoints, if necessary.These have worked fine for a number of years. I now have another system which uses these same stored procedures via a linked server. When these stored procedures are executed from the remote server they are within a distributed transaction and I get the error "Cannot use SAVE TRANSACTION within a distributed transaction". Fair enough, I though I'll just test to see if I am in a distributed transaction and if so I'll bypass that bit of code. But I can't find any way to determine if I am in a distributed transaction - Anyone any ideas of how I can find out if I am in a distributed transaction or of how I can achieve my objectives a better way?I know there is trace flag 8599 to allow savepoints in distributed transactions but I don't want to resort to that as there is a big chance it won't exist in future versions.Thanks." |
|
|
|
|
|
SAVE TRANSACTION in DISTRIBUTED TRANSACTION