Control-M for Databases job fails when submitting a stored procedure or an open query to MSSQL Server while the autocommit checkbox is set to true, the job run correctly on database but generates an exception.

Version 1
    Share This:

    This document contains official content from the BMC Software Knowledge Base. It is automatically updated when the knowledge article is modified.


    PRODUCT:

    Control-M for Databases


    COMPONENT:

    Control-M for Databases


    APPLIES TO:

    Control-M for Databases all versions



    PROBLEM:

     

    The Control-M for Databases may produce an error about not being able to run a stored procedure from within a transaction, even though a transaction is not called.  As an example, let's take the following Control-M for Databases job:

      

    Execution type: Open Query (also tested as Stored Procedure)
    Query Text
    EXECUTE [dbaDB].[dbo].[DatabaseBackup] @Databases = [caja], @Directory = 'G:\SQLBackup', @BackupType = 'FULL', @Verify = 'Y', @CleanupTime = 36

      

    This is calling a stored procedure that runs the BACKUP DATABASE command, which can't be executed inside a transaction.  This results in the  following error in the job sysout.

      

    Job failure message:
    Cannot perform a backup or restore operation within a transaction.

      

    The same command works when it is run from an adhoc query tool, as it did not start a transaction.

     


    SOLUTION:

     

    The Control-M for Databases works exclusively in manual commit mode, where we commit all the lines of a stored procedure at once, so this is assumed as a transaction. 
    A database backup/restore requires auto commit mode.

      
      To circumvent the problem, add the following line at the beginning of your open query script and rerun the execution:  
    SET IMPLICIT_TRANSACTIONS OFF; 
      
        
      
      This forces the session to switch to auto commit mode.  Please note that switching to auto commit mode may affect the success of killing job actions, as changes are applied to the database after each statement. 

     


    Article Number:

    000064245


    Article Type:

    Solutions to a Product Problem



      Looking for additional information?    Search BMC Support  or  Browse Knowledge Articles