在 SQL Server 代理中计划自动管理任务

项目
06/18/2013

在 SMO 中，SQL Server 代理由下列对象表示：

JobServer 对象具有作业、警报和操作员三个集合。
OperatorCollection 对象表示可由 Microsoft SQL Server 代理自动向其发送事件通知的寻呼程序、电子邮件地址和 net send 操作员列表。
AlertCollection 对象表示由 SQL Server 监视的系统事件或性能条件等情况的列表。
JobCollection 对象稍微复杂一些。它表示按指定计划运行的多步骤任务列表。步骤和计划信息存储在 JobStep 和 JobSchedule 对象中。

SQL Server 代理对象位于 Microsoft.SqlServer.Management.Smo.Agent 命名空间中。

示例

若要使用所提供的任何代码示例，您必须选择创建应用程序所需的编程环境、编程模板和编程语言。有关详细信息，请参阅在 Visual Studio .NET 中创建 Visual Basic SMO 项目或在 Visual Studio .NET 中创建 Visual C# SMO 项目。

对于使用 SQL Server 代理的程序，必须包括 Imports 语句以限定该代理的命名空间。请在应用程序的其他 Imports 语句之后、任何声明之前插入该语句，例如：

Imports Microsoft.SqlServer.Management.Smo

Imports Microsoft.SqlServer.Management.Common

Imports Microsoft.SqlServer.Management.Smo.Agent

在 Visual Basic 中创建包含步骤和计划的作业

此代码示例将创建包含步骤和计划的作业，然后通知操作员。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Define an Operator object variable by supplying the Agent (parent JobServer object) and the name in the constructor.
Dim op As [Operator]
op = New [Operator](srv.JobServer, "Test_Operator")
'Set the Net send address.
op.NetSendAddress = "Network1_PC"
'Create the operator on the instance of SQL Agent.
op.Create()
'Define a Job object variable by supplying the Agent and the name arguments in the constructor and setting properties.
Dim jb As Job
jb = New Job(srv.JobServer, "Test_Job")
'Specify which operator to inform and the completion action.
jb.OperatorToNetSend = "Test_Operator"
jb.NetSendLevel = CompletionAction.Always
'Create the job on the instance of SQL Agent. 
jb.Create()
'Define a JobStep object variable by supplying the parent job and name arguments in the constructor.
Dim jbstp As JobStep
jbstp = New JobStep(jb, "Test_Job_Step")
jbstp.Command = "Test_StoredProc"
jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess
jbstp.OnFailAction = StepCompletionAction.QuitWithFailure
'Create the job step on the instance of SQL Agent.
jbstp.Create()
'Define a JobSchedule object variable by supplying the parent job and name arguments in the constructor. 
Dim jbsch As JobSchedule
jbsch = New JobSchedule(jb, "Test_Job_Schedule")
'Set properties to define the schedule frequency, and duration.
jbsch.FrequencyTypes = FrequencyTypes.Daily
jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Minute
jbsch.FrequencySubDayInterval = 30
Dim ts1 As TimeSpan
ts1 = New TimeSpan(9, 0, 0)
jbsch.ActiveStartTimeOfDay = ts1
Dim ts2 As TimeSpan
ts2 = New TimeSpan(17, 0, 0)
jbsch.ActiveEndTimeOfDay = ts2
jbsch.FrequencyInterval = 1
Dim d As Date
d = New Date(2003, 1, 1)
jbsch.ActiveStartDate = d
'Create the job schedule on the instance of SQL Agent.
jbsch.Create()

在 Visual C# 中创建包含步骤和计划的作业

此代码示例将创建包含步骤和计划的作业，然后通知操作员。

