Linked Server related

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 Wi开发者_开发知识库ndows 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.

---

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

---

You might want to try to disable remote transactions all together if you do not need to update the remote tables.

Go to the properties of the linked server (assuming you are using one) Select "Server Options" and set "Enable Promotion of Distributed Transactions" to "False".

It worked for me. ;)

---

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

---

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