Monday, September 26, 2005

BizTalk and MSDTC

I ran into a problem a while ago where the BizTalk SQL Adapter wizard would emit this error when I tried to create a new SQL adapter schema to pull data from a stored procedure.

Failed to execute SQL Statement. Please ensure that the supplied syntax is correct. An error occurred while enlisting in a distributed transaction
Transaction manager has disabled its support for remote/network transactions

I didn't have the error against my local SQL Server instance. It would happen when I tried it against the shared SQL Server machine the group used as a development sandbox.

I tried all of the steps mentioned at....

I eventually figured it was due to Windows XP SP2 and MSDTC and that SQL Server was not local. Looking into it a little more, I found this article from Microsoft....

How to troubleshoot MS DTC firewall issues

As mentioned in the article, I installed and used both the DTCPing tool and the DTCtester tool to identify the problem. I could DTC ping the server but the server could not DTC ping my laptop. For some reason, since my laptop was not a full member of the domain, the server had an invalid IP address for my laptop.

I fixed the issue by adding my laptop to the server's host file (c:\windows\system32\drivers\etc\hosts) with my correct IP address.

Again, this was only required in the development environment, and only because of the unusual situation my laptop was in (I'm a consultant and my laptop is not part of the domain and moves between many different networks).

More on DTCPing...
and general MSDTC debugging...

No comments: