Section 16.1. Programming SQL Server Agent


16.1. Programming SQL Server Agent

Figure 16-1 shows the key SMO SQL Server Agent classes and defines the relationships between them.

These classes are in the Microsoft.SqlServer.Management.Smo.Agent namespace.

The rest of this chapter presents programming examples that show how to use SMO SQL Server Agent classes and provides descriptions of the classes. You need a reference to the following assemblies to compile and run the examples:

  • Microsoft.SqlServer.ConnectionInfo

  • Microsoft.SqlServer.Smo

  • Microsoft.SqlServer.SmoEnum

  • Microsoft.SqlServer.SqlEnum

Figure 16-1. SQL Server Agent class hierarchy


16.1.1. Creating a Job

A SQL Server Agent job specifies a series of job steps (actions) that SQL Server Agent performs according to a schedule, in response to an alert, or by executing the sp_start_job stored procedure. This example creates a SQL Server Agent job named TestJob. The job has a single job step named TestJob that runs a database consistency check on the AdventureWorks database.

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             // create the job             Job j = new Job(jobServer, "TestJob");             j.Create(  );             // set the execution target server (same as sp_add_jobserver)             j.ApplyToTargetServer("(local)");             // create the job step             JobStep js = new JobStep(j, "Step 1");             js.SubSystem = AgentSubSystem.TransactSql;             js.Command = "DBCC CHECKDB('AdventureWorks') WITH NO_INFOMSGS";             js.OnFailAction = StepCompletionAction.QuitWithFailure;             js.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

You must specify the server by using the string (local) if the job is to run on the local server. The string localhost will not work.


In this example, a job step is added to the job by creating a JobStep object and adding it to the job by passing a reference to the job in the JobStep constructor.

After you run the example, expand and refresh the SQL Server Agent Jobs node in Object Explorer to show the new job TestJob. Right-click the TestJob node and select Properties from the context menu to show the Job Properties dialog box. Select the Steps page to display the single step you added to the job, as shown in Figure 16-2.

Figure 16-2. Job Properties dialog box


The SubSystem property of the JobStep class specifies the SQL Server subsystem that is used by the job step. It is a value from the AgentSubSystem enumeration described in Table 16-1. In this example, the single step added uses the Transact SQL subsystem.

Table 16-1. AgentSubSystem enumeration

Value

SQL Server subsystem

ActiveScripting

Active Scripting

AnalysisCommand

Analysis Command

AnalysisQuery

Analysis Query

CmdExec

Operating System Command executive

Distribution

Distribution Agent

LogReader

Log Reader Agent

Merge

Merge Agent

QueueReader

Queue Reader Agent

Snapshot

Snapshot Agent

Ssis

SQL Server Integration Services (SSIS)

TRansactSql

Transact-SQL


SMO has classes used to manage SQL Server Agent jobs, job steps, job categories, filters, and schedules. These classes are described in Table 16-2.

Table 16-2. SMO classes for administering SQL Server Agent jobs

Class

Description

Job

Represents a SQL Server Agent job.

JobCategory

Represents attributes that allow jobs to be categorized.

JobCategoryCollection

Represents a collection of job categories as JobCategory objects. The JobCategories property of the JobServer class returns the job categories defined on SQL Server Agent.

JobCollection

Represents a collection of jobs as Job objects. The Jobs property of the JobServer class returns the jobs defined on SQL Server Agent.

JobFilter

Represents constraints used to restrict output of the EnumJobs( ) method of the JobServer class.

JobHistoryFilter

Represents constraints used to restrict output of the EnumJobHistory( ) method of the JobServer class.

JobSchedule

Represents a SQL Server Agent job schedule.

JobScheduleCollection

Represents a collection of job schedules as JobSchedule objects.

The SharedSchedule property of the JobServer class returns the shared schedules defined on SQL Server Agent.

The JobSchedules property of the Job class returns the job schedules assigned to the job.

JobServer

Represents the SQL Server Agent subsystem.

JobStep

Represents a SQL Server Agent job step.

JobStepCollection

Represents a collection of job steps as JobStep objects. The JobSteps property of the Job class returns the job steps defined for the job.


16.1.2. Running a Job

This example runs the job created in the preceding example:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             Job j = jobServer.Jobs["TestJob"];             j.Start(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The Start( ) method of the Job class runs the job. If you open SQL Server Agent Job Activity Monitor by selecting SQL Server Agent Job Activity Monitor in Object Explorer, you will see that the job TestJob is executing, as shown in Figure 16-3. You have to click the Refresh button on the toolbar to update the status.

Figure 16-3. SQL Server Agent Job Activity Monitor


You can view the history for the job TestJob by right-clicking the SQL Server Agent Jobs TestJob node in Object Explorer and then selecting View History from the context menu.

16.1.3. Creating a Schedule

A SQL Server Agent schedule specifies when a job runseither whenever SQL Server Agent starts, whenever CPU utilization is at a level defined as idle, at a specified date and time, or on a recurring basis. This example creates a job schedule that runs once daily at 4:00 A.M. A later example associates this schedule with the job named TestJob created in the first example in this section.

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             JobSchedule js = new JobSchedule(jobServer, "TestSchedule");             js.FrequencyTypes = FrequencyTypes.Daily;             js.FrequencyInterval = 1;             js.ActiveStartTimeOfDay = TimeSpan.FromHours(4);             js.IsEnabled = true;             js.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

After you run this program, you can view and manage the new schedule by right-clicking the SQL Server Agent Jobs node in Object Explorer and selecting Manage Schedules from the context menu.

The schedule is defined using the properties of the JobSchedule class described in Table 16-3.

Table 16-3. Properties of the JobSchedule class used to define schedule

Property

Description

ActiveEndDate

The date and time when the schedule ends.

ActiveEndTimeOfDay

The time when the schedule stops for the day.

ActiveStartDay

The date and time when the schedule starts.

ActiveStartTimeOfDay

The time when the schedule starts for the day.

FrequencyInterval

The frequency interval that specifies how often the job is scheduled to run. The FrequencyInterval property is relative to the value of the FrequencyTypes property.

FrequencyRecurrenceFactor

The number of weeks or months between scheduled jobs having weekly or monthly frequency

FrequencyRelativeIntervals

The value of a day relative to the first day of the month.

FrequencySubDayIntervals

The time (hours or minutes) between scheduled jobs having daily frequency, with a frequency subday type of hour or minute.

FrequencySubDayTypes

The unit of time used to specify the interval between scheduled jobs having daily frequency. The value is from the FrequencySubDayTypes enumerationHour, Minute, Once, or Unknown.

FrequencyTypes

The frequency specifying how often the schedule executes. The value is from the FrequencyTypes enumerationAutoStart, Daily, Monthly, MonthlyRelative, OneTime, OnIdle, Unknown, or Weekly.


16.1.4. Scheduling a Job

This example associates the job named TestJob, created in the first example in this section, with the schedule named TestSchedule, created in the preceding section:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             JobSchedule js = jobServer.SharedSchedules["TestSchedule"];             Job j = jobServer.Jobs["TestJob"];             j.AddSharedSchedule(js.ID);             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The AddSharedSchedule( ) method of the Job class associates the schedule with the job. The method uses the schedule ID number to identify the schedule.

After you run this program, you can see that job TestJob is associated with the schedule TestSchedule by right-clicking the SQL Server Agent Jobs TestJob node in Object Explorer and selecting Properties from the context menu. This opens the Job Properties dialog box. Select the Schedules page to view the list of schedules for the job. You can also right-click the SQL Server Agent Jobs node and select Manage Schedules from the context menu. This opens the Manage Schedules dialog box. Click the value in the Jobs in schedule column to display the Jobs Referencing a Schedule dialog box, which lists the jobs associated with the schedule.

16.1.5. Creating an Operator

A SQL Server Agent operator defines contact information for a SQL Server administrator. An alert can notify using email, a pager through email, or net send. This example creates an operator named Test Operator and assigns an email address to the operator:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             Operator o = new Operator(jobServer, "Test Operator");             o.EmailAddress = "test@operator.org";             o.Enabled = true;             o.Create(  );             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

The EmailAddress, NetSendAddress, and PagerAddress properties of the Operator class specify the address for the operator for the different communication options.

After you run this program, you can view the new operator by refreshing and expanding the SQL Server Agent Operators node.

The SMO classes used to manage SQL Server Agent operators are described in Table 16-4.

Table 16-4. SMO classes for administering SQL Server Agent operators

Class

Description

Operator

Represents a SQL Server Agent operator.

OperatorCategory

Represents attributes that allow operators to be categorized.

OperatorCategoryCollection

Represents a collection of operator categories as OperatorCategory objects. The OperatorCategories property of the JobServer class returns the operator categories defined on SQL Server Agent.

OperatorCollection

Represents a collection of operators as Operator objects. The Operators property of the JobServer class returns the operators defined on SQL Server Agent.


16.1.6. Creating an Alert

A SQL Server Agent alert specifies an automatic response to a specific conditioneither a SQL Server event, a SQL Server performance condition, or a Windows Management Instrumentation (WMI) event. The alert either notifies one or more operators or runs a job.

This example creates an alert that emails the operator named Test Operator, created in the preceding example, when an error with a severity of 20 is encountered:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             Alert a = new Alert(jobServer, "Test Alert");             a.Severity = 20;    // Fatal error in current process             a.Create(  );             a.AddNotification("Test Operator", NotifyMethods.NotifyEmail);             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

After you run this program, you can view the new alert by refreshing and expanding the SQL Server Agent Alerts node.

Execute the following T-SQL statement to trigger the alert:

     RAISERROR ('test error', 20, 1) WITH LOG 

The following message is displayed as a result of the error:

     Msg 2745, Level 16, State 2, Line 1     Process ID 59 has raised user error 50000, severity 20. SQL Server is       terminating this process.     Msg 50000, Level 20, State 1, Line 1     test error     Msg 0, Level 20, State 0, Line 0     A severe error occurred on the current command.  The results, if any,       should be discarded. 

You can check that the alert Test Alert occurred by right-clicking the SQL Server Agent Alerts Test Alert node in Object Explorer and selecting Properties from the context menu. Select the History page in the Alert Properties dialog box to see the date of the last alert, the date of the last response, and the total number of occurrences.

When creating an alert, you must specify one of the following:

  • A non-zero message ID

  • A non-zero severity

  • A non-null performance condition

  • A non-null WMI namespace and query

SQL Server Agent Mail is turned off by default. To enable it, right-click the SQL Server Agent node in Object Explorer and select Properties from the context menu. Next, select the Alert System page in the SQL Server Agent Properties dialog box, check the Enable mail profile checkbox, and complete the rest of the Mail Session section of the dialog page.


The SMO classes used to manage SQL Server Agent alerts are described in Table 16-5.

Table 16-5. SMO classes for administering SQL Server Agent alerts

Class

Description

Alert

Represents a SQL Server alert.

AlertCategory

Represents attributes that allow alerts to be categorized.

AlertCategoryCollection

Represents a collection of alert categories as AlertCategory objects. The AlertCategories property of the JobServer class returns the alert categories defined on SQL Server Agent.

AlertCollection

Represents a collection of alerts as Alert objects. The Alerts property of the JobServer class returns the alerts defined on SQL Server Agent.

AlertSystem

Represents defaults for all alerts defined on a SQL Server instance. The AlertSystem property of the JobServer class returns the alert system information defined on SQL Server Agent.


16.1.7. Creating a Proxy Account

A SQL Server Agent proxy account defines a security context in which a job step can run. This example creates a proxy account named Test Proxy, gives the public database role access to the proxy, and lets job steps use the Active Scripting and Operating System (CmdExec) subsystems:

     using System;     using Microsoft.SqlServer.Management.Common;     using Microsoft.SqlServer.Management.Smo;     using Microsoft.SqlServer.Management.Smo.Agent;     class Program     {         static void Main(string[] args)         {             Server server = new Server("(local)");             JobServer jobServer = server.JobServer;             ProxyAccount p = new ProxyAccount(jobServer, "Test Proxy");             // see note below about credentials             p.CredentialName = "Test Credential";             p.IsEnabled = true;             p.Create(  );             // give the public database role access to the proxy account             p.AddMsdbRole("public");             // allow Active Scripting and Operating System             p.AddSubSystem(AgentSubSystem.ActiveScripting);             p.AddSubSystem(AgentSubSystem.CmdExec);             Console.WriteLine("Press any key to continue.");             Console.ReadKey(  );         }     } 

You must create a credential before creating a proxy. Credentials contain authentication information that authenticated SQL Server users require to access resources outside of SQL Serveron the local machine or on the network domain. The credential used to create the proxy must be for a valid Windows user. Add credentials from Object Explorer in SQL Server Management Studio by selecting Security Credentials New Credential in Object Explorer. Alternatively, you can add and manage credentials by using the SMO Credential class, discussed in Chapter 11.


After you run the code, you can view the proxies by refreshing and opening either the SQL Server Agent Proxies ActiveXScript node or the SQL Server Agent Proxies Operating System (CmdExec) node in Object Explorer.

Assign a proxy to a job step by using the ProxyName property of the JobStep class. The AgentSubSystem property of the ProxyAccount class specifies the SQL Server subsystems that can be used by the proxy. You can give SQL login, database role, or server role principals access to the proxy account by using the AddLogin( ), AddMsdbRole( ), or AddServerRole( ) methods, respectively, of the ProxyAccount class.

The SMO classes used to manage SQL Server Agent proxies are described in Table 16-6.

Table 16-6. SMO classes for administering SQL Server Agent proxy accounts

Class

Description

ProxyAccount

Represents a SQL Server Agent proxy account.

ProxyAccountCollection

Represents a collection of proxy accounts as ProxyAccount objects. The ProxyAccounts property of the JobServer class returns the proxy accounts defined on SQL Server Agent.


16.1.8. Multiserver Environments

A master server defines SQL Server Agent jobs that are run on remote (target) servers. A target server downloads and executes jobs defined on a master server. Target servers are defined only on a master SQL Server Agent. For information about setting up a multiserver administration group, see Microsoft SQL Server 2005 Books Online.

Target server instances are automatically populated when you connect to a SQL Server instance defined as a master in a multiserver administration group. The SMO target server objects let you retrieve information about a target server and set the location for a target server. The SMO classes used to manage SQL Server Agent target servers are described in Table 16-7.

Table 16-7. SMO classes for administering SQL Server Agent target servers

Class

Description

TargetServer

Represents a target server for multiserver administration on SQL Server Agent. The TargetServer object is obtained using an item from the TargetServerCollection object returned by the TargetServers property of the JobServer object and cannot be created as a standalone object.

TargetServerCollection

Represents a collection of target servers as TargetServer objects. The TargetServers property of the JobServer class returns the target servers defined on SQL Server Agent.

TargetServerGroup

Represents a target server group for multiserver administration on SQL Server Agent.

TargetServerGroupCollection

Represents a collection of target server groups as TargetServerGroup objects. The TargetServerGroups property of the JobServer class returns the target server groups defined on SQL Server Agent.




Programming SQL Server 2005
Programming SQL Server 2005
ISBN: 0596004796
EAN: 2147483647
Year: 2007
Pages: 147
Authors: Bill Hamilton

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net