SA0211 : Setting REMOTE_PROC_TRANSACTIONS option is deprecated

When developing or maintaining T-SQL code for SQL Server, using deprecated features like REMOTE_PROC_TRANSACTIONS can lead to issues in future releases, as these features may be removed. The REMOTE_PROC_TRANSACTIONS option relates to remote stored procedure transactions and is supported for historical reasons. However, its reliance can limit forward compatibility.

For example:

1-- Deprecated practice with REMOTE_PROC_TRANSACTIONS
2SET REMOTE_PROC_TRANSACTIONS ON;
3-- Code that utilizes remote stored procedure calls
4EXEC RemoteServer.MyDatabase.MyRemoteProcedure;

This example illustrates the problem because the reliance on REMOTE_PROC_TRANSACTIONS means the code may no longer work when the feature is removed in future SQL Server versions. Instead, developers should use distributed queries and linked servers setup which offer a more robust and compatible approach.

• Future SQL Server updates will likely remove the deprecated option, causing potential functionality and compatibility issues.

• Using distributed queries with linked servers, defined by sp_addlinkedserver, provides a modern and efficient alternative.

Modify your T-SQL code to avoid using deprecated features like REMOTE_PROC_TRANSACTIONS to ensure compatibility with future SQL Server releases.

Follow these steps to address the issue:

1. Identify any instances in your code where REMOTE_PROC_TRANSACTIONS is set. This is typically done using SET REMOTE_PROC_TRANSACTIONS ON.

2. Replace deprecated remote procedure transaction handling with distributed queries using linked servers. Set up linked servers using sp_addlinkedserver to connect and run remote procedures or queries.

3. Update any code that executes remote stored procedures to utilize the linked server configuration. Use EXEC with linked server syntax for invoking remote procedures or SELECT queries that span multiple servers.

For example:

1-- Corrected approach using a linked server
2EXEC LinkedServer.MyDatabase.MyRemoteProcedure;

The rule has a Batch scope and is applied only on the SQL script.

Rule has no parameters.

The rule does not need Analysis Context or SQL Connection.

Deprecated Features, Bugs

Deprecated Database Engine Features in SQL Server 2017

Other Resources