Multiple Stored Procedures

Home Page › Forums › BizTalk 2004 – BizTalk 2010 › Multiple Stored Procedures

Viewing 1 reply thread
  • Author
    Posts
    • August 2, 2006 at 5:41 AM #15155
      community-content

        I’m not a big fan of calling Stored Procedures using the SQL Adapter. I’d suggest using .net class and create a single transaction to handle you calls.

        I’m not even 100% sure the SQL Adapter would support multiple proc calls in one transaction.

      • August 15, 2006 at 2:59 PM #15156
        community-content

          I’d just use .net code to call your SP’s. Then, call this .net code inside your Orchestration.

          This would by pass the SQL Adapter all together for calling the SQL SP’s. This way you can set your transaction levels yourself inside your .net code. Make sense?

          • August 15, 2006 at 4:53 PM #15157
            community-content

              I’d actually put it on a helper .net component and then reference it from your Orchestration.

              Think of it like you are using a WinForm to make the SP calls. In fact, I usually use a WinForm to test the .net class. I think this sample would be good to look at: https://www.biztalkgurus.com/Samples/Debatch-Smaller-Batch-Orchestration.html It uses a helper class called from the Orchestration.

              You would want to make sure your class is transactional and called inside an Atomic scope shape. I have done this before using Enterprise Services but I think using an ADO Transaction is better. Not real sure how to do that though

              • July 31, 2006 at 3:42 PM #15158
                community-content

                  I need to utilize an SQL adapter to process multiple stored procedures on a single transaction.

                  I have an EDI \”858 4010\” transaction from a customer which needs to be processed with ten separate stored procedures in a row. The first stored procedure inserts a record into a table in our database, and passes back an identification number (a primary key field) which is then needed to be passed into the remaining stored procedures.

                  Obviously, I will need to use both kinds of SQL adapters (request and request/response).

                  What is recommended and what are the best practices for a situation like this?

                  Thank you in advance for your support.

                  Mike

                  • August 14, 2006 at 3:06 PM #15159
                    community-content

                      [quote:c1d439e91d=\”Stephen W. Thomas\”]I’m not a big fan of calling Stored Procedures using the SQL Adapter. I’d suggest using .net class and create a single transaction to handle you calls.

                      I’m not even 100% sure the SQL Adapter would support multiple proc calls in one transaction.[/quote:c1d439e91d]

                      Stephen:

                      I’m not familiar with the \”.net class\” that was suggested. Could you please elaborate?

                      Thank you in advance for your repsonse.

                      Sincerely,

                      Mike

                      • August 15, 2006 at 3:38 PM #15160
                        community-content

                          [quote:91631eece1=\”Stephen W. Thomas\”]I’d just use .net code to call your SP’s. Then, call this .net code inside your Orchestration.

                          This would by pass the SQL Adapter all together for calling the SQL SP’s. This way you can set your transaction levels yourself inside your .net code. Make sense?[/quote:91631eece1]

                          Where would I write this code? In the BizTalk Expression Editor? Even though I don’t have any .net experience, I somewhat understand what you’re saying. I just need a little help with the \”.net code\” part.

                          Thank you in advance for your support.

                          Mike

                          • August 15, 2006 at 5:02 PM #15161
                            community-content

                              Although there are advantages to doing it in .NET if you are more SQL familiar can you create a wrapper stored procedure. Create a stored procedure that calls the first and takes the output param and puts it into the others. Then you call only one sp from BizTalk.

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