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.
hehe，我自己也有自己的处理方法。方法与这个差不多。不过，现在这个方法更方便:)