Results 1 to 5 of 5
  1. #1
    Join Date
    Aug 2003
    Posts
    7

    Unanswered: Using COMMIT in stored procedures

    I want to Commit the a number of transactions, generated in one stored procedure, from another procedure.

    Is this posible?

  2. #2
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    I think you can do this with named transactions. Perhaps you can store the names of open transactions in a table from the sp that begins them, then read and commit them by name from the second sp. So something like this:

    DECLARE @tran_name VARCHAR(30)

    SET @tran_name='test1'

    INSERT INTO my_transactions
    SELECT @tran_name

    BEGIN TRANSACTION @tran_name

    .
    .
    .


    then in other sp:

    DECLARE @tran_name VARCHAR(30)

    SELECT @tran_name = transaction_name from my_transactions
    COMMIT TRANSACTION @tran_name
    DELETE FROM my_transactions WHERE transaction_name = @tran_name


    No idea if this will work, but I'd be interested in what you come up with.
    -bpd

  3. #3
    Join Date
    Aug 2003
    Posts
    7
    The problem is that the COMMIT TRANSACTION needs at BEGIN TRANSACTION, or els an error occurs.

  4. #4
    Join Date
    Aug 2003
    Location
    Andover, MA
    Posts
    256
    My suggestion is no good, as even named transations will go out of scope when the first sp ends.

    I am very curious as to why you would need to do this.

    Keep in mind that as long as the transaction is open, you will be locking resources in the database.
    -bpd

  5. #5
    Join Date
    Aug 2003
    Posts
    7
    I have a program that logs events in to a table and I only want the logging to occure when 500 events is checked and alright.

    The program have to use a stores procedure for the logging and one for the COMMIT.

Posting Permissions

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