Creating Job Steps

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 operating system commands, Transact-SQL statements, Microsoft® ActiveX® scripts, or replication tasks.

CmdExec Job Steps

CmdExec job steps are operating system commands or executable programs ending with .bat, .cmd, .com, or .exe.

When you create a CmdExec job step, you must specify:

• The process exit code returned if the command was successful.
  
  
• The CmdExec command (for example,
  C:\Program Files\Microsoft SQL Server\80\Tools\Binn\Osql.exe\E\Q "sp_who").
  
  
• A full path to all executables.

To create a CmdExec job step

Enterprise Manager

Transact-SQL

SQL-DMO

To reset SQLAgentCmdExec permissions

Enterprise Manager

Transact-SQL Job Steps

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

• Identify the database in which to execute the job.
  
  
• Write the Transact-SQL statement, stored procedure, or extended stored procedure.

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

Members of the sysadmin role can write job steps to run in the context of another database user. For example, a system administrator can run a job that creates database objects in the pubs database on behalf of another database user.

Note  A single Transact-SQL job step can contain multiple batches. Transact-SQL job steps can contain embedded GO commands, just like osql.exe.

To create a Transact-SQL job step

Enterprise Manager

Transact-SQL

SQL-DMO

To define Transact-SQL job step options

Enterprise Manager

SQL-DMO

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. ActiveX script commands can alternatively be externally compiled (for example, using Microsoft Visual Basic®) and then run as CmdExec executables.

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 (PrintObject and CreateObject). The following examples show how ActiveX scripting works in Visual Basic Scripting Edition (VBScript) and PerlScript.

Rem VBScript job step example:
Sub main()
    Set DMOServer = CreateObject("SQLDMO.SQLServer")
    DMOServer.LoginSecure = True
    DMOServer.Connect "myserver"
    Print DMOServer.Name
    DMOServer.Disconnect
    Set DMOServer = Nothing
End Sub

#PerlScript job step example:
Sub main()
{
$DMOServer = $SQLActiveScriptHost->CreateObject("SQLDMO.SQLServer");
    $DMOServer->Connect("myserver", "sa");
    $SQLActiveScriptHost->Print($DMOServer->Name);
    $DMOServer->Disconnect();
    $DMOServer = undef;
}

To create an ActiveX Script job step

Enterprise Manager

Transact-SQL

SQL-DMO

Replication Job Steps

When you create a publication using replication, replication jobs are created automatically. The type of replication (snapshot, transactional, or merge) and options used determine the type of job that is created.

Replication job steps execute one of these replication agents:

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

When replication is set up, the replication agents can run continuously after SQL Server Agent is started, on demand, or according to a schedule.

Note  It is recommended that you use Replication Monitor to control replication job steps.

TML>