Note: This article applies to adTempus version 2.1 and earlier. Beginning in adTempus 3.0, SQL Server jobs can be run using the SQL Server Job Task.

Summary

adTempus can be used to run a SQL Server job (a job defined and run within SQL Server) using two approaches:

• By running the osql command-line tool with the necessary commands to start the job.

• By running a script that uses SQL DMO to start and monitor the job

More Information

Using osql

The simplest way to launch a SQL Server job is to run osql to execute the necessary SQL statement. This is the approach that the New Job Wizard used in adTempus version 2.0. The following example illustrates the command line parameters that would be passed to osql to start the job "Test Job":

-S serverName -Q "EXEC sp_start_job @job_name='Test Job'" -n

This is an effective approach if you only need adTempus to start the SQL Server job, but not to monitor it. Because osql returns as soon as the SQL Server job is submitted, there is no way for adTempus to wait for the SQL Server job to complete, or to report on its outcome.

Using SQL DMO

Beginning with version 2.0.1, the New Job Wizard does not use osql to execute the SQL Server job.

Instead, the New Job Wizard generates a script that uses SQL DMO to start the SQL Server job, wait for it to complete, and return the result to adTempus. Your adTempus job therefore waits for the SQL Server job to run. Further, the adTempus job step that runs the SQL Server job will only succeed if the SQL Server job succeeds. This allows you to build logic into your adTempus job to handle SQL Server job failure.

The "Run a SQL Server Job" wizard in the New Job Wizard generates a new Script Library called "ADSQLServerUtilities", which contains a function called "RunSQLJob" that starts and optionally waits for a SQL Server job.

For an example of how this is used, use the New Job Wizard to generate a job, then review the Script Execution Task that the Wizard creates for the job.