none
SQL Transactions Rollback, Commit

    Question

  • Hey,

     

    I am using a stored procedure that has numberous insert and update statements within it, I have noticed that sometimes parts of this stored procedure can fail therefore I am trying to implement "transactions" into this SP but I am having a lot of trouble,

     

    Here is a snippit of the SP, Basically when I force the SP to fail (for testing) if i have the "COMMIT" after the first transaction it works fine it just raises the error, but if i add a second transaction after the next insert and place the "COMMIT" after it also it will error, giving me the following message;

     

    "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing.

    Previous count = 1, current count = 2."

     

    BEGIN TRANSACTION

     

    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)

    INSERT INTO #Activities

    SELECT aa.[id],jp.[accrual],jp.[lateaccrual]

    FROM accountactivity as aa,accounts as a,journalsperiods as jp

     

    IF @@Error <> 0

    BEGIN

    ROLLBACK TRAN

    RETURN

    END

     

    CREATE TABLE #Trans ([transactionID] UNIQUEIDENTIFIER)

    INSERT INTO #Trans

    SELECT DISTINCT t.[id] as transactionID

    FROM transactions as t

    IF @@Error <> 0

    BEGIN

    ROLLBACK TRAN

    RETURN

    END

    COMMIT

     

    Any help would be great, thanks  !!

     

    Monday, September 29, 2008 3:54 PM

Answers

  •  

    Hi,Karl Gregory 

     

    SQL2000:

     

     

    Code Snippet

    BEGIN TRANSACTION

     

    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)

    INSERT INTO #Activities

    SELECT aa.[id],jp.[accrual],jp.[lateaccrual]

    FROM accountactivity as aa,accounts as a,journalsperiods as jp

    IF @@Error <> 0
     goto error
     

    CREATE TABLE #Trans ([transactionID] UNIQUEIDENTIFIER)

    INSERT INTO #Trans

    SELECT DISTINCT t.[id] as transactionID

    FROM transactions as t

    IF @@Error <> 0
     goto error

    goto successful

    error:
     ROLLBACK TRAN

    successful:
    if @@trancount>0
     commit tran

     

     

     

    SQL2005:

    Code Snippet

    begin try
    begin tran


    if object_id('Tempdb..#Activities') is not null
     drop table #Activities

    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)

    INSERT INTO #Activities

    SELECT aa.[id],jp.[accrual],jp.[lateaccrual]

    FROM accountactivity as aa,accounts as a,journalsperiods as jp

    if object_id('Tempdb..#Trans') is not null
     drop table #Trans

    CREATE TABLE #Trans ([transactionID] UNIQUEIDENTIFIER)

    INSERT INTO #Trans

    SELECT DISTINCT t.[id] as transactionID

    FROM transactions as t

    commit tran
    end try
    begin catch
     declare @error nvarchar(1000)
     set @error=error_message()
     raiserror 50001 @error
     rollback tran
    end catch

     

     

    Tuesday, September 30, 2008 10:08 AM

