DTC error while copying data from one table to another

Question

DTC error while copying data from one table to another

HSM_DBA

Right there with Babe

Points: 771
More actions
October 12, 2004 at 2:08 pm

#109199

I am getting an error copying data from a table in a database on one server to another database on a different server. The error reported is for Distributed Transaction Coordinator.
Command: "Insert into Table_Name Select * From [Server01].[DBName].[dbo].[Table_Name]"
The error reported is as below:
"Server: Msg 7391, Level 16, State 1, Line 2
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. ]"
The operation system is Windows 2003 Server Enterprise Edition and the SQL Server is also Enterprise Edition on both the servers.
All the services are started SQL Server, SQL Agent, MSDTC, Full Text Search.
In all my research I found articles on similar problems (and solutions for) Windows XP. Solutions needed changing registry setting, checking DLL versions, installing utilities to verify, changing firewall settings and of course needed a server reboot and restart of the MSDTC service.
But, I was not able to find anything for Windows Server 2003. This is not a production issue yet, so I have a little breathing room. Any response and help will be appreciated. Thanks in advance.
October 15, 2004 at 8:00 am

This was removed by the editor as SPAM

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Steve Jones-189739 Newbie Points: 3 More actions · Answer 1

Hi

having experienced this problem, my advice really comes from the perspective of having a firewall between your 2 servers.

This article is very useful - http://support.microsoft.com/?id=250367

Essentially, if you're using a firewall, you need to configure the registry on both sides of the firewall to tell each server which ports MSDTC should communicate over. You will also need to add these ports to your firewall policy along with port 135. Another gotcha is that you haven't configured MSDTC for Network Access - you can do this through running DCOMCNFG at the command prompt, right click my computer and select the MSDTC tab - click on Security Configuration and then select Network DTC Access, Network Administration and Network Transactions.

Search the web for DTCPING - a really useful troubleshooting app that will help

good luck

dchq

Tea Mugs Grasshopper Points: 14 More actions · Answer 2

This error is usually enconutered when your SQL Server is running on Windows 2003. You should enable Network DTC Access on the windows 2003 server.Just try to find out how this can be done in Win2k3 if u have no idea.