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.

I was looking to accomplish via software the process of going to HAT, listing all Serivce Instances, and then selectively terminating service instances that have a specific error message.

We have some “errors” that aren’t really errors. In other words, our orchestrations get suspended when we call a web service to a legacy system, and for example the shipping address if over 30 characters. We didn’t try this error, and right now I’m not going to suggest we change and remigrate the orchestrations. So I simply want to run a daily script that finds all suspended orchestrations that have “shipping addres over 30” in the error message, and terminate them. That way, when a person looks in HAT, only the more interesting errors are seen.

The WMI to list the suspended service instance took about six minutes to run, while HAT took about six seconds or less. So I finally decided that HAT had to be using SQL directly, not using WMI, and I sought out on a trek to find how HAT did what it did. I used SQL profiler to start a trace and capture all SQL command to the Biztalk databases, then went to HAT, ran the desired processes, when went back to profiler to see what the real SQL commands actually were.

To list the suspend service instances, this is the call to a stored proc:

use BiztalkMsgBoxDb
exec MBOM_LookupInstances @nvcHost = NULL, @nServiceClass = 1, @uidServiceType = NULL, @uidInstanceID = NULL, @nvcUserState = NULL, @nStatus = 63, @nPendingOperation = 15, @dtFrom = NULL, @dtUntil = NULL, @nMaxMatches = 200

To delete one of the suspended service instances, call this Stored Proc and pass the three GUIDs:

exec [dbo].[bts_AdminCompleteTerminate_BizTalkServerApplication]
@uidInstanceID = N'{0E6DF4B2-76D4-46D8-AF80-9B0C07907E38}’,
@uidServiceID = N'{7825F761-716A-5253-7592-A62A4D711BD1}’,
@uidClassID = N'{226FC6B9-0416-47A4-A8E8-4721F1DB1A1B}’,
@nResult = 1

So now, I am using a VBScript with ADODB to run the stored procs, and I’m happy with the results.