Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2003
    Posts
    13

    Question Unanswered: Error 266 - Transaction count after EXECUTE issue

    Hi

    I am Migrating a database from MSSql to Sybase asn have a problem

    -- Script
    -- this script demonstrates the issue - the real problem
    -- the transaction management is taking place by the calling application
    -- and several levels of stored procedures are used of which some
    -- create temporary tables for interim results etc.
    --
    -- I have tried also ticking DDL in transaction on both the user current
    -- database and dbtemp
    --
    -- Note - if I change #tt1 to tempdb..tt1 the behaviour is different and
    -- and appears to work although I will need to manually add the
    -- drop table statements (that are not required and currently not
    -- present) in the original databse using the '#' syntax
    -- and also will no longer support multi-user access because these
    -- will no be global tables - looks like the # type tabes need to be
    -- created outside the transaction?

    create procedure p_p1
    as
    print 'in p_p1 - start'
    create table #tt1
    ( ID int, Name varchar(50) )
    print 'in p_p1 - logic'
    drop table #tt1
    print 'in p_p1 - end'
    go

    create procedure p_p0
    as
    print 'in p_p0- start'

    begin tran

    exec p_p1

    rollback tran

    print 'in p_p0- end'
    go


    exec p_p0

    -- Output

    in p_p0- start
    in p_p1 - start
    in p_p1 - end
    Server Message: Number 266, Severity 10
    Server 'ASE01', Procedure 'p_p0', Line 6:
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0.
    Server Message: Number 3701, Severity 11
    Server 'ASE01', Procedure 'p_p1', Line 15:
    Cannot drop the table '#tt', because it doesn't exist in the system catalogs.
    Server Message: Number 277, Severity 16
    Server 'ASE01', Procedure 'p_p1', Line 17:
    There was a transaction active when exiting the stored procedure 'p_p1'. The temporary table '#tt1' was dropped in this transaction either explicitly or implicitly. This transaction has been aborted to prevent database corruption.
    in p_p0- end
    (return status = 0)

    Any help appreciated
    Last edited by msg2dd; 11-25-03 at 08:48.

  2. #2
    Join Date
    Nov 2003
    Posts
    10

    Re: Error 266 - Transaction count after EXECUTE issue

    You can't create a temporary table within a transaction.
    You can code in following way..commit tran,create temp #table, begin tran,statement,commit tran.

    Or check your application code, somewhere the application developer might have coded begin tran.(I had this problem)

    HTH

    Originally posted by msg2dd
    Hi

    I am Migrating a database from MSSql to Sybase asn have a problem

    -- Script
    -- this script demonstrates the issue - the real problem
    -- the transaction management is taking place by the calling application
    -- and several levels of stored procedures are used of which some
    -- create temporary tables for interim results etc.
    --
    -- I have tried also ticking DDL in transaction on both the user current
    -- database and dbtemp
    --
    -- Note - if I change #tt1 to tempdb..tt1 the behaviour is different and
    -- and appears to work although I will need to manually add the
    -- drop table statements (that are not required and currently not
    -- present) in the original databse using the '#' syntax
    -- and also will no longer support multi-user access because these
    -- will no be global tables - looks like the # type tabes need to be
    -- created outside the transaction?

    create procedure p_p1
    as
    print 'in p_p1 - start'
    create table #tt1
    ( ID int, Name varchar(50) )
    print 'in p_p1 - logic'
    drop table #tt1
    print 'in p_p1 - end'
    go

    create procedure p_p0
    as
    print 'in p_p0- start'

    begin tran

    exec p_p1

    rollback tran

    print 'in p_p0- end'
    go


    exec p_p0

    -- Output

    in p_p0- start
    in p_p1 - start
    in p_p1 - end
    Server Message: Number 266, Severity 10
    Server 'ASE01', Procedure 'p_p0', Line 6:
    Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRAN is missing. Previous count = 1, Current count = 0.
    Server Message: Number 3701, Severity 11
    Server 'ASE01', Procedure 'p_p1', Line 15:
    Cannot drop the table '#tt', because it doesn't exist in the system catalogs.
    Server Message: Number 277, Severity 16
    Server 'ASE01', Procedure 'p_p1', Line 17:
    There was a transaction active when exiting the stored procedure 'p_p1'. The temporary table '#tt1' was dropped in this transaction either explicitly or implicitly. This transaction has been aborted to prevent database corruption.
    in p_p0- end
    (return status = 0)

    Any help appreciated

  3. #3
    Join Date
    Nov 2003
    Posts
    13

    Re: Error 266 - Transaction count after EXECUTE issue

    Tkanks for your help

    Yes - as it's a database migration I did not want to change the application and the application is calling begin trans before calling
    the procedure

    I currently have it working (in single user) using tempdb..tmp_tab
    instead of #tmp_tab (required DDL in tran option on) but will need
    to revisit it later

    here is a modified version of sometging I fould on the net
    I tried it in interactive mode and it does not give errors
    I have not verified this YET with the real database and application
    where I had in initial problem.

    -- test main will emulate the application
    create procedure main
    as
    begin tran
    exec a
    commit tran
    go

    create procedure a
    as
    create table #fred (a int, b datetime)
    exec b
    exec c
    drop table #fred
    go

    -- to make it visible to other procedure at build time
    create table #fred (a int, b datetime)
    go

    create procedure b
    as
    insert into #fred values( 1, '20020303 10:10:11' )
    go

    create procedure c
    as
    select * from #fred
    go

    drop table #fred
    go

    -- run test
    exec main
    - no errors - but is it the correct instance of #fred?

    Regards,
    msg2dd

    Originally posted by Hemendra
    You can't create a temporary table within a transaction.
    You can code in following way..commit tran,create temp #table, begin tran,statement,commit tran.

    Or check your application code, somewhere the application developer might have coded begin tran.(I had this problem)

    HTH
    Last edited by msg2dd; 11-27-03 at 13:30.

Posting Permissions

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