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

http://blogs.msdn.com/florinlazar/archive/2004/06/18/159127.aspx
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;839187
and
http://support.microsoft.com/default.aspx?scid=kb;en-us;301600
and
http://cs.orange.us/blogs/thomas_mullaly/archive/2005/03/26/27.aspx


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...
http://blogs.msdn.com/florinlazar/archive/2005/09/16/469064.aspx
and general MSDTC debugging...
http://www.chriskempster.com/articles/debugdtcissues.htm