{
            //Connect to the local, default instance of SQL Server.
            Server srv = new Server();

            //Define an Operator object variable by supplying the Agent (parent JobServer object) and the name in the constructor. 
            Operator op = new Operator(srv.JobServer, "Test_Operator");

            //Set the Net send address. 
            op.NetSendAddress = "Network1_PC";

            //Create the operator on the instance of SQL Server Agent. 
            op.Create();

            //Define a Job object variable by supplying the Agent and the name arguments in the constructor and setting properties. 
            Job jb = new Job(srv.JobServer, "Test_Job");

            //Specify which operator to inform and the completion action. 
            jb.OperatorToNetSend = "Test_Operator";
            jb.NetSendLevel = CompletionAction.Always;

            //Create the job on the instance of SQL Server Agent. 
            jb.Create();

            //Define a JobStep object variable by supplying the parent job and name arguments in the constructor. 
            JobStep jbstp = new JobStep(jb, "Test_Job_Step");
            jbstp.Command = "Test_StoredProc";
            jbstp.OnSuccessAction = StepCompletionAction.QuitWithSuccess;
            jbstp.OnFailAction = StepCompletionAction.QuitWithFailure;

            //Create the job step on the instance of SQL Agent. 
            jbstp.Create();

            //Define a JobSchedule object variable by supplying the parent job and name arguments in the constructor. 
           
            JobSchedule jbsch = new JobSchedule(jb, "Test_Job_Schedule");

            //Set properties to define the schedule frequency, and duration. 
            jbsch.FrequencyTypes = FrequencyTypes.Daily;
            jbsch.FrequencySubDayTypes = FrequencySubDayTypes.Minute;
            jbsch.FrequencySubDayInterval = 30;
            TimeSpan ts1 = new TimeSpan(9, 0, 0);
            jbsch.ActiveStartTimeOfDay = ts1;
            
            TimeSpan ts2 = new TimeSpan(17, 0, 0);
            jbsch.ActiveEndTimeOfDay = ts2;
            jbsch.FrequencyInterval = 1;
          
            System.DateTime d = new System.DateTime(2003, 1, 1);
            jbsch.ActiveStartDate = d;

            //Create the job schedule on the instance of SQL Agent. 
            jbsch.Create();
        }

在 PowerShell 中创建包含步骤和计划的作业

此代码示例将创建包含步骤和计划的作业，然后通知操作员。

#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server

#Define an Operator object variable by supplying the Agent (parent JobServer object) and the name in the constructor.
$op = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Operator -argumentlist $srv.JobServer, "Test_Operator"

#Set the Net send address.
$op.NetSendAddress = "Network1_PC"

#Create the operator on the instance of SQL Agent.
$op.Create()

#Define a Job object variable by supplying the Agent and the name arguments in the constructor and setting properties. 
$jb = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Job -argumentlist $srv.JobServer, "Test_Job" 

#Specify which operator to inform and the completion action. 
$jb.OperatorToNetSend = "Test_Operator"; 
$jb.NetSendLevel = [Microsoft.SqlServer.Management.SMO.Agent.CompletionAction]::Always

#Create the job on the instance of SQL Server Agent. 
$jb.Create()

#Define a JobStep object variable by supplying the parent job and name arguments in the constructor. 
$jbstp = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobStep -argumentlist $jb, "Test_Job_Step" 
$jbstp.Command = "Test_StoredProc"; 
$jbstp.OnSuccessAction = [Microsoft.SqlServer.Management.SMO.Agent.StepCompletionAction]::QuitWithSuccess; 
$jbstp.OnFailAction =[Microsoft.SqlServer.Management.SMO.Agent.StepCompletionAction]::QuitWithFailure; 

#Create the job step on the instance of SQL Agent. 
$jbstp.Create(); 

#Define a JobSchedule object variable by supplying the parent job and name arguments in the constructor. 
$jbsch =  New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.JobSchedule -argumentlist $jb, "Test_Job_Schedule" 

#Set properties to define the schedule frequency, and duration. 
$jbsch.FrequencyTypes =  [Microsoft.SqlServer.Management.SMO.Agent.FrequencyTypes]::Daily

