Gobbledygooks by Richard Hallgren

Recently I had to insert a record using a stored procedure and the SQL Adapter in BizTalk 2006. There are lots of examples on both how to insert records and how to select a number of record using this adapter. However I had problems finding how to insert a record and receiving the new id of the inserted row in return (the SCOPE_IDENTITY()). In my scenario I needed the id to insert into another database further down in the orchestration.

I ended up with a stored procedure looking like the below.

ALTER PROCEDURE [dbo].[TestInsertParty]
    @partyName nchar(30) = NULL
AS
BEGIN
    SET NOCOUNT ON;
    Insert Into Party ([name], chain_idx) Values(@partyName, NULL)
    Select Scope_Identity() As Id For Xml Raw ('Response')
END

The trick was to use the XML RAW Mode. This mode transforms the result set into a generic identifier as <row>. It is however possible to provide a element name, as <Response>.  Basically this will insert the new value and return something like this from the stored procedure.

<Response Id="1054" />

After return via the send port the orchestration will receive something like the below.

<TestInsertResponse xmlns="TestInsert">
    <Response Id="1054" />
</TestInsertResponse>

The schema that I use to both handling the response and request against the SQL Adapter is shown below. First I set the type of the Id-attribute to xs:int but this gave me some problems when using the promoted value in the orchestration, everything worked fine when switching back to xs:string.

 
The same technique would be used for receiving a code from the stored procedure (say 1 for success and 0 for failure or whatever) and then to make a logical decision in the orchestration.

This entry was posted on Wednesday, April 18th, 2007 at 2:11 pm and is filed under BizTalk 2006.