Visual Basic Job Step Result  

Home
Up
What's New
DTS
SQL-DMO
SQL-NS
XP's
Misc. Tools
Events
FAQ
Links
About...

 

How to fail or succeed a job step from a Visual Basic 6.0 program?

version: 1.0.0.0

last updated: 28 November 2002

If you are executing Visual Basic programs via SQL Agent using the CmdExec job step type, the job step by default will always succeed. In order to programmatically make the job step succeed or fail, you have to do something extra. This article describes what you need to do to accomplish this.

Visual Basic programs are Windows programs (using WinMain as the entry point for the application), however they always return a process exit code of 0 (zero) which is interpreted as success by the CmdExec job step. No matter if the application raises an error, they process exit code will be 0 (zero). This is easy to verify using a very simple batch or command file, which prints the process exit code.

The batch file would look like this:

exit.exe
echo %errorlevel%

The sample application is called EXIT.EXE, which is a Visual Basic application, if you are not doing anything special the batch file will always return 0 (zero) and you will have no means to distinguish success or failure of your program from the job step.

You can slightly change your Visual Basic program to set the process exit code, by using the Win32 ExitProcess function, which you need to import from KERNEL32.DLL. You call this function to exit / terminate your program and set the process exit code, so that the CmdExec job step can pick it up and you can use it to determine your job flow inside SQL Agent.

A sample Visual Basic program that just sets the process exit code, would like this:

Option Explicit
Public Declare Sub ExitProcess Lib "kernel32" (ByVal uExitCode As Long)

Public Sub main()
    Dim rc As Long

    ' return code (default for CmdExec Job Step exitcode 0 implies success)
    rc = 1

    Call ExitProcess(rc)
End Sub

This article applies both to SQL Server 7.0 and SQL Server 2000

Download VisualBasicJobStepResult.zip

***

Questions or problems regarding this web site should be directed to webmaster@sqldev.net.
Copyright © 1991-2003 SQLDev.Net. All rights reserved.
Last modified: 07/06/03.