SA0211 : Setting REMOTE_PROC_TRANSACTIONS option is deprecated
Introduction
Section titled “Introduction”Ensure compatibility with future SQL Server versions by avoiding deprecated features like REMOTE_PROC_TRANSACTIONS .
Description
Section titled “Description”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:
-- Deprecated practice with REMOTE_PROC_TRANSACTIONSSET REMOTE_PROC_TRANSACTIONS ON;-- Code that utilizes remote stored procedure callsEXEC 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.
How to fix
Section titled “How to fix”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:
-- Corrected approach using a linked serverEXEC LinkedServer.MyDatabase.MyRemoteProcedure;The rule has a Batch scope and is applied only on the SQL script.
Parameters
Section titled “Parameters”Rule has no parameters.
Remarks
Section titled “Remarks”The rule does not need Analysis Context or SQL Connection.
Effort To Fix
Section titled “Effort To Fix”1 hour per issue.
Categories
Section titled “Categories”Deprecated Features, Bugs
Additional Information
Section titled “Additional Information”Deprecated Database Engine Features in SQL Server 2017
Example Test SQL
Section titled “Example Test SQL”SET REMOTE_PROC_TRANSACTIONS ONSET REMOTE_PROC_TRANSACTIONS OFFAnalysis Results
Section titled “Analysis Results”| Message | Line | Column | |
|---|---|---|---|
| 1 | SA0211 : Setting REMOTE_PROC_TRANSACTIONS option is deprecated. | 1 | 4 |
| 2 | SA0211 : Setting REMOTE_PROC_TRANSACTIONS option is deprecated. | 2 | 4 |