Distributed Transaction Error

Distributed transaction error?

Enable the options

• Allow Remote Clients
• Allow Outbound

On Security tab of Local DTC Properties in Component Services.

• Go To Run, type comexp.msc.
• Double click "Console Root".
• Double click "Component Services.
• Double click "Computers".
• Double click "My Computer".
• Double click "Distributed Transaction Coordinator".
• Right click "Local DTC" under "Distributed Transaction Coordinator", and click properties.
• Click the "Security" tab.
• Put tick marks on the checkboxes "Allow Remote Clients" and "Allow Outbound".

Query Involving Linked Server Raises Distributed Transaction Error When No Transaction is Used or Needed

I discovered what was causing the attempt at a transaction in the first place (which was my goal, since I do not need a transaction). The procedure pulling data from the linked server was inserting that data into a #temp table using the SELECT ... INTO #temp pattern. Since the table it was selecting from was across the linked server, I believe it was creating the temp table there as well. Either that, or it was still creating the temp table locally, but since the selected table was across the linked server, it was trying to extended the transaction (implicit to the insert statement) to cover both local and the linked server.

Either way, when I changed to create the temp table in a separate statement before the INSERT ... SELECT statement, it worked without error!

What this doesn't explain to me is why the procedure would work (every time via SSMS, and rarely via .NET). I'm going to chalk that up to different execution plans somehow... and SQL Server voodoo magic.

Unable to begin a distributed transaction

Found it, MSDTC on the remote server was a clone of the local server.

From the Windows Application Events Log:

Event Type: Error

Event Source: MSDTC

Event Category: CM

Event ID: 4101

Date: 9/19/2011

Time: 1:32:59 PM

User: N/A

Computer: ASITESTSERVER

Description:

The local MS DTC detected that
the MS DTC on ASICMSTEST has the same unique identity as the local MS
DTC. This means that the two MS DTC will not be able to communicate
with each other. This problem typically occurs if one of the systems
were cloned using unsupported cloning tools. MS DTC requires that the
systems be cloned using supported cloning tools such as SYSPREP.
Running 'msdtc -uninstall' and then 'msdtc -install' from the command
prompt will fix the problem. Note: Running 'msdtc -uninstall' will
result in the system losing all MS DTC configuration information.

For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.

Running

msdtc -uninstall
msdtc -install

and then stopping and restarting SQL Server service fixed it.

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

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).

The operation could not be performed because OLE DB provider SQLNCLI11 for linked server was unable to begin a distributed transaction

If after configuring your MS Distributed Transaction Coordinator (MSDTC) on the two SQL server's according to the OP's original post, you still get "no transaction active", you should check that each host is reachable via the IP (assuming that's what you've used) registered in the linked server.

For example; on a recent setup, two SQL servers were reachable through a network in the 192.168.200.x range (same subnet), but each server was also indirectly connected through an IP in the 10.x.x.x range. On one SQL Server, the DNS server it used kept resolving the target SQL server to it's 10.x.x.x IP (which was firewalled) even though the linked server entry used the IP in the 192.168.200.x of the target server.

It appears that MSDTC uses the hostname of the server, while SQL server connects over any linked connection using the IP or hostname defined in the linked server entry, leading to this confusing behaviour of apparent connectivity when checking the target linked server within SQL Management Studio, but inability to execute remote procedures on the target.

The solution was to add entries in the host file's (%windir%\system32\drivers\etc\hosts) to explicitly force each SQL server to resolve the other to the IP address on the 192.168.200.x network.

On host 1 (IP 192.168.200.15):

# TARGET SERVER
192.168.200.20    targetserverhostname.and.any.domain.suffix  targetserverhostname

On host 2 (IP 192.168.200.20)

# SOURCE SERVER
192.168.200.15    sourceserverhostname.and.any.domain.suffix sourceserverhostname

Don't forget to ensure MSDTC has been configured according to the OP's screenshot above allowing network access and (if required) No Authentication.

---

Related Topics