"MS DTC has cancelled the distributed transaction" error in SQLCLR stored procedure

Question

I need to move some code from the old project to the new. Old project use DLL with stored procedures (32-bit version), but I need this DLL at 64-bit SQL Server, so I need to rewrite these procedures.

I am writing dll with stored procedures for SQL Server 2008. In Management Studio I load assembly, then create procedure by using:

CREATE PROCEDURE ...
AS EXTERNAL NAME 

Old DLL procedures just uses new connection to remote SQL Server to run stored procedure on it and return results.

So inside my procedure I create a SqlConnection to the remote server and run stored procedure on the remote server:

using (SqlConnection connection = new SqlConnection(String.Format("User ID={0};Password={1};Persist Security Info=True;Initial Catalog={2};Data Source={3}", Login, Password, DBName, ServerName)))
{
    connection.Open();
    SqlCommand command = new SqlCommand("Exec ProcName", connection);
    SqlDataReader reader = command.ExecuteReader();
    SqlContext.Pipe.Send(reader);
}

If I run this procedure in SSMS, it works. But in the old project it raises error:

The Microsoft Distributed Transaction Coordinator (MS DTC) has cancelled the distributed transaction.

MSDTC service runs, and I set all security parameters. How to fix that? Maybe there are other ways to run remote stored procedures (linked server), but I need to save old project functionality.

Answer 1

There are several things that are not quite right going on here:

1. Why are you rewriting anything? If you have the code, worst case is just recompiling for the new architecture.

2. Why are you doing anything in the first place? The code should be compiled against "Any CPU" (under "Platform target" in the "SQLCLR Build" tab of "Project Properties") and not specifically for either 32 bit or 64 bit. And if it was already compiled under "Any CPU", then there is nothing to do. Have you tested on the new system before starting any recompiling and/or rewriting?

3. Don't use String.Format to create the Connection String. Instead, use SqlConnectionStringBuilder:

   SqlConnectionStringBuilder _ConnectionStringBuilder =
                                    new SqlConnectionStringBuilder();
   
   _ConnectionStringBuilder.DataSource = ServerName;
   _ConnectionStringBuilder.InitialCatalog = DBName;
   _ConnectionStringBuilder.UserID = Login;
   _ConnectionStringBuilder.Password = Password;
   

4. Unless you absolutely have no choice and must use this option, do not specify Persist Security Info=True;

5. Instead of using new SqlCommand(), create the SqlCommand using:

   using(SqlCommand command = connection.CreateCommand())
   {
     command.CommandText = "Exec ProcName";
   }
   

6. Be sure to also specify command.CommandType = CommandType.StoredProcedure; so that it does an actual RPC call instead of an ad hoc query. This will require that you remove the text of "EXEC" from the current CommandText of "EXEC ProcName"; you can only specify [[DatabaseName.]SchemaName.]ProcName.

7. A SqlDataReader is a disposable object, just like SqlConnection and SqlCommand, so the SqlDataReader reader = command.ExecuteReader() should be wrapped in a using() construct.

Once the items noted above have been corrected, you should be able to fix the error by simply setting the following property of the SqlConnectionStringBuilder: _ConnectionStringBuilder.Enlist = false.

For more detailed information and examples related to working with SQLCLR, please see the series that I am writing on this topic on SQL Server Central: Stairway to SQLCLR (free registration is required to read content on that site).