March 2, 2006 at 11:34 PM #13039
I have a system that receives an XML Purchase Order file and populates two tables. The ‘orders’ table and ‘orderDetails’ table. The Orders table generates a unique ID which is set in the order details table for each lineItem that gets inserted agaianst that order. That all works very well. But now I have to get a status result returned. If sucessfully inserted then do this else do something else. The trick is getting the UniqueID returned in teh status message.
March 3, 2006 at 3:59 PM #13040
Hmmm, so the response above is telling us how to get the return code back from a stored proc to Biztalk. I have always wondered how to do that!
You can also just return data in an SQL statement in your stored proc.
Are you already doing that? i.e. you have a request/response, right?
The response is the result of an SQL statement at the end of the stored proc that has the \”for xml auto\” clause.
If your order has an identity column, you can access the system variable @@identity (in the SQL stored proc) and return it as a \”normal\” column with the as statement.
Something like this:
Select field1, field2, @@identity as orderid
for xml auto
The SQL Adapter wizard will build a response schema that will have field1, field2, and orderid as elements.
March 3, 2006 at 5:17 AM #13041
JMan, I am assuming you are still using the Insert New Order example on MSDN
You need to use the updg:returnid attribute
1. Open the [b:eceb982134]InsertOrdersService [/b:eceb982134]schema in the BizTalk Editor.
2. Right-click the [b:eceb982134]after[/b:eceb982134] node, click [b:eceb982134]Insert Schema Node[/b:eceb982134], and then click [b:eceb982134]Child Field Attribute[/b:eceb982134].
3. Locate the [b:eceb982134]Data Type [/b:eceb982134]property of the new attribute, and then select the [b:eceb982134]updg:returnid[/b:eceb982134](Reference) type located at the bottom of the list.
4. Right-click the [b:eceb982134]InsertOrderResponse[/b:eceb982134] node, click [b:eceb982134]Insert Schema Node[/b:eceb982134], and then click [b:eceb982134]Any Element[/b:eceb982134]. Set [b:eceb982134]Process Contents[/b:eceb982134]=Skip and [b:eceb982134]MaxOccurs[/b:eceb982134]=unbounded
5. Save the schema.
6. Open the map that creates the updategram
7. Connect the [b:eceb982134]updg:returnid[/b:eceb982134] to the same concatenate functoid as the [b:eceb982134]updg:at-identity[/b:eceb982134] attribute on the [b:eceb982134]Orders[/b:eceb982134] node
8. Change the concatenate functoid string parameter from 1 to X
9. Open the orchestration
10. Create a message using the [b:eceb982134]InsertOrderResponse[/b:eceb982134]
11. Change the [b:eceb982134]SendOrderUpdate[/b:eceb982134] port to a Request/Response port.
I am not sure of the format of the reponse message, you may have to write it to a file
March 3, 2006 at 7:02 PM #13042
This is the method of returning the identity from an updategram not a stored proc
It extends the example of adding a new order to SQL found here:
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.