My client is an early adapter of SQL 2005, maybe going production in August.
We have several Biztalk applications that need to be able to update and retrieve from production databases.

Basically, everybody gives the party line that you have to have Biztalk 2006 to work with SQL2005. I think when they say this, they mean that to have the Biztalk databases running on SQL2005, including BAM/BAS and all that fun stuff.

We are content to leave our Biztalk 2004 databases on SQL 2000, but need the Biztalk 2004 system to call stored procedures on SQL 2005.

Since there are no definitive answers here as to why it won’t work – we
decided to try it and see for ourselves what would happen.

We created a simple stored proc on SQL 2005 (returning a small table with
“for xml auto, elements”. We create a receive port, receive location, and
flat file send port that subscribed to the receive port. After starting the
receive-location which was polling SQL eveyr 30 seconds, we got the same
error every 30 seconds in the application event log:

New transaction cannot enlist in the specified transaction coordinator.

MS-DTC is up and running and looks fine on SQL-2005.

There is a great program called “DTCTester”:
http://support.microsoft.com/default.aspx?scid=kb;en-us;293799
We run this utility on a server that is running BTS 2004.
Our observation so far is that if this utility won’t communicate with the DTC on SQL 2005, then Biztalk won’t be able either.

For some reason yet undiscovered, our development environment will not communicate using the DTCTester program, so we tried our Biztalk QA system and it worked. Thus we repeated the same test described above on our QA server, and it worked fine. Biztalk was able to call a retrieval Stored Proc on SQL 2005 and it worked great.

I’ll keep you posted on what happens next.

Updated 08/02/2005 – Our development system is now running SQL2005. Biztalk databases are still on SQL2000, and everything working fine.