All replies

  • Hey Karl .. can you please provide more detail on how you make the SProc fail and also the code that throws the said error

     

    Monday, September 29, 2008 8:59 PM
  • Hey Sen,


    thanks for your reply


    When I am trying to make it fail I re-name a column, e.g for the below the column accrual I would rename it to accrualTest. This is an obvious error the rollback catches it and says "Invalid column name 'accrualTest'."


    but unfortunitly if i have a second rollback it throws the same error plus another one that I mentioned above


    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)

    INSERT INTO #Activities

    SELECT aa.[id],jp.[accrualTest],jp.[lateaccrual]

    FROM accountactivity as aa,accounts as a,journalsperiods as jp

    Tuesday, September 30, 2008 8:24 AM
  •  

    Hi,Karl Gregory 

     

    SQL2000:

     

     

    Code Snippet

    BEGIN TRANSACTION

     

    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)

    INSERT INTO #Activities

    SELECT aa.[id],jp.[accrual],jp.[lateaccrual]

    FROM accountactivity as aa,accounts as a,journalsperiods as jp

    IF @@Error <> 0
     goto error
     

    CREATE TABLE #Trans ([transactionID] UNIQUEIDENTIFIER)

    INSERT INTO #Trans

    SELECT DISTINCT t.[id] as transactionID

    FROM transactions as t

    IF @@Error <> 0
     goto error

    goto successful

    error:
     ROLLBACK TRAN

    successful:
    if @@trancount>0
     commit tran

     

     

     

    SQL2005:

    Code Snippet

    begin try
    begin tran


    if object_id('Tempdb..#Activities') is not null
     drop table #Activities

    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)

    INSERT INTO #Activities

    SELECT aa.[id],jp.[accrual],jp.[lateaccrual]

    FROM accountactivity as aa,accounts as a,journalsperiods as jp

    if object_id('Tempdb..#Trans') is not null
     drop table #Trans

    CREATE TABLE #Trans ([transactionID] UNIQUEIDENTIFIER)

    INSERT INTO #Trans

    SELECT DISTINCT t.[id] as transactionID

    FROM transactions as t

    commit tran
    end try
    begin catch
     declare @error nvarchar(1000)
     set @error=error_message()
     raiserror 50001 @error
     rollback tran
    end catch

     

     

    Tuesday, September 30, 2008 10:08 AM
  • Hi 中國風,

    Thank you for the quick responce, I have just tried your solution and unfortunitly it is still producing the same error,

    "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 7, current count = 8."

    This SP is a lot longer than the code snippet that I used in the above example, there are some IF statements and within them there are up to 4 insert statements. I am now wondering if they are causing the problem ?,.. Is there a certain way that transactions need to be nested within an IF statement ? or do they even need to be nested ?

    Thanks again for your help, any sugguestions are welcomed.
    Tuesday, September 30, 2008 10:24 AM
  • Hi Karl,

     

    I would recommend to use TRY/CATCH blocks in your procedure if you are using SQL2005 or above. Based on the sample code you posted, I think this is one of that scenario when the XACT_ABORT ON is a good choice and check the XACT_STATE() , but with TRY/CATCH needs more attention.

     

    Please check these in BOL for detailed information. If you can post the whole SQL script, I'll be glad to integrate these for you.

     

    I hope it helps.

     

    thanks.

    Janos

     

     

    Tuesday, September 30, 2008 10:35 AM
  • Hi Janos,

    Thank you for your responce, unfortunitly the procedure is in SQL2000 the try catch block would have been a good solution,

    The XACT_ABORT sounds promising though and It would be fantastic if you could integrate this, the SP is quite larger maybe 500 lines,.. Are you sure you dont mind ?

    Thanks again for you help
    Tuesday, September 30, 2008 11:30 AM
  • Hi Janos,

    Here is the full SP,.. I hope you can help

    ------------------------------------------------------------------------------------------------------------------------------------------


    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROCEDURE  [dbo].[spJournalCreate]
    (
    @status varchar(50),                   
    @statusJournaled varchar(50),           
    @statusJournaledAccrual varchar(50),   
    @statusJournaledLateAccrual varchar(50),
    @categoryId UNIQUEIDENTIFIER,
    @category varchar(50),
    @categoryFlag bit,                       
    @journalType int ,                       
    @userName varchar(50),
    @title varchar(50),
    @account varchar(50),
    @catSplitLines varchar(50)

    )
    AS

    declare  @name varchar(50)
    declare  @dateCreation datetime
    declare @datePeriod datetime
    declare @periodId varchar(6)
    declare @bottomBorder datetime           
    declare @topBorder datetime               
    declare @accountId UNIQUEIDENTIFIER       
    declare @statusId UNIQUEIDENTIFIER       
    declare @statusJournaledId  UNIQUEIDENTIFIER
    declare @totalAmount money
    declare @positiveSplitsAmount money
    declare @negativeSplitsAmount money
    declare @nullGuid UNIQUEIDENTIFIER
    declare @newId UNIQUEIDENTIFIER
    declare @uploaded bit
    declare @reversal bit
    declare @glcode varchar(50)
    declare @trCount int
    declare @trSplitCount int
    declare @titleFull varchar(200)
    declare @countryID varchar(3)
    SELECT @accountid = [id] from accounts where number= @account

    CREATE TABLE #transactions ([id] UNIQUEIDENTIFIER, [amount] money)
    CREATE TABLE #transactionsplits ([id] UNIQUEIDENTIFIER, [amount] money, [reversal] bit)

    SET @dateCreation = getdate()
    SET @totalAmount = 0
    SET @uploaded = 0
    SET @newId=newid()
    SET @nullGuid='00000000-0000-0000-0000-000000000000'

    SELECT @countryID=b.[country] from banks as b, accounts as a where a.[bank]=b.[id] and a.[number]=@account
    SELECT @name = [dbo].[ dbo.fn_GetNewJournalName](@journalType,@countryID)


    IF (@journalType = 0  )
        BEGIN
        SELECT @statusJournaledId=statusCodes.[id]
        FROM statusCodes
        WHERE  statusCodes.[code] = @statusJournaled
        SET @name='HFCBNK'+ @name
        SET @reversal = 0
    END

    IF (@journalType < 0)
        BEGIN
        SELECT @statusJournaledId=statusCodes.[id]
        from statusCodes
        where  statusCodes.[code] =  @statusJournaledAccrual
        set @name='HFCBNKACCRUAL'+ @name
        set @reversal = 1
    END

    IF (@journalType > 0)
        BEGIN
        SELECT @statusJournaledId=statusCodes.[id]
        FROM statusCodes
        WHERE  statusCodes.[code] =  @statusJournaledLateAccrual
        SET @name='HFCBNKLATEACCRUAL'+@name
        SET @reversal = 1
    END

    IF @countryID='FRA'
    BEGIN
        SELECT @name=@name +'; '+ b.[name]
        FROM banks as b, accounts as a
        WHERE a.[bank]=b.[id] and a.[number]=@account
    END

    SELECT @statusId=statusCodes.[id]
    FROM statusCodes
    WHERE  statusCodes.[code] = @status

    IF  @journalType =0
        BEGIN
            IF not exists(SELECT * from journalsperiods where datediff(day,[date],@dateCreation)=0 and [accrual]=1 )
                BEGIN
                -- 1 Ordinary date
                SET @datePeriod = @dateCreation
            END
            ELSE
                BEGIN
                -- 2 Journaling date = accrual date
                SET @datePeriod=dateadd(d,-1,@dateCreation)
            END

            SELECT @periodId=Periods.[id]
            FROM periods
            WHERE DateDiff(day,periods.[dateFrom],@datePeriod) >=0 and DateDiff(day,@datePeriod, periods.[dateTo])>=0
    END
    ELSE
        BEGIN
            SELECT 5
            -- 3 Get previous period for accrual lateaccrual journals,
            SELECT @periodId=Periods.[id]
            FROM periods
            WHERE DateDiff(day,periods.[dateFrom],@dateCreation) >=0 and DateDiff(day,@dateCreation, periods.[dateTo])>=0

            SELECT @datePeriod = DateAdd(d,-5,jp.[date])
            FROM journalsperiods as jp,periods as p
            WHERE datediff(day,p.[datefrom],jp.[date])>=0 and datediff(day,jp.[date],p.[dateto])>=0 and jp.[accrual]=1 and p.[id]=@periodId
    END


    SELECT @bottomBorder=[DateFrom],@topBorder =[DateTo]
    FROM periods
    WHERE [id]=@periodId


     -- **********
    BEGIN TRANSACTION
     -- **********

    CREATE TABLE #ActivitiesAccrual ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)
    INSERT INTO #ActivitiesAccrual
    SELECT aa.[id],jp.[accrualk],jp.[lateaccrual]
    FROM accountactivity as aa,accounts as a,journalsperiods as jp
    WHERE aa.[account] = a.[id] and a.[number] = @account and datediff(day,@bottomBorder,jp.[date])>=0 and datediff(day,jp.[date],@topBorder)>=0 and datediff(day,aa.[theday],jp.[date])=0
     
    IF @@Error <> 0
     goto error


    CREATE TABLE #TransactionsToAccrual ([transactionID] UNIQUEIDENTIFIER)
    INSERT INTO #TransactionsToAccrual
    SELECT DISTINCT t.[id] as transactionID
    FROM transactions as t, CategoryRulesAccrualGlCode as acat, #ActivitiesAccrual as aa
    WHERE t.[category]=acat.[categoryid] and (acat.[accflag]=1  or acat.[lateaccflag]=1 )and t.[activity]=aa.[activityID] and acat.[country]=@countryID
     
    IF @@Error <> 0
     goto error


    CREATE TABLE #SplitsToAccrual ([SplitID] UNIQUEIDENTIFIER)
    INSERT INTO #SplitsToAccrual
    SELECT DISTINCT ts.[id] as SplitID from transactionsplits as ts,transactions as t, CategoryRulesAccrualGlCode as acat, #ActivitiesAccrual as aa
    WHERE ts.[category]=acat.[categoryid] and (acat.[accflag]=1  or acat.[lateaccflag]=1 )and ts.[transactionID]=t.[id] and t.[activity]=aa.[activityID] and acat.[country]=@countryID
     
    IF @@Error <> 0
     goto error


    CREATE TABLE #Activities ([activityID] UNIQUEIDENTIFIER)
    INSERT INTO #activities
    SELECT  aa.[id] as activityID
    FROM accountactivity as aa
    WHERE aa.[account] = @accountid and datediff(day,aa.[theday],@topBorder)>=0 and (datediff(day,@bottomBorder,aa.[theday])>=0 or (aa.[id] in (SELECT [activity] from transactions as t where aa.[id]=t.[activity] and t.[status]=@statusId)))
     
    IF @@Error <> 0
     goto error


    SELECT @periodId=Periods.[id]
    FROM periods
    WHERE DateDiff(day,periods.[dateFrom],@datePeriod) >=0 and DateDiff(day,@datePeriod, periods.[dateTo])>=0


    IF @categoryId<>@nullGuid or @category<>''
    BEGIN

    IF @categoryFlag = 1       
       BEGIN
         IF @journalType = 0   
           BEGIN
                INSERT  #transactions  
                    SELECT DISTINCT transactions.[id],transactions.[amount]
                    FROM    transactions,#Activities as aa,periods as p
                    WHERE    (transactions.[glCode] is  not null) and (transactions.[glCode]<>@nullGuid)and transactions.[status] = @statusId and transactions.[category] = @categoryId and
                            transactions.[activity]= aa.[activityID] and p.[id]=@periodId and (not( transactions.[id] in (SELECT [transactionid] FROM #TransactionsToAccrual ))
                            or
                            (transactions.[id] in (SELECT jt.[transactionid] from journals as j,  journalstransactions as  jt WHERE j.[id]=jt.[journalid] and datediff(day,p.[dateFrom],j.[dateCreation])>=0 and datediff(day,j.[dateCreation],p.[dateTo])>=0)))
                            and ( (not transactions.[category] in(SELECT bc.[categoryId] FROM batchcategories as bc)) or (transactions.[id] in (SELECT  bn.[transactionId] FROM batchnumbers as bn  WHERE  bn.[transactionid]=transactions.[id] and bn.[deleted]=0)))
                           
                            IF @@Error <> 0
                            goto error

                INSERT  #transactionsplits
                    SELECT DISTINCT transactionsplits.[id],transactionsplits.[amount],1
                    FROM    transactionsplits,transactions,#Activities as aa,periods as p
                    WHERE    (transactionsplits.[glCode] is  not null) and (transactionsplits.[glCode]<>@nullGuid)and transactionsplits.[status] = @statusId and transactionsplits.[category] = @categoryId and transactionsplits.[transactionId]=transactions.[id] and
                            transactions.[status] = @statusId    and transactions.[activity]= aa.[activityid] and p.[id]=@periodId and (not( transactionsplits.[id] in (SELECT [transactionid] from #SplitsToAccrual ))
                            or
                            (transactionsplits.[id] in (SELECT jt.[splitlineId] FROM journals as j,journalstransactions as  jt
                            where j.[id]=jt.[journalid] and datediff(day,p.[dateFrom],j.[dateCreation])>=0 and datediff(day,j.[dateCreation],p.[dateTo])>=0 )))    and ( (not transactionsplits.[category] in(SELECT bc.[categoryId] from batchcategories as bc))
                            or (transactionsplits.[id] in (SELECT  bn.[splitlineId] FROM batchnumbers as bn WHERE bn.[splitlineId]=transactionsplits.[id] and bn.[deleted]=0)) )
                           
                            IF @@Error <> 0
                            goto error
        END


        IF (@journalType < 0  )
            BEGIN
                INSERT  #transactions 
                SELECT distinct transactions.[id],transactions.[amount]
                from transactions, #TransactionsToAccrual    as ta,#ActivitiesAccrual as aa  
                where      transactions.category = @categoryId  and status = @statusId and  transactions.[id] =ta.[transactionID] and  transactions.[activity]= aa.[activityID]  and aa.[acc]=1
                 
                IF @@Error <> 0
                goto error

                INSERT  #transactionsplits     
                SELECT distinct transactionsplits.[id],transactionsplits.[amount],1
                from transactionsplits,transactions,#SplitsToAccrual    as ta,#ActivitiesAccrual as aa  
                where transactionsplits.category =@categoryId and transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId and  transactionsplits.[status] = @statusId and transactions.[activity]= aa.[activityID] and  transactionsplits.[id] =ta.[splitID] and aa.[acc]=1
                 
                IF @@Error <> 0
                goto error

        END



        IF (@journalType > 0  )
        BEGIN
            INSERT  #transactions 
                SELECT distinct transactions.[id],transactions.[amount]
                FROM transactions,#TransactionsToAccrual    as ta,#ActivitiesAccrual as aa  
                WHERE     transactions.status = @statusId and  transactions.category =@categoryId and status = @statusId and  transactions.[id] =ta.[transactionID] and  transactions.[activity]= aa.[activityID]  and aa.[lacc]=1
               
                IF @@Error <> 0
                goto error

           
            INSERT  #transactionsplits     
            SELECT distinct transactionsplits.[id],transactionsplits.[amount],1
            FROM transactionsplits,transactions,#SplitsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE transactionsplits.category = @categoryId and transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId and  transactionsplits.[status] = @statusId and transactions.[activity]= aa.[activityID] and  transactionsplits.[id] =ta.[splitID] and aa.[lacc]=1
           
            IF @@Error <> 0
            goto error
        END
    END
      
    ELSE
    BEGIN

    IF (@journalType = 0  )
    BEGIN
        INSERT  #transactions 
            SELECT distinct transactions.[id],transactions.[amount]
            FROM transactions,categoryRules,#Activities as aa,periods as p
            WHERE    (transactions.[glCode] is  not null) and (transactions.[glCode]<>@nullGuid)and
                    transactions.status = @statusId and  transactions.category = categoryRules.[id] and
                    categoryRules.category = @category and transactions.[activity]= aa.[activityID] and
                    p.[id]=@periodId and (not( transactions.[id] in (SELECT [transactionid] from #TransactionsToAccrual ))
            or
                    (transactions.[id] in (SELECT jt.[transactionid] from journals as j, journalstransactions as  jt
                    where j.[id]=jt.[journalid] and datediff(day,p.[dateFrom],j.[dateCreation])>=0 and datediff(day,j.[dateCreation],p.[dateTo])>=0 )))
                    and ( (not transactions.[category] in(SELECT bc.[categoryId] from batchcategories as bc))
                    or (transactions.[id] in (SELECT  bn.[transactionId] from batchnumbers as bn  where  bn.[transactionid]=transactions.[id] and bn.[deleted]=0)) )
                   
                    IF @@Error <> 0
                    goto error



    INSERT  #transactionsplits     
            SELECT distinct transactionsplits.[id],transactionsplits.[amount],1
            FROM    transactionsplits,categoryRules,#Activities as aa ,transactions,periods as p
            WHERE  (transactionsplits.[glCode] is  not null) and (transactionsplits.[glCode]<>@nullGuid)and transactionsplits.[status] = @statusId and transactionsplits.category = categoryRules.[id] and categoryRules.category = @category and
                    transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId and transactions.[activity]= aa.[activityID]  and p.[id]=@periodId and
                    (not( transactionsplits.[id] in (SELECT [transactionid] from #SplitsToAccrual ))
                    or(transactionsplits.[id] in (SELECT jt.[splitlineId] from journals as j, journalstransactions as  jt
                    where j.[id]=jt.[journalid] and datediff(day,p.[dateFrom],j.[dateCreation])>=0 and datediff(day,j.[dateCreation],p.[dateTo])>=0 )))
                    and ( (not transactionsplits.[category] in(SELECT bc.[categoryId] from batchcategories as bc))
                    or (transactionsplits.[id] in (SELECT  bn.[splitlineId] from batchnumbers as bn  where  bn.[splitlineId]=transactionsplits.[id] and bn.[deleted]=0)) )
                   
                    IF @@Error <> 0
                    goto error
    END



    IF (@journalType < 0  )
    BEGIN
        INSERT  #transactions 
            SELECT distinct transactions.[id],transactions.[amount]
            FROM    transactions,categoryRules, #TransactionsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE    transactions.category = categoryRules.[id] and categoryRules.category = @category  and
                    status = @statusId and  transactions.[id] =ta.[transactionID] and  transactions.[activity]= aa.[activityID]  and aa.[acc]=1
                   
                    IF @@Error <> 0
                    goto error



        INSERT  #transactionsplits     
            SELECT distinct transactionsplits.[id],transactionsplits.[amount],1
            FROM transactionsplits,categoryRules,transactions,#SplitsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE transactionsplits.category = categoryRules.[id] and categoryRules.category = @category and transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId
            and  transactionsplits.[status] = @statusId and transactions.[activity]= aa.[activityID] and  transactionsplits.[id] =ta.[splitID] and aa.[acc]=1
           
            IF @@Error <> 0
            goto error
    END



    IF (@journalType > 0  )
    BEGIN
    INSERT  #transactions 
            SELECT distinct transactions.[id],transactions.[amount]
            FROM transactions,categoryRules,#TransactionsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE     transactions.status = @statusId and  transactions.category = categoryRules.[id] and  categoryRules.category = @category  and
                    status = @statusId and  transactions.[id] =ta.[transactionID] and  transactions.[activity]= aa.[activityID]  and aa.[lacc]=1
                   
                    IF @@Error <> 0
                    goto error

       
    INSERT  #transactionsplits     
            SELECT distinct transactionsplits.[id],transactionsplits.[amount],1
            FROM transactionsplits,categoryRules,transactions,#SplitsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE    transactionsplits.category = categoryRules.[id] and categoryRules.category = @category and
                    transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId
                    and  transactionsplits.[status] = @statusId and transactions.[activity]= aa.[activityID] 
                    and  transactionsplits.[id] =ta.[splitID] and aa.[lacc]=1
                    -- **********
                    IF @@Error <> 0
                    goto error

    END
    END
    END

    ELSE 
    BEGIN

    IF (@journalType = 0  )
    BEGIN                                                             

     INSERT  #transactions 
           SELECT  transactions.[id],transactions.[amount]
        FROM transactions,categoryrules as c,#Activities as aa,periods as p

        WHERE    (transactions.[glCode] is  not null) and (transactions.[glCode]<>@nullGuid)and
                status = @statusId and transactions.[activity]= aa.[activityID] and
                p.[id]=@periodId  and c.[id]= transactions.[category]  and (( transactions.[id] not in (SELECT [transactionid] FROM #TransactionsToAccrual ))
                or
                (transactions.[id] in (SELECT jt.[transactionid] from journals as j, journalstransactions as  jt
                where j.[id]=jt.[journalid] and    datediff(day,p.[dateFrom],j.[dateCreation])>=0 and
                datediff(day,j.[dateCreation],p.[dateTo])>=0  ))) and ((not transactions.[category] in(SELECT bc.[categoryId] from batchcategories as bc))
                or (transactions.[id] in (SELECT  bn.[transactionId] FROM batchnumbers as bn  where bn.[transactionid]=transactions.[id] and bn.[deleted]=0)) )
               
                IF @@Error <> 0
                goto error

     INSERT  #transactionsplits     

        SELECT transactionsplits.[id],transactionsplits.[amount],1
        FROM transactionsplits,transactions,#Activities as aa,periods as p

        WHERE    (transactionsplits.[glCode] is  not null) and (transactionsplits.[glCode]<>@nullGuid)and
                transactionsplits.[status] = @statusId and transactionsplits.[transactionId]=transactions.[id] and
                transactions.[status] = @statusId and transactions.[activity]= aa.[activityID]  and
                p.[id]=@periodId and (not( transactionsplits.[id] in (SELECT [splitid] from #SplitsToAccrual  ))
                or (transactionsplits.[id] in (SELECT jt.[splitlineId] from journals as j, journalstransactions as  jt
                where j.[id]=jt.[journalid] and    datediff(day,p.[dateFrom],j.[dateCreation])>=0 and
                datediff(day,j.[dateCreation],p.[dateTo])>=0 )))and ( (not transactionsplits.[category] in(SELECT bc.[categoryId] from batchcategories as bc))
                or (transactionsplits.[id] in (SELECT  bn.[splitlineId] from batchnumbers as bn  where  bn.[splitlineId]=transactionsplits.[id] and bn.[deleted]=0)) )
               
                IF @@Error <> 0
                goto error
    END

    IF (@journalType < 0)
    BEGIN
        INSERT  #transactions 
               SELECT  transactions.[id],transactions.[amount]
            FROM transactions, #TransactionsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE status = @statusId and  transactions.[id] =ta.[transactionID] and  transactions.[activity]= aa.[activityID]  and aa.[acc]=1
           
            IF @@Error <> 0
            goto error

            INSERT  #transactionsplits     
            SELECT  transactionsplits.[id],transactionsplits.[amount],1
            FROM transactionsplits,transactions,#SplitsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId and  transactionsplits.[status] = @statusId and transactions.[activity]= aa.[activityID]  and  transactionsplits.[id] =ta.[splitID] and aa.[acc]=1
           
            IF @@Error <> 0
            goto error
    END

    IF (@journalType > 0) 
    BEGIN
     
        INSERT  #transactions  
               SELECT  transactions.[id],transactions.[amount]
            FROM  transactions, #TransactionsToAccrual    as ta,#ActivitiesAccrual as aa    
             WHERE     status = @statusId and  transactions.[id] =ta.[transactionID] and  transactions.[activity]= aa.[activityID]  and aa.[lacc]=1
           
            IF @@Error <> 0
            goto error

        INSERT  #transactionsplits     
            SELECT  transactionsplits.[id],transactionsplits.[amount],1
            FROM transactionsplits,transactions,#SplitsToAccrual    as ta,#ActivitiesAccrual as aa  
            WHERE transactionsplits.[transactionId]=transactions.[id] and transactions.[status] = @statusId and  transactionsplits.[status] = @statusId and transactions.[activity]= aa.[activityID]  and  transactionsplits.[id] =ta.[splitID] and aa.[lacc]=1
           
            IF @@Error <> 0
            goto error
    END
    END


    --------- TOTAL AMOUNT -----------
    IF  exists(SELECT * from #transactionsplits)
        BEGIN
        CREATE TABLE #trPositiveAmount (trID UNIQUEIDENTIFIER,amount money)
        CREATE TABLE #trNegativeAmount (trID UNIQUEIDENTIFIER,amount money)

        INSERT #trPositiveAmount
            SELECT sl.[transactionId] , sum(sl.[amount])
            FROM transactionsplits as sl,#transactionsplits as ts
            WHERE sl.[id]=ts.[ID]and  sl.[amount]>0
            GROUP BY  sl.[transactionId]
           
            IF @@Error <> 0
            goto error


        INSERT #trNegativeAmount
            SELECT sl.[transactionId] , sum(sl.[amount])
            FROM transactionsplits as sl,#transactionsplits as ts
            WHERE sl.[id]=ts.[ID]and sl.[amount]<0
            GROUP BY  sl.[transactionId]
           
            IF @@Error <> 0
            goto error



    IF  exists(SELECT * from #trNegativeAmount as n,#trPositiveAmount as p where p.[trId]=n.[trId])
    BEGIN
      SELECT @positiveSplitsAmount = isnull(sum(abs(p.[amount])),0)
        FROM #trPositiveAmount as p ,#trNegativeAmount as n
        WHERE p.[trId]=n.[trId] and abs(isnull(p.[amount],0))>abs(n.[amount])

      SELECT @positiveSplitsAmount =  isnull(@positiveSplitsAmount,0)+  isnull(sum(abs(n.[amount])),0)
        FROM #trPositiveAmount as p,#trNegativeAmount as n
        where p.[trId]=n.[trId] and abs(n.[amount])>abs(p.[amount])
    END


    IF  exists(SELECT * FROM #trNegativeAmount)
      SELECT @positiveSplitsAmount =  isnull(@positiveSplitsAmount,0)+   isnull(sum(abs(n.[amount])),0)
        FROM #trNegativeAmount as n
        WHERE n.[trId] not in (SELECT [trId] from #trPositiveAmount)


    IF  exists(SELECT * from #trPositiveAmount)
      SELECT @positiveSplitsAmount =  isnull(@positiveSplitsAmount,0)+   isnull(sum(abs(p.[amount])),0)
        FROM #trPositiveAmount as p
        WHERE    p.[trId] not in (SELECT [trId] from #trNegativeAmount)


    DROP TABLE #trPositiveAmount
    DROP TABLE #trNegativeAmount

    END
    SELECT @totalAmount = sum(abs(amount))
    FROM #transactions
    SET @totalAmount = isnull(@totalAmount,0)+isnull(@positiveSplitsAmount,0)

    -- TITLE
    SELECT @glcode= SUBSTRING(glcodes.glcode, 1, 10) FROM accounts, accountglcode, glcodes
    WHERE accounts.[number] = @account and accounts.[id] =accountglcode.[accountId] and accountglcode.[glcodeId] = glcodes.[id]
    SET @titleFull =@glcode+' '+ @title +' '+ @periodId
    SELECT @trCount=count(*) FROM #transactions
    SELECT @trSplitCount=count(*) FROM #transactionsplits



    IF @totalamount is  null
    BEGIN
    PRINT 'Total amount is null, Please contact IT to resolve this Issue.'
    END
    ELSE
    BEGIN
    IF @periodId is null
     BEGIN
        PRINT 'Period  is null, Please contact your Team Leader to add it to the system using the Admin module.'
     END
    ELSE
    BEGIN
    IF @titleFull is null
     BEGIN
        PRINT 'Title is null. The glcode is missing for the account: ' +@account+ ' . Please contact your Team Leader to add it to the system. He can do it in Account-GlCode screen of Admin module.'
     END
    ELSE
     BEGIN
    IF (@trCount <=0) and (@trSplitCount<=0)
    BEGIN
         PRINT 'There are neither transactions nor splitlines to create journal.'
    END
    ELSE
    BEGIN

    -- ADD a new journal to Journals Datatable
    INSERT INTO Journals
                    ([id], [name], [dateCreation], [PeriodId],[reversal], [totalAmount], [userName],[uploaded],[title])
    VALUES            (@newId,@name,@dateCreation,@PeriodId,@reversal,@totalAmount,@userName,@uploaded,@titleFull)
           
            IF @@Error <> 0
            goto error


    -- ADD all trandactions wich were matched to journal to JournalsTransactions datatable
    INSERT INTO JournalsTransactions ( [journalId],[transactionId])
    SELECT @newId,#transactions.[id]
    FROM #transactions

    IF @@Error <> 0
    goto error

    INSERT INTO JournalsTransactions  ([journalId],[splitlineId], [reversal])
    SELECT @newId,#transactionsplits.[id],#transactionsplits.[reversal]
    FROM #transactionsplits

    IF @@Error <> 0
    goto error


    -- Update status to all #transactions.[id] in transactions to JOURNAL
    UPDATE transactions
    SET transactions.[status] = @statusJournaledId,  transactions.[cleanDescription]=@name +'; '+ isnull(cleanDescription,'')
    FROM transactions
    WHERE transactions.[id] in (SELECT #transactions.[id] FROM #transactions )

    IF @@Error <> 0
    goto error


    -- Update status to all #transactionsplits.[id] in transactionsplits to JOURNAL
    UPDATE transactionsplits
    SET transactionsplits.[status] = @statusJournaledId
    FROM transactionsplits
    WHERE transactionsplits.[id]in (SELECT #transactionsplits.[id] from #transactionsplits )

    IF @@Error <> 0
    goto error


    -- Update status in transactions to JOURNAL for transax with category = split line!!!
    UPDATE transactions
    SET        transactions.[status] = @statusJournaledId,  transactions.[cleanDescription]=@name +'; '+  isnull(cleanDescription,'')
    FROM    transactions as t,transactionsplits as sl,categoryrules as c
    WHERE    t.[category]=c.[id] and c.[category]=@catSplitLines and t.[status]=@statusId and t.[id]= sl.[transactionId] and sl.[status] = @statusJournaledId
            and (( not exists(SELECT * from transactionsplits as ts where t.[id]=ts.[transactionId]and (( ts.[status]!=@statusJournaledId and ts.[glcode] is not null) or (ts.[glcode] is null and ts.[category] is null)) ) and @journalType = 0)or(@journalType != 0))

    IF @@Error <> 0
    goto error


    IF (@journalType = 0  )
    BEGIN
    --INSERT INTO HISTORY added  journal
    INSERT journalshistory ([parentId])
    VALUES (@newId)

    IF @@Error <> 0
    goto error

    END
    PRINT ''  -- It shows that Journal was successfully created
    END
    END
    END
    END


    goto successful

    error:
     ROLLBACK TRAN

    successful:
    if @@trancount>0
     commit tran

    DROP TABLE #transactions
    DROP TABLE #transactionsplits
    DROP TABLE #TransactionsToAccrual
    DROP TABLE #SplitsToAccrual
    DROP TABLE #ActivitiesAccrual
    DROP TABLE #Activities
    Tuesday, September 30, 2008 11:38 AM
  • Hi Karl,

     

    I think the solution is easy if you accept this limitation: if any error occures all the open transactions will be rolled back, there will not be any open transaction left. If you are ok with this, simply put XACT_ABORT ON just before you start a transaction.

     

    I hope it helps.

     

    thanks,

    Janos

    Tuesday, September 30, 2008 12:37 PM
  • Hi Janos,

    Should I put the
    XACT_ABORT ON before each roll back or just before the "BEGIN TRANSACTION"

    E.g

    SET XACT_ABORT ON
    BEGIN TRANSACTION

    CREATE TABLE #ActivitiesAccrual ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)
    INSERT INTO #ActivitiesAccrual
    SELECT aa.[id],jp.[accrualk],jp.[lateaccrual]
    FROM accountactivity as aa,accounts as a,journalsperiods as jp
    WHERE aa.[account] = a.[id] and a.[number] = @account and datediff(day,@bottomBorder,jp.[date])>=0 and datediff(day,jp.[date],@topBorder)>=0 and datediff(day,aa.[theday],jp.[date])=0

    IF @@Error <> 0
    GOTO error

    -- OR --


    BEGIN TRANSACTION

    CREATE TABLE #ActivitiesAccrual ([activityID] UNIQUEIDENTIFIER,acc bit,lacc bit)
    INSERT INTO #ActivitiesAccrual
    SELECT aa.[id],jp.[accrualk],jp.[lateaccrual]
    FROM accountactivity as aa,accounts as a,journalsperiods as jp
    WHERE aa.[account] = a.[id] and a.[number] = @account and datediff(day,@bottomBorder,jp.[date])>=0 and datediff(day,jp.[date],@topBorder)>=0 and datediff(day,aa.[theday],jp.[date])=0

    SET XACT_ABORT ON
    IF @@Error <> 0
    GOTO error



    Thanks Again ..!
    Tuesday, September 30, 2008 1:09 PM
  • Hi,

     

    Put it only before the BEGIN TRANSACTION. This is a session option, so it will be applied for the whole procedure.

     

    So, simply put it in the procedure as below:

    SET XACT_ABORT ON
    BEGIN TRANSACTION

     

    I hope it helps.

    Thanks,

    Janos

     

    Tuesday, September 30, 2008 1:24 PM
  • Hi Janos,

    Unfortunitly it still gives me the error,.. i noticed that the count increases every time i try to execute it (below the count is now 13) does this mean anything ?

    "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 12, current count = 13."
    Tuesday, September 30, 2008 1:31 PM
  • Ok, seems the problem is more sophisticated Smile. Do you have nested transactions somehow? Do you use any transaction in the dbo.fn_GetNewJournalName function? it should cause the problem.

     

    in other hand, a simple RETURN after the ROLLBACK TRAN should solve your problem.

     

    Try to update your code with this:

    error:

    ROLLBACK TRAN

    RETURN

     

    Anyway, you should never use GOTO in SQL Wink

     

    I hope it helps.

     

    Thanks,

    Janos

    Tuesday, September 30, 2008 6:15 PM
  • Hi Janos,


    There are some if statements where I have some rollbacks but there is only one 'Begin Transaction' so I wouldnt say they are nested,.. There are no transactions in the dbo.fn_GetNewJournalName function either, all the function is doing is selecting some data so I began the transaction after it was called.


    I tried using the below code, ... but still the same error, have you any other suggestions, thank you for your help


    error:

    ROLLBACK TRAN

    RETURN




    --------------------------------------------------------------


    SQLUSA,


    Thank you for your tip, unfortunitly I have very little time between project, but it is certanily something that I will do in the future, I plan to undrage the db to SQL2005 in the new year after that I will put it next on the list.

    Wednesday, October 01, 2008 8:37 AM
  • I have just tired (again ) to create a smaller SP to test the rollback, This time I used a TempTable and it failed too,... Do Transactions not work with temptables ?..

    Here is my code,.. It works fine if i dont force it to error, however if i rename the 'ID' column to 'ID2' it gives me the same error as in my original SP, "Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing"

    The SP is not using any database tables so you can try execute it yourself to see...

    Im really baffelled now.. ??

    Thanks again guys for all the help,.. any suggestions are always appreciated.. !


    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROC [dbo].[spINSERT]

    AS

    BEGIN TRANSACTION

    CREATE TABLE #TempInsert ([ID] UNIQUEIDENTIFIER, [Description] UNIQUEIDENTIFIER)
    INSERT INTO #TempInsert (ID2, Description)
    VALUES    (newId(), newId())

    SELECT * FROM #TempInsert


    IF @@Error <> 0
    BEGIN
    ROLLBACK TRAN
    RETURN
    END

    COMMIT
    DROP TABLE #TempInsert

    Wednesday, October 01, 2008 10:23 AM
  • Karl,

     

    Ensure that the number of transactions before you start is 0. Otherwise it will never work.

     

    Nonetheless, as you are using temporary object and transactions I would be tempted to write a parent procedure to control the tranaction. That way if you get an error as a result of a missing object you can rollback the tranactions.

     

    In the original transaction you simply have to return the error value. Any return value other than 0 should then result in a rollback in the parent.

     

    Example:

     

    CREATE PROCEDURE dbo.ParentProc

    AS

    --Clean up orphaned transactions

     

    IF @@TRANCOUNT > 0 AND @@NEST_LEVEL = 1

    ROLLBACK TRANSACTION

     

    declare @RetVal int

    SET @RetVal = 0

    BEGIN TRANSACTION

     

    EXEC @RetVal = dbo.MyProc

     

    IF @RetVal =0

    COMMIT TRANSACTIONS

    ELSE

    ROLLBACK TRANSACTION

    GO

     

     

     

    Wednesday, October 01, 2008 11:21 AM
  • Thanks Rick,

    To understand you correctly, If any part of my procedure errors then it will return it to the parent procedure and therefore rollback the transactions, will it rollback the entire (or what has been exectuted) in the main SP ?
    Wednesday, October 01, 2008 11:29 AM
  • Yes unless you implement savepoints. You must ensure that you return an error number and not 0 from the child package.

     

    The parent package is important as any reference in the child package to an object that does not exist with automatically stop the procedure without dropping into any error handler. Whereas, the parent package will catch this although you might have to set the @RetVal to a non 0 value to be sure of the clean up.

     

    Rick

     

    Wednesday, October 01, 2008 11:33 AM
  • Thanks for your help rick, .. but still no joy ? I tried it with my example SP (below) and Im still getting the error message,.. can you see any issues with it ?

    -------------------------------
    ---------------------------------------------------------------------------------------------
    Alter PROCEDURE dbo.ParentProc

    AS
    -- Clean up orphaned transactions

    declare @@NEST_LEVEL as int
    declare @RetVal int
    set @RetVal = 0
    set @@NEST_LEVEL = 0


    IF @@TRANCOUNT > 0 AND @@NEST_LEVEL = 1

    ROLLBACK TRANSACTION

    BEGIN TRANSACTION
     
    Exec @RetVal = dbo.spINSERT

    IF @RetVal =0
        COMMIT TRANSACTION
    ELSE
        ROLLBACK TRANSACTION
    GO
    ----------------------------------------------------------------------------------------------------------------------------


    ----------------------------------------------------------------------------------------------------------------------------
    set ANSI_NULLS ON
    set QUOTED_IDENTIFIER ON
    go

    ALTER PROC [dbo].[spINSERT]

    AS

    BEGIN TRANSACTION

    CREATE TABLE #TempInsert ([ID] UNIQUEIDENTIFIER, [Description] Varchar(20))
    INSERT INTO #TempInsert (IDy, Description)
    VALUES    (newId(), 'TEST')
    IF @@Error <> 0
    BEGIN
    ROLLBACK TRAN
    RETURN
    END


    SELECT * FROM #TempInsert


    COMMIT
    DROP TABLE #TempInsert
    ----------------------------------------------------------------------------------------------------------------------------
    Wednesday, October 01, 2008 11:43 AM
  • Karl,

     

    Take all the transaction code out of the child

     

     

    ALTER PROC [dbo].[spINSERT]

    AS
    declare @err int

    SET @err = 0


    CREATE TABLE #TempInsert ([ID] UNIQUEIDENTIFIER, [Description] Varchar(20))
    INSERT INTO #TempInsert (IDy, Description)
    VALUES    (newId(), 'TEST')

     

    set @err = @@ERROR
    IF @err <> 0

    RETURN @err

    SELECT * FROM #TempInsert

    DROP TABLE #TempInsert

    return @ERR

    Wednesday, October 01, 2008 11:50 AM