【SQL Server】batファイルからジョブを同期的に実行する方法

SQL Serverでシステムを運用していると、例えば稼働させているジョブの一連の流れにbatファイルで実行している処理を組み込みたくなる場合が有ります。※バックアップ処理が完了した後にbatファイルを実行させるとか。

その場合、ジョブのステップとしてbatファイルの実行をさせることも本来出来るはずですが、いざそのように設定するとアクセス権などの問題で上手く実行してくれなかったり面倒です。

だったら逆に、batファイルでジョブを呼び出すこともできますが、その場合、「ジョブの終了を待ってbatファイル内の次の処理に移る」ことが出来ません。

どうしても同期的にジョブをbatファイルから呼びたかったので試行錯誤して出来るようになりました。

おそらく需要はあると思うので、今回はそのやり方を紹介します。

batファイルからジョブを実行する通常の方法

まずは非同期でbatファイルから単純にジョブを実行する方法を紹介します。
ジョブの同期的実行に拘らなければこちらの方が処理はシンプルです。

システムストアド「sp_start_job」の説明

SQL Serverでは、SQL Server自身の管理目的で使用しているシステムデータベースが幾つかあり、そのなかには「システムストアドプロシージャ」と呼ばれる、SQL Server自身の処理の実行や管理を目的としたストアドプロシージャが組み込まれています。
登録されているジョブの実行や停止などの操作は、システムデータベースの「msdb」内に持っている特定のストアドプロシージャ(以下「ストアド」と呼称)を使用します。

ストアドの実行については「sp_start_job」という名前のストアドに、ジョブの名前などの必要な引数を渡して呼び出してあげることで実行されます。

sp_start_jobの詳細はMicrosoftのページを参照してください。

sp_start_job (Transact-SQL) Microsoft

「sp_start_job」を実行するbatファイルの作成(非同期)

batファイルから呼び出したいジョブをSQL Server Management Studio(以下「SSMS」と呼称)で作成しておきます。
今回は「テストジョブ」という名称で作成されている仮定します。

batファイルを「sql_job_exec.bat」という名前で作成し「テストジョブ」を呼び出します。

osql -U sa -P saのパスワード -S localhost -d msdb -Q "EXEC dbo.sp_start_job N'テストジョブ'"

このバッチを実行すると、一瞬コマンドプロンプトのウィンドウが開きますが、すぐに閉じます。
これはSQL Serverに接続して対象のジョブを起動させた後、そのジョブの完了を待たないためにこのような挙動になります。

その為、当batファイルのジョブ呼び出し処理の次に別の処理を追加した場合、起動したジョブの正常終了の有無などお構いなしに次の処理が実行されてしまいます。

上記リンクの「sp_start_job」に関するMicrosoftのページにも記載されていますが、このストアドは成功か失敗の戻り値を返します。
ただ、この戻り値は“ジョブが正常終了したか否か”ではなく、“ジョブが正常起動出来たか否か”を返します。
よって、このストアドを実行した直後に戻りが返ってきてしまうため、上記のサンプルコードではそこまでの記述はしていませんが、この戻り値をbatファイル側で受け取れるようにbatファイルに記述しても、やはりジョブを呼び出した直後に次の処理が実行されてしまいます。

また、色々調べましたが、ジョブを対象のストアドで実行する場合に、batファイルからosqlやsqlcmdを使って呼ぼうが、SSMSなどでクエリから呼ぼうが、ジョブの完了まで待つような仕組みはありませんでした。

batファイルからジョブを同期的に実行する方法

色々調べたところ、ジョブの実行状態や実行履歴はシステムデータベース内のシステムテーブルに保持しており、ジョブの実行後、そのテーブルを監視することで実行したジョブが終了したか否かを検出できることがわかりました。
更に、とあるブログで上記のように「sp_start_job」を実行し、システムテーブルを監視して完了するまで待つという処理のTransact-SQLを紹介してくれている記事を見つけた為、そのTransact-SQLを流用させて頂き、「sp_start_job」をラップしたストアドを作成することで、ジョブの同期的実行が可能となりました。

参考とさせて頂いた記事は以下です。
SQL Server エージェントのジョブをクエリから同期的に実行する

sp_start_jobラップ用ストアドの作成

システムデータベースの「msdb」に対して以下のストアドをCREATEしてください。

USE [msdb]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_exec_job]
-- 引数1：実行するジョブ名
-- 引数2：開始するステップ名
-- 戻り値：0 正常 1 異常
@INPUT_JOB_NAME VARCHAR(50),
@INPUT_STEP_NAME VARCHAR(50)
AS
DECLARE @date varchar(8), @time varchar(8)
SELECT @date = CONVERT(varchar,GETDATE(), 112), @time = REPLACE(CONVERT(varchar,GETDATE(), 108),':','')
 
