Using SAVE TRANSACTION with a Linked Server

Question

Inside a transaction that has a savepoint, I have to make a connection to a table that is on a linked server. When I try to do this, I get an error message:

"Cannot use SAVE TRANSACTION within a distributed transaction" 

Remote table data rarely changes. It is almost fixed. Can I tell SqlServer to exclude this table from a transaction? I tried a hint (NOLOCK), but using this hint for a table on a linked server is not possible.

Does anyone know of a workaround? I am using ole SqlServer 2000.

Answer 1

In accordance with this The ability to use SAVEPOINT in a distributed transaction has been removed in SQL 7.

To allow application migration from Microsoft SQL Server 6.5 when savepoints are inside distributed transactions, Microsoft SQL Server 2000 Service Pack 1 specifies a trace flag that allows savepoints in a distributed transaction. The trace flag is 8599 and can be enabled during SQL Server startup or inside a separate session (that is, before enabling a distributed transaction with BEGIN DISTRIBUTED TRANSACTION) using the DBCC TRACEON command. When trace flag 8599 is set to ON, SQL Server allows you to use a savepoint in a distributed transaction.

So, unfortunately, you may need to discard the associated ACID transaction or modify the SPROC on the remote server so that it does not use SAVEPOINTs .

On the side note (although I saw that you marked it as SQL SERVER 2000), but to indicate that SQL SERVER 2008 has a remote proc trans Option for this.

Answer 2

One thing you can do is make a local copy of the remote table before starting the transaction. I know this may sound like a lot of overhead, but remote connections are often a performance issue anyway, and fixing the SOP for this also consists in making a local copy.

Answer 3

In this case, if the distributed table is not too large, I would copy it to the temporary table. If possible, enable any filtering to get the minimum number of rows. Then you can continue normally. Another option, since the data rarely changes, copies the data to a persistent table and checks to see if something has been changed to prevent a lot of data from being sent over the network every time you start a transaction. You could only pull on the latest changes.

Answer 4

If you want to process a transaction from the user interface level, and you have Visual Studio 2008 / .net fx 3.5 or + framework, you can wrap your logic with the TransactionScope class. If you don’t have any interfaces and only work on Sql servers, kindly ignore my answer ...