Linked Server related

Question

I have two instances of SQL Server:

• Server1 (SQL Server 2008)
• Server2 (SQL Server 2005)

I am executing a stored procedure from Server1 which references tables on Server2.

It is working fine in my test environment:

• Server1 runs Vista SP2, SQL Server 2008;
• Server2 runs Windows XP SP2, SQL Server 2005.

However, it is not working in the production environment:

• Server1 runs Vista SP1, SQL Server 2008;
• Server2 runs Windows XP SP2, SQL Server 2005.

The error message I receive is:

OLE DB provider "SQLNCLI10" for linked server "Server2" returned message "No transaction is active.".
Msg 7391, Level 16, State 2, Line 21
The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "Server2" was unable to begin a distributed transaction.

Answer 1

Have you configured MSDTC in production?

Two common causes are DTC not being started on one or all machines, and not having a suitable domain account for all DTC's

Answer 2

The "unable to begin a distributed transaction" usually means that DTC is disabled on one or both of the machines. To resolve:

• Start->Run dcomcnfg
• Expand Component Services, Computers
• Right-click My Computer, go to Properties
• Click MSDTC tab
• On Vista: Click "Default Properties" tab, check "Enable Distributed COM on this computer"
• On XP: Click "MSDTC" tab, click "Security Configuration", check "Network DTC Access".
• Click OK, restart MSDTC.

If that doesn't solve the issue then you may be experiencing this: KB954950. Have you made sure to install all the updates?

Failing that I'd probably head over to serverfault, I think they're more knowledgeable about this stuff...

Answer 3

Article on above issue is published on SQLVillage.com http://www.sqlvillage.com/Articles/Distributed%20Transaction%20Issue%20for%20Linked%20Server%20in%20SQL%20Server%202008.asp