MS States remote proc trans option will be removed in Next version of SQL SERVER...

Question

Hi All,

I'm not sure whether it is right to ask it here... I searched the net for an answer, But in vain... So I just wanted to try my luck here.

Here is the excerpt from Microsoft Website http://msdn.microsoft.com/en-us/library/ms189322(SQL.90).aspx

remote proc trans Option

Use the remote proc trans option to protect the actions of a server-to-server procedure through a Microsoft Distributed Transaction Coordinator (MS DTC) transaction. Set remote proc trans to 1 to provide a MS DTC-coordinated distributed transaction that protects the ACID (atomic, consistent, isolated, and durable) properties of transactions. Sessions begun after setting this option to 1 inherit the configuration setting as their default.

This feature will be removed in the next version of Microsoft SQL Server. Avoid using this feature in new development work, and plan to modify applications that currently use this feature.

This option is provided for compatibility with earlier versions of Microsoft SQL Server for applications that use remote stored procedures. Instead of issuing remote stored procedure calls, use distributed queries that reference linked servers, which are defined by using sp_addlinkedserver.

Does this means, we need to avoid using Transaction Scope Object on scenarios where it gets promoted to MSDTC ?

Note : This is the message for SQL Server 2005, but the same message appears for SQL Server 2008 also.

Answer 1

The document you reference is not talking about removing MSDTC, but removing the remote proc trans option from SQL Server.

This relates purely to MSDTC mediated stored procedure calls between SQL Servers (a legacy approach superseded by linked servers), and has no direct bearing on accessing SQL Server from the .net framework.

EDIT

remote proc trans is a SQL Server confguration option which deals with whether transactions between 2 or more SQL Servers where one server executes a stored procedure on another will use MSDTC.

As far as I understand it, .net transaction scope deals with transactions initiated by the .net framework, which may or may not use MSDTC. SQL Server transactions can be included in the transaction scope under MSDTC regardless of the remote proc trans setting.