cancel
Showing results for 
Search instead for 
Did you mean: 

Stored Procedure causing log file to blow up

Former Member
0 Kudos

Hey everyone!  I am having a n issue with SAP BODS 4.1 and SQL Server 2012.  We occasionally need to do a complete reload of some of our source systems into our EDW.  We can't truncate the table because there are multiple systems that are loaded.  We wrote a stored procedure to delete the EDW based on the source system we are reloading.  This allows us to manage the performance of the delete in SQL Server and give us a bit more control over the transactions.  The stored procedure deletes in chunks so we can keep the log file clean.  Here is the main part of the stored procedure:


WHILE(@RowCount > 0)

  BEGIN

    SET @SQLString = N'DELETE TOP ('+ CAST(@DeletesPerCommit AS VARCHAR(10)) +')

                     FROM ' + @EdwTable + ' WHERE SOURCENUMID = ' + CAST(@SourceNum AS VARCHAR(10))

    BEGIN TRY

      BEGIN TRANSACTION

        EXEC(@SQLString)

        SET @RowCount = @@ROWCOUNT

      COMMIT TRANSACTION

    END TRY

    BEGIN CATCH

      SET @ErrorMessage = ERROR_MESSAGE()

      IF (XACT_STATE()) = -1 ROLLBACK TRANSACTION

      IF (XACT_STATE()) = 1 COMMIT TRANSACTION

      RAISERROR('ERROR: %s',18,-1,@ErrorMessage)

      RETURN -1

    END CATCH

    SET @RecordsDeleted = @RecordsDeleted + @RowCount

    SET @Counter = @RecordsDeleted

    IF(@Counter >= 400000)

      BEGIN

        CHECKPOINT 10

        SET @Counter = 0

      END

  END  

RETURN @RecordsDeleted

When we execute this from SQL Server, everything looks perfect and we are able to see the table count decrease as expected.  However, when we call this from BODS, the table count does not decrease and on large table, we eventually fill up the log file.  I realize we are executing this script from a single threaded environment, but should that matter?  Here is our BODS script that calls the stored procedure:


$V_SP_RETURN  = DS_RD_APMart.DBO.SPDELETERECORDSBYSOURCE(

    upper(substr(workflow_name(),11,(length(workflow_name()) - 17))),

    $G_RDSOURCENUMID ,

    10000,

    $V_AL_SP_RETCODE ,

    $V_AL_SP_ERRMSG );

# The stored procedure will return a -1 if the table passed does

# not exist OR -2 if any of the input parameters are incorrect,

# otherwise it will return the total number of records deleted

# If the stored procedure fails, the SQL Server error

# message will be stored in the $V_AL_SP_ERRMSG variable.

IF($V_AL_SP_RETCODE = 'ACTA_SP_OK' AND $V_SP_RETURN >= 0 )

    #  Successful execution of stored procedure

    #  Print number of deleted records

    print( 'Deleted ' || cast( $V_SP_RETURN, 'varchar(50)') || ' records for '|| upper(substr(workflow_name(),11,(length(workflow_name()) - 17))));

ELSE

  BEGIN

    print( '[workflow_name()] - DS_RD_DWCR.DBO.SPDELETERECORDSBYSOURCE return value: [$V_AL_SP_RETCODE] - '|| cast( $V_SP_RETURN, 'varchar(50)'));

    print( '[workflow_name()] - DS_RD_DWCR.DBO.SPDELETERECORDSBYSOURCE error message: [$V_AL_SP_ERRMSG]');

    raise_exception($V_AL_SP_ERRMSG);

  END

Anyone have any ideas why this blows up the database when called from BODS?

Thanks,

Scott

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Found a solution.  This was very odd, because what we noticed was that when we kicked off the stored procedure as indicated in my first post, BODS would wrap the call in a transaction.  This is what was causing the issue.  But, depending on the repository, sometimes it wouldn't.  Very buggy.

We added the following at the beginning of the stored procedure:


DECLARE @TranCount int;

SET @TranCount = @@TRANCOUNT

IF(@TranCount > 0) ROLLBACK

This basically killed the transaction that BODS started and then the stored procedure would run as expected.  We also had to add the following to the end of our stored procedure:


IF(@TranCount > 0) BEGIN TRAN

This was to ensure that if a transaction was started, this would prevent the error: "Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 1, current count = 0."

Hopefully this helps someone else out there!

former_member182007
Active Contributor
0 Kudos

Hello Scott, It is great to hear that your issue is resolved. Could you please help here to know that how stored procedure can be called into BODS.

Thanks in advance

Best !

Deep

Former Member
0 Kudos

Deepak,

Page 84 of the designer guide, section - 5.2.6.2 Imported stored function and procedure information, goes through importing stored procedures/function based on your database type.

Here is a link to the guide.

Answers (1)

Answers (1)

Former Member
0 Kudos

So it appears that BODS wraps the stored procedure call in a transaction.  In doing so, the explicit transactions in the stored procedure are being ignored.  So on large tables with many indexes, the log file grows until it is full and causes the failure.

I have read the SAP Note: 0002017964 - Multiple database transactions in a single statement support - SAP Data Services


Workaround #3 was the only viable option, but it did not work.  Anyone know how to turn off transaction control when calling a stored procedure?

Thanks,

Scott