Retrieving Unique ID

Home Page Forums BizTalk 2004 – BizTalk 2010 Retrieving Unique ID

Viewing 1 reply thread
  • Author
    Posts
    • #13039

      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

    • #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.

      Neal Walters
      http://Biztalk-Training.com

      • #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

        • #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:
          [url]http://msdn.microsoft.com/library/default.asp?url=/library/en-us/BTS_2004WP/html/74cfc9d0-0974-4f4a-81f5-6768ff245df1.asp[/url]

Viewing 1 reply thread
  • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.