Help on SQL Adapter – URGENT!!!

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Help on SQL Adapter – URGENT!!!

Viewing 2 reply threads
  • Author
    Posts
    • June 5, 2008 at 1:54 AM #19857
      community-content

         Hi,

        I have a scenario where i have three tables say table1, table2 and table3. Table1 is the parent of table2 and 3.

        I have to insert data into this table1 and want to return the created primary key id and assign them to the fields in table2 and 3 through BizTalk orchestration, I am using the SQL adapter with insert updategram.

        Can anybody suggest how to return the created primary key id that will be created back to orchestration.

        Any solution will be appreciated.

        Thanks,

        SKL

      • June 5, 2008 at 4:24 AM #19859
        community-content

          A better method would be to pass all the data in one updategram and use the at-identity feature of updategrams to add child records

          http://msdn.microsoft.com/en-us/library/ms171764.aspx

          Alternatively, you could send the an Xml message to a stored procedure and shred the message and update tables using X-Query (requires SQL2005)

        • June 5, 2008 at 5:11 AM #19861
          community-content

            Personally, I’m use a Stored Procedure everytime I need to do any database work – that might be overkill, but I have complete control over the operation.

            Perform the insert into table1 in the SP and use the @@IDENTITY system function to retrieve the last-inserted identity value generated by the INSERT statement as value to assign to table2 and table3.

            More information can be found at BOL – @@IDENTITY (Transact-SQL)

            • June 5, 2008 at 2:09 PM #19866
              community-content

                Nothing wrong with stored procs, but recursively calling a stored proc to add single rows from an orchestration will not be the most performant solution.

                The datagram solution using at-identity is good for large amounts of data that are not too complicated.

                The alternative of a stored proc to process an Xml data object is the other way of not recursively calling SQL. See this example from Leonid  Ganeline http://geekswithblogs.net/LeonidGaneline/archive/2008/04/03/biztalk-sql-port-how-to-pass-several-rows-to-the.aspx.  This uses OPENXML and will work with SQL2000 or SQL2005. If you are using SQL2005 using XQuery instead of OPENXML will give better performance

                • June 10, 2008 at 8:14 AM #19886
                  community-content

                     

                    Thanks Guys,

                     

                    I have solved it. I wrote a new stored procedure in SQL for insert and then used the scope_identity() method to return the last updated identity.

                     

            • Author
              Posts
            Viewing 2 reply threads
            • The forum ‘BizTalk 2004 – BizTalk 2010’ is closed to new topics and replies.