I’ve just finished working for a client who needed to insert multiple records into a SQL Server database from an Orchestration. This isn’t anything particularly hard using the SQL adapter, but after a review of the requirements it became apparent that further processing needed to be done after each insert. We therefore decided to move to stored procedures and add the additional processing at the database level.
This again shouldn’t have been a problem – we were simply generating a SQL Insertgram (are they all called that?) that executed the same stored procedure several times instead of performing several direct inserts.
Unfortunately, we encounted the following error as the response message was received back into the orchestration (I have omitted the message name for brevity):
Inner exception: Multi-part message [Msg Name] has body part 'BodyPart3', expected body part 'BodyPart1'.
It would appear that the ‘out-of-the-box’ SQL adapter shipped with BizTalk 2006 doesn’t like multipart messages being returned from the adapter and throws the message above. The KB article 918316 has all of the gory details – the hotfix simply updates the SQL adapter DLL to version 3.5.1612.0.
Following installation of the hotfix, the error disappears and multipart mesages can be successfully returned by the SQL Adapter.