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

MS SQL Server
スポンサーリンク
当記事はSQL Server 2017、2012、2000で検証済みです。

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の構文
sp_start_job
{ [@job_name =] ‘job_name’ | [@job_id =] job_id }
[ , [@error_flag =] error_flag]
[ , [@server_name =] ‘server_name’]
[ , [@step_name =] ‘step_name’]
[ , [@output_flag =] output_flag]

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ファイルと同じサーバーに存在する前提です。
また、SQL Serverの認証モードは「SQL Server認証」を使用します。

batファイルからSQL Serverへの操作は「osql」というコマンドを使用しています。
「osql」はSQL Server2000などの古いバージョンでも使用出来る、今回のbatファイルの様なCUIベースでSQL Serverを操作する為のユーティリティです。
SQL Server2005からは「sqlcmd」が「osql」の進化版として使えますが、これらのコマンドは互換性があり、今回のコマンド内容であればosqlをsqlcmdに書き換えるだけで、それ以外の構文は同じ内容で同じ処理が実行出来ます。
よってosqlとsqlcmdのどちらで実装して頂いても結構です。
両コマンドの詳しい使用は下記リンクを参照のこと
osqlユーティリティ Microsoft
sqlcmdユーティリティ Microsoft

このバッチを実行すると、一瞬コマンドプロンプトのウィンドウが開きますが、すぐに閉じます。
これは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

上記のストアドのCREATE文では、15行目、24行目で実行するジョブの開始ステップ名のパラメーター作成、値受け取りを行い、27行目で「sp_start_job」の引数として渡していますが、ジョブを呼び出すだけではあれば、パラメーター及びsp_start_jobの引数はジョブ名だけで問題ありません。

動作テストをする際に開始するステップ名を指定出来た方が便利だった為この様に作ってありますが、ステップ名の指定が不要であれば15行目と24行目を削除し、27行目の引数を減らしてください。

 

ラップ用ストアド呼び出し用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

1行目から13行目までは、大して重要な内容では無いです。割とbatファイルでは定番の記述で、カレントディレクトリに移動し、今日の日付をyyyymmdd形式で作成し、処理のログファイル名として使用する為の記述です。
本題は15行目です。
15行目の-Qオプションでは、以降の文字列をSQLServerにクエリとして流します。
EXITの後に括弧で囲むことで、その内部のクエリの戻り値をbatファイル側に戻すことが出来ます。※MicrosoftのosqlのユーティリティのページのEXITの項目に説明があります。
その際には、戻したい値は必ずSELECT文として取得する必要があるため注意してください。
16行目では、%ERRORLEVEL%の値をIFで判定して分岐をしています。
%ERRORLEVEL%はbatファイルでは特殊な変数で、その直前に実行したコマンドの実行結果を自動的に格納してくれます。
15行目のosqlで実行した結果が16行目の%ERRORLEVEL%に格納される為、その値が1であればジョブの実行が正常終了しなかったと判断して処理を終了させています。

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

 

最後に

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

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

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

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

↓以前掲載した記事と今回の記事を組み合わせて頂くことでより効率的な自動化が可能です。
【SQL Server】バックアップとリストアを自動化しよう
今回はSQL Serverのバックアップとリストアを自動化する方法を紹介いたします。 バックアップとリストアを自動化することで、本番環境のDBサーバのデータをテスト環境のDBサーバに日次で上書きするといった環境も作れるので、色々と便利...
↓ジョブの実行の実行結果をSQLServerのエージェント側でメール通知させることも可能です。
【SQL Server】ジョブの実行時エラーを「データベースメール」で通知する設定方法
SQL Serverの「ジョブ」機能でバックアップ処理やデータ更新処理を自動化しているケースも多いかと思いますが、ジョブが失敗しているのに気付けなかったことは無いでしょうか? かく言う私も、最近ジョブの失敗に気付けずに数日バックアップ処理...
タイトルとURLをコピーしました