Biztalk + Registry Access + 64 bits Server Machine

This week I found a problem with my Biztalk Solution deployed in an 64 bits server machine.

I have orchestrations with maps that have some Scripting Functoids witch use methods from a auxiliary dll. This dll have some methods that requirer access to a database. And I'm keeping the connectionstring in registry.

After the deploy of my orchestrations on 64 bits machine I ran a test and found that BTS couldn't access registry. So far so good, I forgot to give permission to the registry key where I saved connectionstring. After giving permissions I re-ran the test and got the same error!!!

I tried every thing to find the problem, even tried to find out what was happening with processmon (from sysinternals).

After some time, the problem was discovered. I was putting the registry key in HKLM\Software\MySoftware\ConnectionString and BTS was trying to get the registry key:
HKLM\Software\Wow6432Node\MySoftware\ConnectionString.
This happens because I'm dealing with a 64 bits machine and the SO internally "changes" the key I'm looking for ("Registry keys specific to 32-bit applications are stored in a branch under HKEY_LOCAL_MACHINE\Software\WOW6432Node.").
By the way the problem was found with regmon (processmon hides this errors about registry access).

This one was tricky to find because in my code I explicitly say I want to read HKLM\Software\MySoftware\ConnectionString registry key, and I made a test application that ran in the same machine with the same user that BTS and this test application was able to read the key (this happened because my test application was running in 64 bits and BTS is running in a 32 bit emulation environment - the Biztalk installed was the 32 bits version and should have been the 64 bits version).
A useful link to understand a little more about this 32/64 bits windows trouble.

Array in BTS Orc

Usefull Link

Another One

SQL Adapter Problems

Problem:
After I put my sql script I get the following error:
"Failed to execute SQL Statement. Please ensure that the supplied syntax is correct."

Some links that could help to resolve this problem:

1. Working With BizTalk Adapter for SQL Server
2. The SQL Transport Schema Generation Wizard closes unexpectedly in BizTalk Server 2006 or in BizTalk Server 2004

Heres another link of someone with a similar problem:

• Biztalk Adapter for SQL - Issues/Resolutions

TODO List

TODO LIST:
High Priority:
-Business Rule Engine (BRE)
-Business Activity Monitoring (BAM)
-Biztalk Throttling and Performance
Low Priority:
-Enterprise Single Sign-On (ESS)
-Pipeline
-Adapters

Still about the same problem

First, some interesting info:
What is message Tracking
Now lets return to the problem.
As you can see from my last post I had some problems with the index IX_TrackingMessageReferences (that exists on table TrackingMessageReferences lacated on db BiztalkMsgBoxDb).
I already posted the solution, but again as you can read on last post I had also a problem with HAT not saving messages anymore. First I thought this problem were related to the index, then after re-configuring BTS I thought it wasn't. Well the HAT after a while stopped working (actualy it stop working since the 3rd time I had a problem with the index of table TrackingMessageReferences ). After some research I found this (really useful post):

The solution if anyone else is looking for: in our case was that there were
bunch of rows in TrackingMsgReferences table which could not be joined in to
the spool table (sproc which gets called when this TrackedMsgs_Copy_BizTalk
gets called has a sql statement which joins these two tables above). Since
this job selects top 50 always it was never able to run (we had more than 50
non refd rows).

Once the rows were deleted, join was happy, sproc and the jobs were able to
run.

Spool table is coming down in the number of rows too.

I only had to delete all entries from TrackingMsgReferences and I was back on track.

Remarks:

The SQL Server Agent service must be running on all MessageBox databases. The TrackedMessages_Copy_ job makes message bodies available to HAT and WMI. To efficiently copy the message bodies, they remain in the MessageBox database and are periodically copied to the BizTalk Tracking (BizTalkDTADb) database by the TrackedMessages_Copy_ job. Having the SQL Server Agent service running is also a prerequisite for the archiving and purging process to work correctly.

Short and simple:
My problem was TrackedMessages_Copy_ job wasn't running because an error was occouring.
To correct the error I had to delete all entries from TrackingMsgReferences table (on BiztalkMsgBoxDb database).

23-May-2008 (Update):
Today I lost the hability of saving messages from HAT again, after trying to run the TrackedMessages_Copy_ job I found it was getting error again, this time because of index ix_tracking_parts1 (not in DB BiztalkMessageBoxDb but in the BizTalkDTADb.Tracking_Parts1). I had to use the first solution I mentioned in my last post. I had to select "Ignore duplicated entries" and re-run the TrackedMessages_Copy_ job.

store procedure call failed bts_UpdateMsgBox_BiztalkServerApplication

As we can see from the picture the problem is related with an insertion of a duplicated key row of index CIX Tracking Message References. After a brief search in the web I found some helping info:
KB 936124.
As we can see from link the resolution was to "select the Ignore duplicate values check box"* from Options menu of the target index (CIX_TrackingMessageReferences). After this change I stop having this type of error, however this was not enough because HAT stop working and I couldn't see tracked messages/orchestrations/pipelines anymore.

Finally I decided to "unconfigure" the BTS2006, to delete the DBs of BTS and reconfigure it from scratch. Of course this resolved all my problems.


* = To select this check box I had to 1) unselect the "unique index" check box, then 2) select the "ignore duplicated values" check box and finally 3) re-select the "unique index" check box.


Update (04 May 2008):
After less than a week the problem returned, this time I decided not to reconfigure the Biztalk. I tried once again the solution mentioned in this post, and this time it worked. The solution managed to correct the problem related with the index and I still have HAT running good. Conclusion: The other time something else mess up HAT.

Update (19 May 2008):
More useful information:
http://support.microsoft.com/kb/936124
http://support.microsoft.com/kb/936894/
http://support.microsoft.com/kb/917845/

The first one

This is the place where I should be posting some problems I encounter with Biztalk Development and their possible solutions.