Summary

This article explains how to use adTempus and the osql utility to execute Transact-SQL (T-SQL) commands against a SQL Server database.

More Information

adTempus does not have a native capability to execute SQL commands against a SQL Server database. However, this can be done using the osql command-line utility that is part of SQL Server.

Setup

The osql utility is included as part of the SQL Server client tools installation. If the computer where adTempus is running does not have these tools, you must install them.

Job Configuration

To call osql from adTempus, you will use a standard Program Execution Task ("Execute a program, batch file, etc.").

Set the Target for the task to the osql program, e.g.,

C:\Program Files\Microsoft SQL Server\100\Tools\Binn\osql.exe

In the Command-Line Parameters box, specify the information needed to connect to SQL Server and execute your command. See the osql utility topic in the SQL Server documentation for complete information on command-line syntax.

Use the -S option to specify the SQL Server to connect to. For example:

-S dbserver1

will connect to the SQL Server named "dbserver1". Use

-S .

to connect to the local SQL Server instance.

The preferred authentication method for SQL Server is Windows (integrated) security. To use this method, the job must be running under a user account with the necessary permissions on the SQL Server, and use the -E option on the command line:

-S . -E

You should generally include the -b option on the command line. When this option is specified, osql will abort processing and return an exit code of "1" if an error occurs, allowing adTempus to detect that the step failed. If this option is not present, osql will execute all of your commands and will not return status information to adTempus.

If you are executing a fairly simple command, you can use the -Q option to specify the command directly on the command line:

-b -S dbserver1 -E -Q "use mydatabase; update myTable set myColumn='42' where myKey='keyvalue';"

Note that the first part of this query issues a "use" statement to switch to the correct database. Also note that the -Q option is case-sensitive: -Q instructs osql to execute the command and then terminate. If you used -q instead, osql would execute the command and then stay open, waiting for more input, which is not what you want.

If you need to issue more commands than can easily fit on the command line, use the -i option to specify the SQL script file that should be executed:

-b -S dbserver1 -E -i "c:\sql scripts\my script.sql"

Executing SQL Server Jobs

If the commands you need to execute are already encapsulated in a job within SQL Server, you can use the SQL Server Job Task to run the SQL Server job, rather than the approach described above.

Status

Beginning with adTempus 4, the Database Operation Task allows you to execute database queries and updates.