- This topic has 3 replies, 1 voice, and was last updated 6 years, 11 months ago by
.
-
Posts
-
-
Hello
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.
Any ideas?
JMan
-
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 autoThe SQL Adapter wizard will build a response schema that will have field1, field2, and orderid as elements.
Neal Walters
http://Biztalk-Training.com-
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
-
-
-
- The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.