發行項
04/02/2012

使用 SQL Server Agent 排程自動管理工作

在 SMO 中，SQL Server Agent 是由下列物件表示：

JobServer 物件具有作業、警示和運算子的三個集合。
OperatorCollection 物件表示呼叫器、電子郵件地址和網路傳送操作員的清單，在發生事件時，SQL Server Agent 會自動加以通知。
AlertCollection 物件表示系統事件或效能條件之類情況的清單，這些情況都受到 SQL Server 的監視。
JobCollection 物件就稍微複雜一點，它表示會在指定排程執行的多重步驟工作的清單。步驟和排程資訊會儲存在 JobStep 和 JobSchedule 物件中。

SQL Server Agent 物件位於 Microsoft.SqlServer.Management.Smo.Agent 命名空間中。

範例

如果要使用所提供的任何程式碼範例，您必須選擇建立應用程式用的程式設計環境、程式設計範本及程式設計語言。如需詳細資訊，請參閱＜如何：在 Visual Studio .NET 中建立 Visual Basic SMO 專案＞或＜如何：在 Visual Studio .NET 中建立 Visual C# SMO 專案＞。

如果程式使用 SQL Server Agent，則您必須包含 Imports 陳述式來限定 Agent 命名空間。將陳述式插入至其他 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 中允許使用者使用 Proxy 帳戶存取子系統

此程式碼範例示範如何允許使用者利用 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# 中允許使用者使用 Proxy 帳戶存取子系統

此程式碼範例示範如何允許使用者利用 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 Agent

共用方式為

使用 SQL Server Agent 排程自動管理工作

範例

在 Visual Basic 中建立具有步驟和排程的作業

在 Visual C# 中建立具有步驟和排程的作業

在 PowerShell 中建立具有步驟和排程的作業

在 Visual Basic 中建立警示

在 Visual C# 中建立警示

在 PowerShell 中建立警示

在 Visual Basic 中允許使用者使用 Proxy 帳戶存取子系統

在 Visual C# 中允許使用者使用 Proxy 帳戶存取子系統

請參閱

概念

其他資源

其他資源