SQL and DTC RRS feed

  • Question


    I have a set of 3 stored procedures that I need to be executed successfully or else roll back all three. How can I do this with the SQL adapter?I have tried with atomic shape but I am aware that that is only atomic to the message box. I also do not want to use compensations since it isn't my DB. Any ideas?

    Thursday, June 19, 2008 2:26 PM

All replies

  • Hi,


    If none of the stored procedures return result sets, and they are in the same DB, you can send a message like this to the SQL adapter:


    (i've skipped the namespace here)


    <NameOfStoredProcOne ParamName1="Foo" ParamName2="Bar" />

    <NameOfStoredProcTwo ParamName1="Foo" />

    <NameOfStoredProcThree ParamName1="Foo" ParamName2="Bar" ParamName3="Par" />



    The adapter will loop through the rcords, and call the procs in a transaction. This is probably not supported, but it works...


    You will have to modify the XSD for the other stored procs, and you can also make the records repeating and call one proc multiple times. This will not work if all three procs return info, and you can't make it work if you need a value returned by the first  to call the second and third procs.


    If you want to use the orchestration transaction model you will need to call the procs, then call compensation if one of them fails, it may do want you want, but it's not ideal.


    Another option could be to create a new proc to call the three procs within a transaction. You could also create a transactional data layer, and call that from an atomic scope in an orch.









    Thursday, June 19, 2008 7:35 PM
  • Alan,


    Thanks for the quick reply.


    The three stored procedures I need to call are in the same DB but I do need to use the return value. It is my foreign key for my other inserts.


    I am trying to avoid the master stored pocedure to which I send a massive amount of data to be sent to 3 different stored procedures. I have also thought about creating my own .Net assembly and calling it from the orchrestration but it seems to me that it goes against the biztalk architecture.


    Using compesation is also virtually impossible because each sp is 8000 lines of code and, besides the project manager is against touching the DB. 


    What did you mean by "transactional data layer, and call that from an atomic scope in an orch"? What is this transactional layer?  The atomic scope only goes as far as the message box.


    I realize I might have to do outside development but I am new to biztalk and want to make sure I am not reinventing the wheel.




    Thursday, June 19, 2008 8:28 PM
  • Just an FYI - the new WCF based SQL Adapter does support calling multiple SPs which return ResultSets/out parameters, within the same DTC transaction. More details available at


    Friday, June 20, 2008 9:13 AM