Post reply

Tesing for Active Transaction

  • October 19, 2007 at 8:05 am

    #62870

    I am an application developer using VB6 with SQL Server 2000.

    Often in my code, I begin Transactions on my SQL Server database via ADO which are committed upon successful execution of the code, or rolled back in case of an error.

    In my error handler, I need to test whether the transaction is active before I roll it back, else it throws another error. Is there a way to do that in SQL?

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

  • October 19, 2007 at 10:10 am

    #743699

    select @@trancount

    Incremented when you start a new transaction, decremented when it's committed or roll'd back. If 0, no transactions for this connection.

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • October 19, 2007 at 3:27 pm

    #743800

    Rather than retrieve the value of @@trancount, test in the VB code and send a rollback if greater than 0, just always send this SQL:

    IF @@trancount > 0 rollback

    Simple and idiot proof.

    SQL = Scarcely Qualifies as a Language

  • October 20, 2007 at 12:51 am

    #743844

    Thanks to each of you; now, if I use this in VB6 simply as it is, will it work?:

    Cnn.Execute "If @@trancount > 0 RollBack"

    (where 'cnn' is my ADO Connection)

    Regards,

    goodguy

    Experience is a bad teacher whose exams precede its lessons

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply