ApexSQL Knowledgebase: Using ApexSQL Enforce's Return Codes

LINKS

General

Apex SQL Audit
Apex SQL Clean
Apex SQL Code
Apex SQL Data Diff
Apex SQL Diff
Apex SQL Doc
Apex SQL Edit
Apex SQL Enforce
Apex SQL Log
Apex SQL Recover
Apex SQL Report
Apex SQL Script

Knowledgebase Home

SITE FEED

Site Feed

Support Forum

The fastest and most effective to get answers to your questions quickly. Click Here for more info.

FAQs

Quick answers to common questions. Click Here for more info.

Troubleshooting

Known issues and basic troubleshooting techniques for problems or unexpected behaviors. Click Here for more info.

Knowledgebase

Technical tips, How-to articles, and other tutorials about ApexSQL Tools. Click Here for more info.

Online Documentation

All of our help files for download or convenient viewing online. Click Here for more info.

Videos

Watch product demos, training videos, or tutorials of our products' main features. We frequently upload new videos so check often for updates. Click Here for more info.

ApexSQL Knowledgebase

Tips and How-to Articles for Apex SQL Tools

Using ApexSQL Enforce's Return Codes

What are the Enforce return codes?

Using ApexSQL Enforce in unattended operation mode

ApexSQL Enforce can be used in unattended operation mode through its Command Line Interface (CLI). This mode allows you to run the application from a batch file and optionally retrieve its return codes and programmatically execute your custom logic for each return code. Below is a sample “blue print” batch file that lists all Enforce return codes, displays a message with descriptive return code and provides place-holders for you to add your code into.

To use it, copy the text below into a new batch (.bat) file (or download the file from Here), replace {Your Username} placeholder in rulebase path with your own, change your SQL server/instance name if needed and add your own logic inside the cases that you want to handle.

REM Execute an ApexSQLEnforce Rulebase on several databases
REM and take different actions depending on its return code

"C:\Program Files\ApexSQL\ApexSQLEnforce\ApexSQLEnforce.com" /rb:"C:\Documents and Settings\{Your Username}\My Documents\ApexSQL\ApexSQLEnforce\v2008.02\Best practices CS.axrb" /s:(local)\SQL2000 /d:Northwind pubs /v

IF %ERRORLEVEL%==-1 GOTO ALL_DBS_PASSED
IF %ERRORLEVEL%==101 GOTO ALL_DBS_FAILED
IF %ERRORLEVEL%==102 GOTO SOME_DBS_FAILED
IF %ERRORLEVEL%==0 GOTO SUCCESS
IF %ERRORLEVEL%==1 GOTO GENERAL_ERROR
IF %ERRORLEVEL%==2 GOTO ILLEGAL_ARGS_COMBO
IF %ERRORLEVEL%==3 GOTO INVALID_VALUE
IF %ERRORLEVEL%==4 GOTO IO_ERROR
IF %ERRORLEVEL%==5 GOTO INSUF_PERMISSIONS

GOTO END

:ALL_DBS_PASSED
REM All processed databases have passed.
REM Add your handling code here...
MSG * ApexSQL Enforce: ALL_DBS_PASSED
ECHO "ALL_DBS_PASSED"
GOTO END

:ALL_DBS_FAILED
REM All processed databases have failed.
REM Add your handling code here...
MSG * ApexSQL Enforce: ALL_DBS_FAILED
ECHO "ALL_DBS_FAILED"
GOTO END

:SOME_DBS_FAILED
REM Some of the databases processed failed and some passed.
REM Add your handling code here...
MSG * ApexSQL Enforce: SOME_DBS_FAILED
ECHO "SOME_DBS_FAILED"
GOTO END

:SUCCESS
REM Success.
REM Add your handling code here...
MSG * ApexSQL Enforce: SUCCESS
ECHO "SUCCESS"
GOTO END

:GENERAL_ERROR
REM General Error.
REM Add your handling code here...
MSG * ApexSQL Enforce: GENERAL_ERROR
ECHO "GENERAL_ERROR"
GOTO END

:ILLEGAL_ARGS_COMBO
REM Illegal argument combination/duplication. Some
REM arguments may not appear in command line at the
REM same time. Some arguments may not be duplicated
REM in command line. Some required arguments are
REM missed in command line.
REM Add your handling code here...
MSG * ApexSQL Enforce: ILLEGAL_ARGS_COMBO
ECHO "ILLEGAL_ARGS_COMBO"
GOTO END

:INVALID_VALUE
REM The value supplied for an argument is invalid.
REM Add your handling code here...
MSG * ApexSQL Enforce: INVALID_VALUE
ECHO "INVALID_VALUE"
GOTO END

:IO_ERROR
REM IO error. Some error occurred during IO operation.
REM Also returned if an application attempts to write
REM to existent file without a user having specified
REM a /force option.
REM Add your handling code here...
MSG * ApexSQL Enforce: IO_ERROR
ECHO "IO_ERROR"
GOTO END

:INSUF_PERMISSIONS
REM Insufficient permissions. Action cannot be completed
REM because a user doesn’t have required permissions.
REM Add your handling code here...
MSG * ApexSQL Enforce: INSUF_PERMISSIONS
ECHO "INSUF_PERMISSIONS"
GOTO END

:END
Exit

This example produces the output shown on the following screenshot:

For a functional example of using Enforce in a batch file, please see “How to use Enforce in a build process”.

How to use Enforce in a build process

We’ll take the following example: we want to run an ApexSQL Enforce rulebase against a database, log the action, display a message and play a certain sound if an error is encountered, or a different sound on success.

Here is the batch file we would use for that:
(you can download the file from Here. Also, be sure to replace {Your Username} placeholder for rulebase path with appropriate and modify SQL server/instance name if needed)

"C:\Program Files\ApexSQL\ApexSQLEnforce\ApexSQLEnforce.com" /v /rb:"C:\Documents and Settings\{Your Username}\My Documents\ApexSQL\ApexSQLEnforce\v2008.02\Best practices CS.axrb" /d:Northwind pubs /s:(local)\SQL2000

set ENFORCE_RETURN_CODE=%ERRORLEVEL%

echo Enforce Run Time: %Date% %time%; Return Code: %ENFORCE_RETURN_CODE% >> C:\SomeLogFile.txt

if %ENFORCE_RETURN_CODE%==0 GOTO OK
if %ENFORCE_RETURN_CODE%==-1 GOTO ALL_DBS_PASSED
if not %ENFORCE_RETURN_CODE%==0  GOTO ERROR

:OK
"C:\WINDOWS\SYSTEM32\sndrec32.exe"  /play /close "C:\Windows\Media\Tada.wav"
MSG * ApexSQL Enforce finished processing successfully
goto END

:ERROR
"C:\WINDOWS\SYSTEM32\sndrec32.exe"  /play /close "C:\Windows\Media\Notify.wav"

IF %ENFORCE_RETURN_CODE%==101 GOTO ALL_DBS_FAILED
IF %ENFORCE_RETURN_CODE%==102 GOTO SOME_DBS_FAILED
IF %ENFORCE_RETURN_CODE%==1 GOTO GENERAL_ERROR
IF %ENFORCE_RETURN_CODE%==2 GOTO ILLEGAL_ARGS_COMBO
IF %ENFORCE_RETURN_CODE%==3 GOTO INVALID_VALUE
IF %ENFORCE_RETURN_CODE%==4 GOTO IO_ERROR
IF %ENFORCE_RETURN_CODE%==5 GOTO INSUF_PERMISSIONS

:ALL_DBS_PASSED
REM All processed databases have passed.
REM Add your handling code here...
MSG * ApexSQL Enforce: ALL_DBS_PASSED
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - ALL_DBS_PASSED"
goto END

:ALL_DBS_FAILED
REM All processed databases have failed.
REM Add your handling code here...
MSG * ApexSQL Enforce: ALL_DBS_FAILED
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - ALL_DBS_FAILED"
goto END

:SOME_DBS_FAILED
REM Some of the databases processed failed and some passed.
REM Add your handling code here...
MSG * ApexSQL Enforce: SOME_DBS_FAILED
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - SOME_DBS_FAILED"
goto END

:GENERAL_ERROR
REM General Error.
REM Add your handling code here...
MSG * ApexSQL Enforce: GENERAL_ERROR
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - GENERAL_ERROR"
goto END

:ILLEGAL_ARGS_COMBO
REM Illegal argument combination/duplication. Some
REM arguments may not appear in command line at the
REM same time. Some arguments may not be duplicated
REM in command line. Some required arguments are
REM missed in command line.
REM Add your handling code here...
MSG * ApexSQL Enforce: ILLEGAL_ARGS_COMBO
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - ILLEGAL_ARGS_COMBO"
goto END

:INVALID_VALUE
REM The value supplied for an argument is invalid.
REM Add your handling code here...
MSG * ApexSQL Enforce: INVALID_VALUE
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - INVALID_VALUE"
goto END

:IO_ERROR
REM IO error. Some error occurred during IO operation.
REM Also returned if an application attempts to write
REM to existent file without a user having specified
REM a /force option.
REM Add your handling code here...
MSG * ApexSQL Enforce: IO_ERROR
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - IO_ERROR"
goto END

:INSUF_PERMISSIONS
REM Insufficient permissions. Action cannot be completed
REM because a user doesn’t have required permissions.
REM Add your handling code here...
MSG * ApexSQL Enforce: INSUF_PERMISSIONS
ECHO "ApexSQL Enforce finished processing with error code: %ENFORCE_RETURN_CODE% - INSUF_PERMISSIONS"
goto END

:END
exit

This example produces the output shown on the following screenshot:

AUTHOR
Ivan Vasic

LAST REVIEW DATE
04 August 2008

Labels: ApexSQL Enforce

posted by ApexSQL Team on Monday, August 04, 2008

0 Comments: