Task Scheduling Interface

SQL Enterprise Manager contains the following changes to the task scheduling interface:

• The Task Scheduling command on the Tools menu for SQL Server 6.0 is the Scheduled Tasks command on the Server menu for SQL Server 6.5.
• CmdExec tasks that are owned by users other than system administrators execute in the security context of a Windows NT user account that is called SQLExecutiveCmdExec. For more information, see SQLExecutiveCmdExec Account.
• The Command value for Transact-SQL tasks supports multiple Transact-SQL statements and procedures. For example:

  sp_who 
  go 
  sp_help 
  go
  

• A user can view tasks and task histories for only those tasks the user created and owns. However, the system administrator can view all tasks.
• Tasks now return actual error messages (if encountered). Also, the last 230 bytes (approximately) of any CmdExec task output is displayed as part of the message that is shown in the Task History dialog box.
• Scheduling support has been added for the following long-running operations: create database, expand database, create device, and expand device.
• The Task History log can be cleared from the Task Engine Options dialog box by using the Clear Task History Log button.
• The sp_runtask stored procedure has been added. With this stored procedure, a user can invoke a task from Transact-SQL. For more information about sp_runtask, see What's New for Transact-SQL.
• The number of history rows per task can now be limited by setting the Maximum Task History Rows per Task value in the Task Engine Options dialog box. Setting this value prevents a frequently occurring task from monopolizing the msdb database syshistories table.
• Task history can also be accessed by choosing the History button in the Edit Task dialog box.
• Tasks that are created to respond to alerts are supported by the following parameters, which can be set while creating a task.

For example, five servers forward their events to a master "alert management" server. Each event causes an alert to fire and the user has associated a task with each alert. In response to the five alerts, the master server will run a user-defined task for each alert. The task could be defined to log back into the forwarding server, to use the database that caused the initial alert, and to address the problem. To do this, define the task command as:

isql /Usa /p /sairedale /daccounts /Q "sp_fixit"
  

For more information, see the Microsoft SQL Server Administrator's Companion.

When using the task scheduling interface, note the following:

• Shell commands such as COPY and RENAME do not work as CmdExec tasks unless you carefully phrase the task. For example:

  cmd /C copy d:\out.txt d:\outmore.txt
    

  This is necessary because SQL Executive assumes the first space-delimited token of the command specifies an EXE or CMD module name.

• Do not run programs that require user input as CmdExec tasks, because they run on a virtual desktop and cannot be seen.