DECLARE @ReturnCode int;

--パラメーターを変数に代入します。
DECLARE @job sysname = N''+@INPUT_JOB_NAME+''
DECLARE @step sysname = N''+@INPUT_STEP_NAME+''

--引数を渡してsp_start_jobを実行します。
EXEC @ReturnCode = sp_start_job @job_name=@job,@step_name=@INPUT_STEP_NAME

--sp_start_jobの正常起動が出来ているかを判定し、
--sp_start_jobの戻り値が0の場合に処理を継続します。
IF @ReturnCode = 0
BEGIN
    --無限ループさせる為に必ずTrueになる条件を設定
    WHILE(0 = 0)
    BEGIN
        --指定したジョブ名で当ストアド実行日時以降に
        --開始したジョブの件数が0より大きいかを判定します。
        IF(SELECT COUNT(*) FROM sysjobhistory sh
        LEFT JOIN sysjobs sj ON sh.job_id = sj.job_id 
        WHERE sj.name = @job AND sh.step_id = 0 AND
        sh.run_date >= @date and sh.run_time >= @time) > 0
        BEGIN
            --指定したジョブ名で当ストアド実行日時以降に
            --開始したジョブの実行ステータスが1以外か否かを判定します。
            IF(SELECT run_status FROM sysjobhistory sh
            LEFT JOIN sysjobs sj ON sh.job_id = sj.job_id 
            WHERE sj.name = @job AND sh.step_id = 0 AND
            sh.run_date >= @date and sh.run_time >= @time) <> 1
            BEGIN
                --当ストアドの戻り値に0を返します。
                RETURN 0
            END
            --ループを抜けます。
            BREAK
        END
        ELSE
        BEGIN
            --5秒待ちます。
            WAITFOR DELAY '00:00:05'
        END
    END
END
ELSE
BEGIN
    --当ストアドの戻り値に1を返します。
    RETURN 1
END

ラップ用ストアド呼び出し用batファイルの作成

次にbatファイルから、作成した上記のストアドを呼び出して、実行完了時の戻り値を取得出来るようにする必要があります。
ここも工夫が必要です。

非同期のサンプルと同様に、batファイルを「sql_job_exec.bat」という名前で作成し、コマンドを以下のように記述して「テストジョブ」を呼び出します。

@echo off
REM カレントディレクトリに移動します。
cd /d %~dp0

REM ログファイル名に使用する日付を作成します。
set YYYYMMDD=%DATE:/=%

REM ログファイルのフルパスを作成します。カレントディレクトリのlogsフォルダ配下
set OutFileName=%~dp0logs\%YYYYMMDD%.log

echo 処理を開始します >> %OutFileName%
echo %date% %time% >> %OutFileName%
echo SQLServerのジョブを起動するストアドを実行します。 >> %OutFileName%
REM SQLServerのジョブを起動するストアドを実行し、戻り値が1なら処理を中断します。
osql -U sa -P saのパスワード -S localhost -d msdb -Q "EXIT(DECLARE @return_value int EXEC @return_value = dbo.sp_exec_job 'テストジョブ,'開始するステップ名' SELECT @return_value)"
IF %ERRORLEVEL% EQU 1 (
echo %date% %time% >> %OutFileName%
echo ジョブが異常終了した為処理を終了します。 >> %OutFileName%
EXIT
)
REM ジョブ実行以降に実行したい処理を以下に記述

EXIT

このbatファイルを実行した場合、非同期実行の時のようにコマンドプロンプトのウィンドウは一瞬で閉じることはなく、ジョブが終了するまで表示され続けます。

最後に

今回紹介した内容のポイントは以下だと考えています。

• osql又はsqlcmdを使用して、batファイルからSQL Serverを操作できる。
• osqlやsqlcmdで実行したクエリはSELECT文形式であればEXITで値をbat側に戻せる。
• 本来同期的実行が出来ないsp_start_jobでも別のストアドで包んでしまえば同期的実行が可能になる。

今回紹介した上記のポイントはそれぞれ色々と応用が効くテクニックです。
是非活用してください。

それでは皆様、今回も読んで頂きましてありがとうございました。

【SQL Server】バックアップとリストアを自動化しよう

今回はSQL Serverのバックアップとリストアを自動化する方法を紹介いたします。 バックアップとリストアを自動...

www.depthbomb.net

2019.12.05

【SQL Server】ジョブの実行時エラーを「データベースメール」で通知する設定方法

SQL Serverの「ジョブ」機能でバックアップ処理やデータ更新処理を自動化しているケースも多いかと思いますが、ジョブ...

www.depthbomb.net

2020.11.20