The Temple of Fu

A developer at work today encountered the error below when trying to insert stored procedure results into a temp table where the stored procedure contains a reference to a linked server. Well fortunately he fixed it and shared his information so I am sharing it with you.

The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a].

He enabled the MSDTC service but still received the error. In order to completely fix this error he had to perform the following to enable network access, by default the network access settings of MSDTC are disabled on new installations of SQL Server 2000 on computers that are running Windows Server 2003.

To work around this behavior, install network DTC access on both servers:

1. Click Start, and then click Control Panel.
2. Click Add or Remove Programs, and then click Add/Remove Windows Components.
   In the Components box, click Application Server, and then click Details.
3. Click to select the Enable network DTC access check box, and then click OK.
4. Click Next, and then follow the instructions that appear on the screen to complete the installation process.
5. Stop and then restart the Distributed Transaction Coordinator service.
6. Stop and then restart any resource manager services that participates in the distributed transaction (such as Microsoft SQL Server or Microsoft Message Queue Server).

References:
Microsoft Support