locked
How to wrap Multiple stored procedures in one transaction RRS feed

  • Question

  • I am using VS2015, C#, and MS SQL Server 2016. I am converting my VB6 Multi-user desktop application to C# using Windows forms.

    I need to create a Stored procedure "A"  that wraps several other stored procedures ("B", "C" and "D") in a transaction. If one stored procedure in the stored procedure "A" fails, I need all of the stored procedures that completed successfully to be rolled back, AND an error returned to the C# Try,Catch block in the Application that called the Stored procedure "A".

    I have spent several 12 hour days trying to wade through the ridiculously confusing documentation in the Help files.

    I am not even sure that what I want to do is possible. If it is and you know how to do this, please let me know how, your help will be sincerely appreciated.

    Ed


    A giraffe is a horse designed by a committee.

    Friday, March 9, 2018 8:22 PM

Answers

  • Hello,

    see "Handling transactions in nested stored procedures" What is shown would be done with the data provider SqlClient. The article with source explains on an issue an non-fatal error code is returned and things get rolled back.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Stanly Fan Monday, March 12, 2018 2:37 AM
    • Marked as answer by ChickenLegWilly Monday, March 12, 2018 12:14 PM
    Friday, March 9, 2018 9:09 PM

All replies

  • Hello,

    see "Handling transactions in nested stored procedures" What is shown would be done with the data provider SqlClient. The article with source explains on an issue an non-fatal error code is returned and things get rolled back.


    Please remember to mark the replies as answers if they help and unmark them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    • Proposed as answer by Stanly Fan Monday, March 12, 2018 2:37 AM
    • Marked as answer by ChickenLegWilly Monday, March 12, 2018 12:14 PM
    Friday, March 9, 2018 9:09 PM
  • I am testing this out now. It seems like it will work, but I want to be sure before I mark it as the answer. The link you sent sure has a much clearer explanation and sample than Microsoft does.

    Thanks.


    A giraffe is a horse designed by a committee.

    Friday, March 9, 2018 11:02 PM
  • I am testing this out now. It seems like it will work, but I want to be sure before I mark it as the answer. The link you sent sure has a much clearer explanation and sample than Microsoft does.

    Thanks.


    A giraffe is a horse designed by a committee.

    Hi,

    If Karen's reply provided you with a solution please mark the reply as answered as this will help others looking for the same or similar issues down the road.

    Regards,

    Frankie


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, March 12, 2018 2:44 AM