Professional Web Applications Themes

Programmatic BEGIN TRAN, ROLLBACK and COMMIT - Microsoft SQL / MS SQL Server

Hi, I know how to use BEGIN TRAN before SQL statements in the Query yser (SQL 2000) and the either COMMIT or ROLLABCK as required. I have a VB application that will be updating large amounts of data in our database. My Ideal solution would be to programmatically BEGIN TRAN before the updates occur and then COMMIT once it has completed but with the option to rollback if the application runs into unrecoverable trouble. My questions are: 1.) How do you BEGIN TRAN, COMMIT and ROLLBACK in a stored proc call from VB using ADO? 2.) When calling BEGIN TRAN ...

  1. #1

    Default Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    Hi,

    I know how to use BEGIN TRAN before SQL statements in the Query yser
    (SQL 2000) and the either COMMIT or ROLLABCK as required.

    I have a VB application that will be updating large amounts of data in our
    database. My Ideal solution would be to programmatically BEGIN TRAN before
    the updates occur and then COMMIT once it has completed but with the option
    to rollback if the application runs into unrecoverable trouble.

    My questions are:

    1.) How do you BEGIN TRAN, COMMIT and ROLLBACK in a stored proc call from VB
    using ADO?

    2.) When calling BEGIN TRAN how many operations can be completed before
    being UNABLE to rollback? Also Is a BEGIN TRAN limited to one per
    connection, i.e, if I BEGIN TRAN, open a connection, make updates, close
    connection, make another connection, make more updates, can I now COMMIT or
    ROLLBACK? Or is the the transaction commited once the connection is closed?

    3.) Is there a better way of insuring data integrity while updating
    information?

    Thanks in advance

    MattC


    MattC Guest

  2. #2

    Default Re: Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    Here are the answers:
    (1) You just need to decorate your stored procedure with calls to BEGIN
    TRAN, COMMIT and ROLLBACK.
    (2) For ordinary VB components, a transaction spans for the connection.
    Thus, if you open a connection and then close it, the transaction context is
    lost. Thus the next open connection is treated like a new transaction.
    (3) If you want to do large updates by repeatedly connecting to the
    database, develop an ActiveX DLL and deploy it in COM+. You can mark the
    component with various transaction attributes. COM+, among other things can
    co-ordinate distributed transactions. Thus for your case, the component in
    COM+ can open a connection, do some work, close the connection, open it
    again and do some work and then when the component is destroyed, all
    operations can be committed or rolledback.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "MattC" <mm.com> wrote in message
    news:ur%phx.gbl... 
    before 
    option 
    VB 
    or 
    closed? 


    SriSamp Guest

  3. #3

    Default Re: Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    So if i open the connection then do all my database updates in that one
    connection i can roll back all the of the work done.

    Is there a time limit on how long a transaction can be. For example if I
    open a conenction then run a stored proc that just has BEGIN TRAN in it,
    then I call several other stored procs over a period of time for doing
    updates, then finally call a stored proc that calls either COMMIT or
    ROLLBACK and then close the connection. Will that work???


    MattC

    "SriSamp" <co.in> wrote in message
    news:e$phx.gbl... 
    is 
    can [/ref]
    our 
    > before 
    > option [/ref]
    from 
    > or 
    > closed? 
    >
    >[/ref]


    MattC Guest

  4. #4

    Default Re: Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    Comments:
    (1) So if i open the connection... ==> Yes. As long as all operations are
    done within the scope of one connection, you can commit or rollback.
    (2) Is there a time limit on ... ==> The time limit depends on the
    connection timeout that is set by your driver. This is a configurable option
    that you can set in your code. Thus, if you know that your SP is going to
    take a long time, you can modify this value.
    --
    HTH,
    SriSamp
    Please reply to the whole group only!
    http://www32.brinkster.com/srisamp

    "MattC" <mm.com> wrote in message
    news:OR2$phx.gbl... [/ref]
    context 
    > can [/ref]
    in [/ref][/ref]
    yser [/ref]
    > our 
    > > before 
    > > option [/ref]
    > from [/ref][/ref]
    before [/ref][/ref]
    close [/ref][/ref]
    COMMIT 
    > > closed? 
    > >
    > >[/ref]
    >
    >[/ref]


    SriSamp Guest

  5. #5

    Default Re: Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    How do I determine if its in the scope of the connection? As long as I ise
    the same ADO connection object for all calls to stored procs?

    Can you have a stored proc with just BEGIN TRAN, COMMIT or ROLLBACK in it?

    MattC

    "SriSamp" <co.in> wrote in message
    news:e%phx.gbl... 
    option [/ref]
    I [/ref][/ref]
    BEGIN [/ref][/ref]
    connection. [/ref]
    > context [/ref][/ref]
    the [/ref][/ref]
    things [/ref][/ref]
    component [/ref][/ref]
    it [/ref]
    > yser [/ref][/ref]
    in 
    > > from [/ref]
    > before [/ref]
    > close [/ref]
    > COMMIT 
    > >
    > >[/ref]
    >
    >[/ref]


    MattC Guest

  6. #6

    Default Re: Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    Hi MattC,

    I'm looking into this issue right now and I will response you later.

    Thank you,

    Michael Shao
    Microsoft Online Partner Support
    Get Secure! - www.microsoft.com/security
    This posting is provided "as is" with no warranties and confers no rights.

    Michael Guest

  7. #7

    Default Re: Programmatic BEGIN TRAN, ROLLBACK and COMMIT

    Thanks Michael,

    I think that's cleared that one up. I can use the same connection object
    calling BeginTrans on the object and setting its isolation level to
    adXactIsolated. Each stored proc called on that connection can then run
    freely in that transaction. If there is no error then the application can
    call CommitTrans on the connection object and close the connection. If
    there is an error/crash the transaction can be rolledback or, in the event
    of a crash, SQL will rollback the transaction.

    Cool

    MattC


    "Michael Shao [MSFT]" <microsoft.com> wrote in message
    news:phx.gbl... 
    the 
    transaction 
    TRANSACTION 
    while 
    is 
    ROLLBACK 


    MattC Guest

Similar Threads

  1. rollback or commit?
    By kt03 in forum Coldfusion - Getting Started
    Replies: 6
    Last Post: October 25th, 08:27 PM
  2. What is postgresql doing for "statement: commit;begin;"
    By Nick Burch in forum PostgreSQL / PGSQL
    Replies: 2
    Last Post: January 17th, 04:25 PM
  3. Commit ... Rollback in Access
    By Astra in forum ASP Database
    Replies: 0
    Last Post: April 30th, 11:41 AM
  4. tempdb tran log
    By Mark Denham in forum Informix
    Replies: 0
    Last Post: August 21st, 01:59 PM
  5. Commit inner transaction rollback outer
    By Kresimir Radosevic in forum Microsoft SQL / MS SQL Server
    Replies: 0
    Last Post: July 2nd, 08:18 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139