你知道作業步驟T-SQL指令碼裡可以使用Token與逸出巨集嗎?

　　最近看到一行有著看不懂字串的T-SQL，類似像下面這行
　　EXEC usp_StoredProcdure $(ESCAPE_NONE(JOBID))

　　從$符號開始都很奇怪，ESCAPE_NONE也不像是函數，JOBID也不是字串，這兩個也不像是關鍵字，更怪的是直接拿到SSMS上執行更會告訴我什麼語法不正確，看來別人似乎用的很高興，但我卻不知怎麼用...

　　上網搜尋了一下，原來BOL作業步驟使用 Token有提到，ESCAPE_NONE是SQL Server Agent逸出巨集 ，而JOBID是SQL Server Agent token，看說明使用token可以讓你取得作業執行階段的資訊當作變數喔，不過似乎只能用在作業步驟裡 ,所以我拿到SSMS執行會錯是正常的

　　但我一時想不到我要拿這個來做什，於是上網看看有沒有相關的應用，查到陳俊宇老師的一篇淺談偵測「死結(DeadLock)」的作法，使用「警示(Alert)」作訊息通知，以WMI 提供者為例，原來可以用警示觸發作業把當時死結資訊寫入資料表供之後查閱，這方法還真特別，我想都沒想過可以這樣用

　　另一篇則是sqlmag上的Have SQL Server Email You Error Messages Generated by Job Failures，這篇可以補強我以前作業排程執行失敗時的通知作法，因為之前我不知怎麼取得當時排程失敗的相關job資訊，所以我的作法是定時偵測前幾個小時失敗的作業，再去取得詳細的錯誤歷史訊息，發mail通知給管理者，但這樣做不夠即時啊，現在有了token就可以將job_id當變數傳入，取得當時執行失敗的作業啦，把這篇提供的例子直接拿來套用即可，現在的問題是怎麼把所有的工作排程全部都加入[失敗細節通知]步驟呢？然後還要把前幾個步驟[失敗時]的動作移到最後新增的這個步驟去？

　　我寫了一段語法用Cursor幫我處理掉這瑣事，此語法會先排除掉已經加過此作業步驟的工作，然後針對每個工作加入[失敗細節通知]的步驟，再將此[失敗細節通知]步驟前的所有步驟[失敗時]所執行的動作，改為最後這一步驟囉，理論上之後有新增的工作加進來，再執行此語法就會自動幫你加入[失敗細節通知]步驟了，重複執行也沒關係的

USE MSDB

DECLARE @stepname NVARCHAR(200)

SET @stepname = N'失敗細節通知'


DECLARE job_cur CURSOR FOR

SELECT j.name,MAX(s.step_id) FROM msdb.dbo.sysjobs j INNER JOIN msdb.dbo.sysjobsteps s
ON j.job_id = s.job_id
WHERE j.job_id NOT IN (SELECT job_id FROM msdb.dbo.sysjobsteps WHERE step_name = @stepname) GROUP BY j.name


DECLARE @jobname sysname, @stepid SMALLINT


OPEN job_cur


FETCH NEXT FROM job_cur INTO @jobname, @stepid


WHILE @@FETCH_STATUS = 0


BEGIN

BEGIN TRANSACTION


DECLARE @ReturnCode INT

--先加入[失敗細節通知]步驟
IF NOT EXISTS (SELECT * FROM msdb.dbo.sysjobs sj JOIN msdb.dbo.sysjobsteps sjs ON sj.job_id =sjs.job_id WHERE sj.NAME=@jobname AND sjs.step_NAME=@stepname)
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_name=@jobname, @step_name=@stepname,
@cmdexec_success_code=0,
@on_success_action=2,
@on_success_step_id=0,
@on_fail_action=2,
@on_fail_step_id=0,
@retry_attempts=0,
@retry_interval=0,
@os_run_priority=0, @subsystem=N'TSQL',
@command=N'EXEC spDBA_job_notification $(ESCAPE_NONE(JOBID))',
@database_name=N'master',
@flags=0

IF (@@ERROR <> 0 OR @ReturnCode <> 0)
ROLLBACK TRANSACTION
ELSE
PRINT N'['+ @jobname + N']新增步驟[' + @stepname + N']'
END

--將最後一步驟之前所有步驟的[失敗時的動作]改為[移至最後一步驟]
DECLARE @MAXID SMALLINT,@int SMALLINT
SET @int = @stepid + 1

SET @MAXID = 1

WHILE @MAXID <= @stepid
BEGIN
EXEC @ReturnCode = msdb.dbo.sp_update_jobstep @job_name = @jobname,
@step_id = @MAXID,
@on_fail_action = 4,
@on_fail_step_id = @int
IF (@@ERROR <> 0 OR @ReturnCode <> 0)
BEGIN
ROLLBACK TRANSACTION
BREAK
END
IF @MAXID > @stepid BREAK
PRINT N'***修改[' + @jobname+ N']第' + CAST(@MAXID AS VARCHAR(3))+ N'個步驟的[失敗時的動作]為[移至第' + CAST(@int AS VARCHAR(3))+ N'驟]'
SET @MAXID = @MAXID + 1
END

IF (XACT_STATE()) = 1
BEGIN
COMMIT TRANSACTION
PRINT N'套用成功'
PRINT ''
END

FETCH NEXT FROM job_cur INTO @jobname, @stepid


END


CLOSE job_cur


DEALLOCATE job_cur

　　執行完畢會有如下類似的訊息喔

[定期回收Error Log檔]新增步驟[失敗細節通知]
***修改[定期回收Error Log檔]第1個步驟的[失敗時的動作]為[移至第2驟]
套用成功

[封存 Database Mail]新增步驟[失敗細節通知]
***修改[封存 Database Mail]第1個步驟的[失敗時的動作]為[移至第6驟]
***修改[封存 Database Mail]第2個步驟的[失敗時的動作]為[移至第6驟]
***修改[封存 Database Mail]第3個步驟的[失敗時的動作]為[移至第6驟]
***修改[封存 Database Mail]第4個步驟的[失敗時的動作]為[移至第6驟]
***修改[封存 Database Mail]第5個步驟的[失敗時的動作]為[移至第6驟]
套用成功

　　供各位參考囉，有問題請指正

　　另外補充說明一下，sqlmag例子裡的spDBA_job_notification.sql，其中第57行，原本是">"，我覺得要改成">="囉，要不然第一次失敗似乎不會寄信的，如下
        AND sjh.instance_id >= sjh_Min.instance_id
　　然後有些變數得宣告為nvarchar就是了，要不然會出現問號的