在設計、開發系統時，若需要利用程式來管理 SQL Server 排程及其相關作業，可藉由 SQL 指令來達到這個目的。本文即針對 SQL Server 的排程作業指令進行說明，文後並提供一實作範例，以期讀者能對這類指令有更清楚的瞭解。
NOTE SQL Server Express 不支援排程功能 (SQL Server Agent 無法啟用)；若您使用的為 Express 版而又有作業排程的需求，可參考筆者之前所發佈的幾篇文章：SQL Server 2008 Express 自動化管理 - 前言、SQL Server 2008 Express 自動化管理 (一) 定期備份、SQL Server 2008 Express 自動化管理 (二) 定期刪除資料、SQL Server 2008 Express 自動化管理 (三) 異常警示 。
以下開始說明：
0) 利用 "SQL Server 組態管理員" 啟動 SQL Server Agent
1) 作業項目
建立新的作業項目
exec msdb.dbo.sp_add_job @job_name = 'My Jobs',
@enabled = 1, -- 0: 不啟用 1：啟用
@description = 'Just for testing...'
select * from msdb.dbo.SysJobs
刪除作業項目：將一併刪除所有與該作業項目相關的資料；排程 (見第 4 節) 不會被刪除，但排程與該作業項目的關聯 (見第 5 節) 將被刪除。exec msdb.dbo.sp_delete_job @job_name = N'My Jobs' select * from msdb.dbo.SysJobs
2) 作業伺服器 - 要在哪一台伺服器執行作業項目 (若省略 @server_name 參數，則視為在執行此指令的 SQL Server 本機執行。
與作業項目建立關聯
exec msdb.dbo.sp_add_jobserver @job_name = 'My Jobs',
@server_name = 'Your SQL Server Name...'
select * from msdb.dbo.SysJobServers
刪除此關聯exec msdb.dbo.sp_delete_jobserver @job_name = 'My Jobs',
@server_name = 'Your SQL Server Name...'
select * from msdb.dbo.SysJobServers
3) 執行步驟 - 每個作業項目可包含多個執行步驟，當該項目開始執行時，會依照步驟的序號 (ID) 進行作業。
新增步驟：系統會依照 sp_add_jobsteps 的執行順序賦予序號；若想在已存在的步驟中插入新的步驟，可指定序號參數 (@step_id) 來達到目的。舉例說明，先在作業項目 My Jobs 加入兩個步驟 (不指定 @step_id)，此處務必設定 @on_success_action 參數，否則只會執行第一個步驟 (詳見 MSDN 官網)。
exec msdb.dbo.sp_add_jobstep @job_name = 'My Jobs',
@step_name = 'My Jobs Step. A',
@on_success_action = 3, -- 成功執行後，繼續執行下一步驟。
@command = 'Your Command Codes...'
exec msdb.dbo.sp_add_jobstep @job_name = 'My Jobs',
@step_name = 'My Jobs Step. B',
@on_success_action = 1, -- 成功執行後，結束該作業項目。
@command = 'Your Command Codes...'
select * from msdb.dbo.SysJobSteps
插入新步驟，並且指定 @step_id = 2，結果會將此步驟安插在原來的步驟 2, 3 之間 。exec msdb.dbo.sp_add_jobstep @job_name = 'My Jobs',
@step_id = 2,
@step_name = 'My Jobs Step. A-1',
@on_success_action = 3,
@command = 'Your Command Codes...'
select * from msdb.dbo.SysJobSteps
刪除步驟：欲刪除 "My Jobs" 中的某個步驟時，可參考以下程式 (此例將刪除第二個作業步驟 (step_id = 2))。declare @prg_job_id binary(16) select @prg_job_id = job_id from msdb.dbo.sysjobs where name = N'My Jobs' delete from msdb.dbo.SysJobSteps where job_id = @prg_job_id and step_id = 2 select * from msdb.dbo.SysJobSteps
4) 排程 - 當達到排程所設定的時間條件時，將開始執行此排程所對應的作業項目 (至於如何設定二者的關聯，請見下節 5. 排程與作業項目)。
新增排程：假設將排程設定為 "每個月第一天的 00:00:00" 執行
exec msdb.dbo.sp_add_schedule @schedule_name = 'My Scheduler',
@freq_type = 16,
@freq_interval = 1,
@freq_recurrence_factor = 1,
@active_start_time = 000000
select * from msdb.dbo.SysSchedules
(*) sp_add_schedule 參數設定資料，請見: [MSDN 官方網站] sp_add_schedule刪除排程
exec msdb.dbo.sp_delete_schedule @schedule_name = 'My Scheduler',
@force_delete = 1
select * from msdb.dbo.SysSchedules
當系統發現存在多個同名稱的排程時，執行上述指令會出現錯誤訊息，此時請利用以下程式進行刪除作業 (假設排程名稱為 My Scheduler)。
declare @same_schedule_name_counter int,
@prg_schedule_id int,
@i int
select @same_schedule_name_counter = count(*)
from msdb.dbo.SysSchedules
where name = 'My Scheduler'
select @i = 1
while @i <= @same_schedule_name_counter
begin
select top 1 @prg_schedule_id = schedule_id
from msdb.dbo.SysSchedules
where name = 'My Scheduler'
exec msdb.dbo.sp_delete_schedule @schedule_id = @prg_schedule_id,
@force_delete = 1
select @i = @i + 1
end
select * from msdb.dbo.SysSchedules
5) 排程與作業項目的關聯
建立關聯
exec msdb.dbo.sp_attach_schedule @job_name = 'My Jobs',
@schedule_name = 'My Scheduler'
select * from msdb.dbo.SysJobSchedules
卸除關聯exec msdb.dbo.sp_detach_schedule @job_name = 'My Jobs',
@schedule_name = 'My Scheduler'
select * from msdb.dbo.SysJobSchedules
6) 查看作業項目狀況
select sja.*
from msdb.dbo.SysJobActivity sja,
msdb.dbo.SysJobs sj
where sja.job_id = sj.job_id
and sj.name = 'My Jobs'
7) 查看作業項目執行紀錄 - 若執行失敗，相關的訊息也會記錄於 msdb.dbo.SysJobHistory 的 message 欄位。
select jh.*
from msdb.dbo.SysJobHistory jh,
msdb.dbo.SysJobs j
where j.job_id = jh.job_id
and j.name = 'My Jobs'
實作範例
-- 建立測試 Database, Table -----------------------------------------
create database TEST_DB
go
create table TEST_DB.dbo.Job (c1 int)
go
-- 建立作業項目: My Jobs --------------------------------------------
exec msdb.dbo.sp_add_job @job_name = 'My Jobs',
@enabled = 1,
@description = 'Just for testing...'
select * from msdb.dbo.SysJobs
go
-- 設定 欲執行此作業項目的伺服器 ------------------------------------
exec msdb.dbo.sp_add_jobserver @job_name = 'My Jobs',
@server_name = 'Your SQL Server Name...'
select * from msdb.dbo.SysJobServers
go
-- 建立步驟 ---------------------------------------------------------
exec msdb.dbo.sp_add_jobstep @job_name = 'My Jobs',
@step_name = 'My Jobs Step. A',
@on_success_action = 3,
@command = 'insert into TEST_DB.dbo.Job values(1)'
exec msdb.dbo.sp_add_jobstep @job_name = 'My Jobs',
@step_name = 'My Jobs Step. B',
@on_success_action = 1,
@command = 'insert into TEST_DB.dbo.Job values(2)'
select * from msdb.dbo.SysJobSteps
go
-- 建立排程 ---------------------------------------------------------
-- 設定每月 4 日上午 11:40 執行
exec msdb.dbo.sp_add_schedule @schedule_name = 'My Scheduler',
@freq_type = 16,
@freq_interval = 4,
@freq_recurrence_factor = 1,
@active_start_time = 114000
select * from msdb.dbo.SysSchedules
go
-- 將排程指定給作業項目 ---------------------------------------------
exec msdb.dbo.sp_attach_schedule @job_name = 'My Jobs',
@schedule_name = 'My Scheduler'
select * from msdb.dbo.SysJobSchedules
go
-- 狀態查詢 ---------------------------------------------------------
-- 作業項目 .....................................
select sja.*
from msdb.dbo.SysJobActivity sja,
msdb.dbo.SysJobs sj
where sja.job_id = sj.job_id
and sj.name = 'My Jobs'
-- 執行紀錄 .....................................
select jh.*
from msdb.dbo.SysJobHistory jh,
msdb.dbo.SysJobs j
where j.job_id = jh.job_id
and j.name = 'My Jobs'