$jbsch.FrequencySubDayTypes = [Microsoft.SqlServer.Management.SMO.Agent.FrequencySubDayTypes]::Minute
$jbsch.FrequencySubDayInterval = 30
$ts1 =  New-Object -TypeName TimeSpan -argumentlist 9, 0, 0
$jbsch.ActiveStartTimeOfDay = $ts1
$ts2 = New-Object -TypeName TimeSpan -argumentlist 17, 0, 0
$jbsch.ActiveEndTimeOfDay = $ts2
$jbsch.FrequencyInterval = 1
$jbsch.ActiveStartDate = "01/01/2003"

#Create the job schedule on the instance of SQL Agent. 
$jbsch.Create();

在 Visual Basic 中创建警报

此代码示例将创建由性能条件触发的警报。该条件必须以特定格式提供：

ObjectName|CounterName|Instance|ComparisionOp|CompValue

对于警报通知，操作员是必需的。 Operator 类型需要方括号，因为 operator 是一个 Visual Basic 关键字。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Define an Alert object variable by supplying the SQL Agent and the name arguments in the constructor.
Dim al As Alert
al = New Alert(srv.JobServer, "Test_Alert")
'Specify the performance condition string to define the alert.
al.PerformanceCondition = "SQLServer:General Statistics|User Connections||>|3"
'Create the alert on the SQL Agent.
al.Create()
'Define an Operator object variable by supplying the SQL Agent and the name arguments in the constructor.
Dim op As [Operator]
op = New [Operator](srv.JobServer, "Test_Operator")
'Set the net send address.
op.NetSendAddress = "NetworkPC"
'Create the operator on the SQL Agent.
op.Create()
'Run the AddNotification method to specify the operator is notified when the alert is raised.
al.AddNotification("Test_Operator", NotifyMethods.NetSend)

在 Visual C# 中创建警报

此代码示例将创建由性能条件触发的警报。该条件必须以特定格式提供：

ObjectName|CounterName|Instance|ComparisionOp|CompValue

对于警报通知，操作员是必需的。 Operator 类型需要方括号，因为 operator 是一个 Visual C# 关键字。

{
             //Connect to the local, default instance of SQL Server. 
            Server srv = new Server();

            //Define an Alert object variable by supplying the SQL Server Agent and the name arguments in the constructor. 
            Alert al = new Alert(srv.JobServer, "Test_Alert");

            //Specify the performance condition string to define the alert. 
            al.PerformanceCondition = "SQLServer:General Statistics|User Connections||>|3";

            //Create the alert on the SQL Agent. 
            al.Create();

            //Define an Operator object variable by supplying the SQL Server Agent and the name arguments in the constructor. 
         
            Operator op = new Operator(srv.JobServer, "Test_Operator");
            //Set the net send address. 
            op.NetSendAddress = "NetworkPC";
            //Create the operator on the SQL Agent. 
            op.Create();
            //Run the AddNotification method to specify the operator is notified when the alert is raised. 
            al.AddNotification("Test_Operator", NotifyMethods.NetSend);
        }

在 PowerShell 中创建警报

此代码示例将创建由性能条件触发的警报。该条件必须以特定格式提供：

ObjectName|CounterName|Instance|ComparisionOp|CompValue

对于警报通知，操作员是必需的。 Operator 类型需要方括号，因为 operator 是一个 Visual C# 关键字。

#Get a server object which corresponds to the default instance
$srv = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Server

#Define an Alert object variable by supplying the SQL Agent and the name arguments in the constructor.
$al = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Alert -argumentlist $srv.JobServer, "Test_Alert"

#Specify the performance condition string to define the alert.
$al.PerformanceCondition = "SQLServer:General Statistics|User Connections||>|3"

#Create the alert on the SQL Agent.
$al.Create()

#Define an Operator object variable by supplying the Agent (parent JobServer object) and the name in the constructor.
$op = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Agent.Operator -argumentlist $srv.JobServer, "Test_Operator"

#Set the Net send address.
$op.NetSendAddress = "Network1_PC"

#Create the operator on the instance of SQL Agent.
$op.Create()

#Run the AddNotification method to specify the operator is notified when the alert is raised.
$ns = [Microsoft.SqlServer.Management.SMO.Agent.NotifyMethods]::NetSend
$al.AddNotification("Test_Operator", $ns)

#Drop the alert and the operator
$al.Drop()
$op.Drop()

在 Visual Basic 中使用代理帐户允许用户访问子系统

此代码示例演示如何使用 ProxyAccount 对象的 AddSubSystem 方法来允许用户访问指定的子系统。

'Connect to the local, default instance of SQL Server.
Dim srv As Server
srv = New Server
'Declare a JobServer object variable and reference the SQL Agent.
Dim js As JobServer
js = srv.JobServer
'Define a Credential object variable by supplying the parent server and name arguments in the constructor.
Dim c As Credential
c = New Credential(srv, "Proxy_accnt")
'Set the identity to a valid login represented by the vIdentity string variable. 
'The sub system will run under this login.
c.Identity = vIdentity
'Create the credential on the instance of SQL Server.
c.Create()
'Define a ProxyAccount object variable by supplying the SQL Agent, the name, the credential, the description arguments in the constructor.
Dim pa As ProxyAccount
pa = New ProxyAccount(js, "Test_proxy", "Proxy_accnt", True, "Proxy account for users to run job steps in command shell.")
'Create the proxy account on the SQL Agent.
pa.Create()
'Add the login, represented by the vLogin string variable, to the proxy account. 
pa.AddLogin(vLogin)
'Add the CmdExec subsytem to the proxy account. 
pa.AddSubSystem(AgentSubSystem.CmdExec)
'Now users logged on as vLogin can run CmdExec job steps with the specified credentials.

在 Visual C# 中使用代理帐户允许用户访问子系统

此代码示例演示如何使用 ProxyAccount 对象的 AddSubSystem 方法来允许用户访问指定的子系统。

//Connect to the local, default instance of SQL Server. 
{ 
Server srv = default(Server); 
srv = new Server(); 
//Declare a JobServer object variable and reference the SQL Server Agent. 
JobServer js = default(JobServer); 
js = srv.JobServer; 
//Define a Credential object variable by supplying the parent server and name arguments in the constructor. 
Credential c = default(Credential); 
c = new Credential(srv, "Proxy_accnt"); 
//Set the identity to a valid login represented by the vIdentity string variable. 
//The sub system will run under this login. 
c.Identity = vIdentity; 
//Create the credential on the instance of SQL Server. 
c.Create(); 
//Define a ProxyAccount object variable by supplying the SQL Server Agent, the name, the credential, the description arguments in the constructor. 
ProxyAccount pa = default(ProxyAccount); 
pa = new ProxyAccount(js, "Test_proxy", "Proxy_accnt", true, "Proxy account for users to run job steps in command shell."); 
//Create the proxy account on the SQL Agent. 
pa.Create(); 
//Add the login, represented by the vLogin string variable, to the proxy account. 
pa.AddLogin(vLogin); 
//Add the CmdExec subsytem to the proxy account. 
pa.AddSubSystem(AgentSubSystem.CmdExec); 
} 
//Now users logged on as vLogin can run CmdExec job steps with the specified credentials.

请参阅

概念

SQL Server 代理

执行作业

通过

在 SQL Server 代理中计划自动管理任务

示例

在 Visual Basic 中创建包含步骤和计划的作业

在 Visual C# 中创建包含步骤和计划的作业

在 PowerShell 中创建包含步骤和计划的作业

在 Visual Basic 中创建警报

在 Visual C# 中创建警报

在 PowerShell 中创建警报

在 Visual Basic 中使用代理帐户允许用户访问子系统

在 Visual C# 中使用代理帐户允许用户访问子系统

请参阅

概念

其他资源