Creating Job Steps

Article
12/03/2008

A job step is an action that the job takes on a database or a server. Every job must have at least one job step. Job steps can be:

Executable programs and operating system commands.
Transact-SQL statements, including stored procedures and extended stored procedures.
Microsoft ActiveX scripts.
Replication tasks.
Analysis Services tasks.
Integration Services packages.

Every job step runs in a specific security context. If the job step specifies a proxy, the job step runs in the security context of the credential for the proxy. If a job step does not specify a proxy, the job step runs in the context of the SQL Server Agent service account. Only members of the sysadmin fixed server role can create jobs that do not explicitly specify a proxy.

Because job steps run in the context of a specific Microsoft Windows user, that user must have the permissions and configuration necessary for the job step to execute. For example, if you create a job that requires a drive letter or a Universal Naming Convention (UNC) path, the job steps may run under your Microsoft Windows user account while testing the tasks. However, the Windows user for the job step must also have the necessary permissions, drive letter configurations, or access to the required drive. Otherwise, the job step fails. To prevent this problem, ensure that the proxy for each job step has the necessary permissions for the task that the job step performs. For more information, see Security Considerations for SQL Server.

Job Step Logs

SQL Server Agent can write output from some job steps either to an operating system file or to the sysjobstepslogs table in the msdb database. The following job step types can write output to both destinations:

Executable programs and operating system commands.
Transact-SQL statements.
Analysis Services tasks.

Only job steps that are executed by users who are members of the sysadmin fixed server role can write job step output to operating system files. If job steps are executed by users who are members of the SQLAgentUserRole, SQLAgentReaderRole, or the SQLAgentOperatorRole fixed database roles in the msdb database, then the output from these job steps can be written only to the sysjobstepslogs table.

Job step logs are automatically deleted when jobs or job steps are deleted.

Note

Replication task and Integration Services package job step logging is handled by their respective subsystem. You cannot use SQL Server Agent to configure jog step logging for these types of job steps.

Executable Programs and Operating System Commands as Job Steps

Executable programs and operating system commands can be used as job steps. These files may have .bat, .cmd, .com, or .exe file extensions.

When you use an executable program or an operating system command as a job step, you must specify:

The process exit code returned if the command was successful.
The command to execute. To execute an operating system command, this is simply the command itself. For an external program, this is the name of the program and the arguments to the program, for example: C:\Program Files\Microsoft SQL Server\90\Tools\Binn\sqlcmd.exe -e -q "sp_who"

Note

You must provide the full path to the executable if the executable is not located in a directory specified in the system path or the path for the user that the job step runs as.

To create a job step with an executable program

To reset SQL Server Agent permissions

SQL Server Management Studio

Transact-SQL Job Steps

When you create a Transact-SQL job step, you must:

Identify the database in which to run the job.
Type the Transact-SQL statement to execute. The statement may call a stored procedure or an extended stored procedure.

Optionally, you can open an existing Transact-SQL file as the command for the job step.

Transact-SQL job steps do not use SQL Server Agent proxies. Instead, the job step runs as the owner of the job step, or as the SQL Server Agent service account if the owner of the job step is a member of the sysadmin fixed server role. Members of the sysadmin fixed server role can also specify that Transact-SQL job steps run under the context of another user by using the database_user_name parameter of the sp_add_jobstep stored procedure. For more information, see sp_add_jobstep (Transact-SQL).

Note

A single Transact-SQL job step can contain multiple batches. Transact-SQL job steps can contain embedded GO commands.

To create a Transact-SQL job step

To define Transact-SQL job step options

ActiveX Scripting Job Steps

When you create an ActiveX scripting job step, you must:

Identify the scripting language in which the job step is written.
Write the ActiveX script.

You can also open an existing ActiveX script file as the command for the job step. Alternatively, ActiveX script commands can be externally compiled (for example, using Microsoft Visual Basic) and then run as executable programs.

When a job step command is an ActiveX script, you can use the SQLActiveScriptHost object to print output to the job step history log or create COM objects. SQLActiveScriptHost is a global object that is introduced by SQL Server Agent hosting system into the script name space. The object has two methods (Print and CreateObject). The following example shows how ActiveX scripting works in Visual Basic Scripting Edition (VBScript).

' VBScript example for ActiveX Scripting job step

Sub main()
          
    ' Create a Smo.Server object. The object connects to the
    ' server on which the script is running.

    Set SmoServer = _
        CreateObject ("Microsoft.SqlServer.Management.Smo.Server")


    ' Check the number of active connections for AdventureWorks.
    
    ConnectionCount = _
        SmoServer.GetActiveDbConnectionCount("AdventureWorks")


    ' If there are active connections, close the connections.

    If ConnectionCount > 0 Then

        ' Log in the job history that this job step dropped
        ' connections to AdventureWorks.

        Print ("Dropping connections to AdventureWorks." + crLf )

        ' Drop all active connections to AdventureWorks.

        SmoServer.DropAllActiveDbConnections("AdventureWorks")

   End If

End Sub

The following example shows the same task in JScript:

// JScript example for ActiveX job step

function main() {

    // Create a Smo.Server object. The object connects to the
    // server on which the script is running.

    var SmoServer =
        CreateObject("Microsoft.SqlServer.Management.Smo.Server");

    // Check the number of active connections for AdventureWorks.

    var ConnectionCount =
        SmoServer.GetActiveDbConnectionCount("AdventureWorks");

    // If there are active connections, close the connections.

    if (ConnectionCount > 0) {

        // Log in the job history that this job step dropped
        // connections to AdventureWorks.

        Print ("Dropping connections to AdventureWorks.\n");

        // Drop all active connections to AdventureWorks.

        SmoServer.DropAllActiveDbConnections("AdventureWorks");
    }
}

To create an ActiveX script job step

Replication Job Steps

When you create publications and subscriptions using replication, replication jobs are created by default. The type of job created is determined by the type of replication (snapshot, transactional, or merge) and the options used.

Replication job steps activate one of these replication agents:

Snapshot Agent (Snapshot job)
Log Reader Agent (LogReader job)
Distribution Agent (Distribution job)
Merge Agent (Merge job)
Queue Reader Agent (QueueReader job)

When replication is set up, you can specify to run the replication agents in one of three ways: continuously after SQL Server Agent is started, on demand, or according to a schedule. For more information about replication agents, see Replication Agents Overview.

Analysis Services Job Steps

SQL Server Agent supports two distinct types of Analysis Services job steps, command job steps, and query job steps.

Analysis Services Command Job Steps

When you create an Analysis Services command job step, you must:

Identify the database OLAP server in which to run the job step.
Type the statement to execute. The statement must be an XML for Analysis Services Execute method. The statement may not contain a complete SOAP envelope or an XML for Analysis Services Discover method. Notice that, while SQL Server Management Studio supports complete SOAP envelopes and the Discover method, SQL Server Agent job steps do not.

For more information on XML for Analysis Services, see XML for Analysis Overview (XMLA).

Analysis Services Query Job Steps

When you create an Analysis Services query job step, you must:

Identify the database OLAP server in which to run the job step.
Type the statement to execute. The statement must be a multidimensional expressions (MDX) query.

For more information on MDX, see MDX Query Fundamentals (MDX).

Integration Services Packages

When you create an Integration Services package job step, you must:

Identify the source of the package.
Identify the location of the package.
If configuration files are required for the package, identify the configuration files.
If command files are required for the package, identify the command files.
Identify the verification to use for the package. For example, you can specify that the package must be signed, or that the package must have a specific package ID.
Identify the data sources for the package.
Identify the log providers for the package.
Specify variables and values to set before running the package.
Identify execution options.
Add or modify command-line options.

For more information about creating job steps that run Integration Services packages, see How to: Run a Package Using a SQL Server Agent Job

Share via