INF: Limited Support for Savepoint in Distributed Transactions in SQL Server 2000 Service Pack 1

When a SAVE TRANSACTION statement is attempted within a distributed transaction, the following error message occurs: To allow application migration from Microsoft SQL Server 6.5 when savepoints inside distributed transactions are in use, Microsoft SQL Server 2000 Service Pack 1 introduces a trace flag that allows a savepoint within a distributed transaction. The trace flag is 8599 and can be turned on during the SQL Server startup or within an individual session (that is, prior to enabling a distributed transaction with a BEGIN DISTRIBUTED TRANSACTION statement) by using the DBCC TRACEON command. When trace flag 8599 is set to ON, SQL Server allows you to use a savepoint within a distributed transaction.

Trace flag 8599 is a migration tool for SQL Server 6.5 based applications and a legacy extension that will not be developed in future releases. Applications that rely on trace flag 8599 will not be able to take advantage of the most current and all new features of distributed transactions.

Savepoint support in a distributed transaction in SQL Server was removed in Microsoft SQL Server 7.0 and is being re-introduced in Microsoft SQL 2000 Service Pack 1. This new trace flag is being introduced primarily to allow an application written on SQL Server 6.5 that uses savepoints in distributed transactions to work unchanged in SQL Server 2000 and later.

Some new features in SQL Server 7.0 and SQL Server 2000 that require distributed queries are not compatible with trace flag 8599. Also, after a savepoint is set within a distributed transaction, all remote operations including remote "read only" are disallowed. Disallowed operations include:

• Distributed partitioned views.
• Distributed queries that use data modification statements (INSERT, UPDATE, DELETE).
• Remote stored procedures that use data modification statements (INSERT, UPDATE, DELETE).
• Queries that use OPENQUERY statements.
• Queries that use OPENROWSET statements.

When trace flag 8588 is enabled, client sessions that use the preceding operations terminate with the following error message: In addition, all new features planned for upcoming releases that require remote access will not be compatible with trace flag 8599.

Because there is no synchronization point support from Microsoft Distributed Transaction Coordinator (MSDTC) for a distributed transaction, a savepoint for a DTC transaction in one instance of SQL Server does not automatically set savepoints in other instances of SQL Server and components other than SQL Server that are involved in the DTC transaction. When you rollback a DTC transaction to a savepoint in one server, it does not automatically rollback other parts of the same transaction that are not on the same server.

REFERENCES

For more information about using trace flags and startup parameters, see the "DBCC TRACEON" and "Using Startup Options" topics in SQL Server 2000 Books Online.

To use trace flag 8599, obtain the latest service pack for SQL Server 2000: