Ajax toolkit
Ajax tutorials
|
|
16.1. Programming SQL Server AgentFigure 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:
Figure 16-1. SQL Server Agent class hierarchy 16.1.1. Creating a JobA 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( );
}
}
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 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.
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.
16.1.2. Running a JobThis 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 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 16.1.3. Creating a ScheduleA 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 The schedule is defined using the properties of the JobSchedule class described in Table 16-3.
16.1.4. Scheduling a JobThis 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 16.1.5. Creating an OperatorA 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 The SMO classes used to manage SQL Server Agent operators are described in Table 16-4.
16.1.6. Creating an AlertA 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 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 When creating an alert, you must specify one of the following:
The SMO classes used to manage SQL Server Agent alerts are described in Table 16-5.
16.1.7. Creating a Proxy AccountA 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( );
}
}
After you run the code, you can view the proxies by refreshing and opening either the SQL Server Agent 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.
16.1.8. Multiserver EnvironmentsA 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.
 |
|
|
Ajax toolkit
Ajax tutorials
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