SQL Server で sp_send_dbmail を使った通知バッチを作ると、「エラーは出ないのにメールが届かない」「一部のユーザーだけ送信されない」といったトラブルがよく発生します。本記事では、典型的なストアドプロシージャ構成を題材に、原因パターンと安全な実装例、チェックポイントを実務目線で整理します。
sp_send_dbmail でメールが送信されないときにまず押さえること
sp_send_dbmail は、SQL Server の msdb データベースに用意されたシステムストアドプロシージャで、Database Mail プロファイルを通じてメール送信を行います。呼び出し時点ではメールを即座に SMTP サーバーへ投げるのではなく、「Database Mail キュー」に mailitem_id を登録し、その後バックグラウンドプロセスが順次送信します。
そのため、アプリ側（T-SQL）と実際のメール送信の間にはレイヤーがいくつもあり、設計や実装の不備があると「送られたはずなのに届かない」「たまにしか届かない」という現象になります。
本記事では、次のようなストアドプロシージャ構成を前提に、問題点と解決策を解説します。
EmployeesとAuthorizedUsersを突き合わせて対象者の氏名とメールを抽出する- 抽出結果を
Notificationsテーブルに書き込み（IsSent = 'No'） IsSent = 'No'の行を取り出し、sp_send_dbmail で送信する- 送信後に
IsSent = 'Yes'へ更新する
症状から見る「ありがちパターン」
まず、現場でよく見かける「メールが送れない／おかしい」症状と、そのとき疑うべきポイントを整理します。
| 症状 | よくある原因 |
|---|---|
| 全くメールが届かない | Database Mail 未設定、プロファイル名の誤字、権限不足、キューに詰まっている |
| 一部の人にだけ届かない | メールアドレスの NULL / 空文字、変数連結ミス、カーソル設計ミス |
| 同じ人に何通も届く | IsSent 更新のタイミング不備、並行実行による重複送信 |
| ストアドは成功するが、ログにエラーが出ている | Database Mail 側のエラー（SMTP 認証・接続エラー等）の見落とし |
今回のケースでの主な原因整理
受信者アドレス変数の扱いミス
最も多いのが、受信者アドレス用の変数をループ内で誤って連結してしまうパターンです。
-- よくある誤った書き方のイメージ
SELECT @email = @email + ';' + Email
FROM dbo.Notifications
WHERE IsSent = 'No';
この書き方には、次のような問題があります。
@emailが NULL のまま連結すると、結果もずっと NULL のままになる- カーソルで 1 行ずつ処理しているつもりでも、毎回「テーブル全体」を SELECT してしまい、意図と異なるアドレスリストになる
- 本文側は 1人1通を前提とした個別メッセージなのに、受信者だけ複数まとめてしまい整合性が崩れる
本文に宛名やユーザー固有情報を含めるときは、基本的に 1レコード＝1通 で送るのが鉄則です。受信者変数はカーソルで FETCH した 1 行分だけをそのまま sp_send_dbmail の @recipients に渡す実装に変えましょう。
カーソルの設計不備
カーソルで mailid, email_addr, mailContent を FETCH しておきながら、そのあと別の SELECT で変数を上書きしてしまう実装もよく見かけます。これではカーソルの意味が薄れ、どの行に対して送信したのかが曖昧になります。
カーソルを使うのであれば、次のような原則を守ると安全です。
- カーソルの SELECT で、送信に必要な列（ID・宛先・本文）をすべて取得する
- ループ内で「別の SELECT で同じテーブルを読まない」ようにする
- 送信対象を絞る条件（
IsSent = 0など）は SELECT 側できちんと指定する
送信と IsSent 更新がバラバラに実行されている
sp_send_dbmail の呼び出しと、Notifications.IsSent の更新を別々に実行していると、次のような不整合が起きます。
- メール送信に成功したが、UPDATE だけ失敗して「未送信扱い」のまま残る
- 逆に、送信前に UPDATE が成功し、その後 sp_send_dbmail でエラーになっているのに検知できない
この問題を防ぐには、送信とフラグ更新を同一トランザクションにまとめる ことが重要です。トランザクション内で sp_send_dbmail を呼び、成功したと判断できたら IsSent を更新し、どちらかで例外が起きたら ROLLBACK します。
INSERT 部分に不要なカーソルを使っている
通知レコードを作成する処理（INSERT INTO Notifications ...）にカーソルを使うケースもありますが、ここはほぼ確実に 集合指向クエリ 1 本 で書き直せます。
例えば、「Employees に存在しない AuthorizedUsers だけを抽出し、メールアドレスが NULL でないものを Notifications に挿入する」といった処理なら、カーソルは不要です。セットベースで書くことで、実装がシンプルになりパフォーマンスも向上します。
その他の細かな実装上の問題
- 変数の初期化不足（NULL のまま連結・比較してしまう）
FETCH NEXTを忘れて無限ループ状態になるORDER BYをカーソル定義ではなく別クエリに書いてしまい、想定順序で処理できていない- 文字列に紛れ込んだ不要なトークン（例：誤って残した
'12334 r'など）による構文エラー
このあたりはコードレビューや PRINT デバッグで潰していきましょう。
改善方針：通知の作成は集合指向、送信は 1 行ずつ安全に
上記の問題を避けるため、次の方針で設計し直すと安定します。
| 処理ステップ | 改善方針 |
|---|---|
| 通知レコードの作成 | 集合指向の INSERT 1 文で一括挿入（カーソルは使わない） |
| 送信対象の抽出 | IsSent = 0 の行をカーソルで 1 行ずつ取得 |
| メール送信 | 各行ごとに sp_send_dbmail を呼び出し、本文は個別化 |
| 送信済みフラグの更新 | 送信と同じトランザクション内で IsSent = 1 と SentAt を更新 |
| 再送防止・並行制御 | カーソルの SELECT に WITH (UPDLOCK, READPAST) を付与 |
Notifications テーブルの推奨設計
まず、通知をキューイングするための Notifications テーブルの列設計を整理します。
| 列名 | 型（例） | 用途・ポイント |
|---|---|---|
mailid | int IDENTITY(1,1) | Notifications 内での一意 ID。カーソルでの ORDER BY で使用。 |
mailContent | nvarchar(max) | 本文。プレーンテキストまたは HTML。 |
FullName | nvarchar(100) | 宛名（受信者の氏名）。 |
email_addr | nvarchar(320) | 受信者メールアドレス。RFC 的には 254 文字ですが余裕を持たせることも多い。 |
sender | nvarchar(100) | 送信者識別用（From に直接使わない場合でも記録用に持っておくと便利）。 |
IsSent | bit | 送信済みフラグ。文字列 'Yes'/'No' ではなく bit を推奨。 |
SentAt | datetime2(0) など | 送信日時。監査・トラブルシュートに必須。 |
フラグは bit にしておくと、インデックス効率や比較の記述がシンプルになります。また、SentAt を持っておくと「いつ送ったか」「二重送信していないか」の確認が容易になります。
修正版ストアドプロシージャのサンプル
ここからは、実際に動く形のサンプル実装を示します。前提として上記のような Notifications テーブルを持っているものとします。
CREATE OR ALTER PROCEDURE dbo.GetRegistrationInfo
AS
BEGIN
SET NOCOUNT ON;
----------------------------------------------------------------------
-- 1) 通知レコードを集合指向で作成（未登録ユーザーのみ）
----------------------------------------------------------------------
;WITH target AS (
SELECT
FullName = au.Name, -- 実際の列名に合わせて修正
Email = COALESCE(NULLIF(au.PersonalEmail, ''), au.WorkEmail)
FROM dbo.AuthorizedUsers AS au
WHERE NOT EXISTS (
SELECT 1
FROM dbo.Employees AS e
WHERE e.Email IN (au.WorkEmail, au.PersonalEmail)
)
)
INSERT INTO dbo.Notifications (mailContent, FullName, email_addr, sender, IsSent)
SELECT
mailContent = CONCAT(
'This is a computer generated email message.', CHAR(13)+CHAR(10),
'Please DO NOT use the REPLY button above to respond to this email.', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10),
'Dear ', t.FullName, ':', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10),
'Thanks for registering for the Training!', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10),
'Below are details of your registration information:', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10),
'Your UserName is: ', t.Email, '.', CHAR(13)+CHAR(10),
'Your Password is: ', '<TEMP_PASSWORD>', '.', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10),
'Once you have retrieved your login information, please use the link below to log in.', CHAR(13)+CHAR(10),
'http://servername/training/', CHAR(13)+CHAR(10), CHAR(13)+CHAR(10),
'Regards,', CHAR(13)+CHAR(10),
'The Registrations & Elections Office.'
),
t.FullName,
t.Email,
'NoReply@serverdomain',
CAST(0 AS bit)
FROM target AS t
WHERE t.Email IS NOT NULL;
----------------------------------------------------------------------
-- 2) 未送信分を 1 件ずつ送信（本文が個別化されているため）
----------------------------------------------------------------------
DECLARE
@mailid int,
@recipient nvarchar(320),
@content nvarchar(max);
DECLARE cur_send CURSOR LOCAL FAST_FORWARD FOR
SELECT mailid, email_addr, mailContent
FROM dbo.Notifications WITH (UPDLOCK, READPAST)
WHERE IsSent = 0
ORDER BY mailid;
OPEN cur_send;
FETCH NEXT FROM cur_send INTO @mailid, @recipient, @content;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
BEGIN TRAN;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'Elections_Office',
@recipients = @recipient, -- 1 行分の受信者
@subject = N'Your Account Details',
@body = @content,
@body_format = 'TEXT'; -- 必要に応じて 'HTML'
UPDATE dbo.Notifications
SET IsSent = 1,
SentAt = SYSUTCDATETIME()
WHERE mailid = @mailid;
COMMIT TRAN;
END TRY
BEGIN CATCH
IF XACT_STATE() <> 0
ROLLBACK TRAN;
DECLARE @msg nvarchar(4000) = ERROR_MESSAGE();
RAISERROR(N'GetRegistrationInfo: メール送信に失敗しました。[mailid=%d] %s',
16, 1, @mailid, @msg);
END CATCH;
FETCH NEXT FROM cur_send INTO @mailid, @recipient, @content;
END
CLOSE cur_send;
DEALLOCATE cur_send;
END;
GO
この実装例では、通知作成と送信を同一プロシージャ内で行っていますが、ジョブなどで「通知作成」「送信処理」を分けても構いません。その場合も、送信処理側では同様のカーソル＋トランザクションの構成を維持するのがポイントです。
各パートの解説
通知作成部（集合指向 INSERT）のポイント
COALESCE(NULLIF(au.PersonalEmail, ''), au.WorkEmail)で「個人メールが空なら職場メール」という優先ロジックを実現- Employees と AuthorizedUsers の突き合わせを
NOT EXISTSで表現し、「まだ Employees にいない人だけ」を対象にしている - 本文は
CONCATとCHAR(13)+CHAR(10)の組み合わせで整形しており、改行コードが明示的
重要なのは、対象者の抽出から Notifications への INSERT までが完全にセットベースで書かれている点です。行ごとの処理は送信フェーズに限定することで、パフォーマンスとコードの見通しを両立できます。
送信ループ部（カーソル＋トランザクション）のポイント
LOCAL FAST_FORWARDカーソルで、前方走査のみの軽量カーソルにしている- SELECT に
WITH (UPDLOCK, READPAST)を付けることで、他のセッションと二重送信競合しないように制御 - 1 レコードごとに
BEGIN TRAN～COMMIT TRANを行い、sp_send_dbmail と UPDATE をひとまとめにしている
特に UPDLOCK, READPAST は、高頻度ジョブや並行実行時の二重送信を防ぐための現場テクニックです。ロック済みの行はスキップされるため、別セッションが同じ行を再送してしまうリスクを減らせます。
TRY…CATCH によるエラーハンドリング
メール送信は、ネットワークや SMTP サーバー側の事情で失敗することがあります。そのため、送信部分は必ず TRY…CATCH ブロックで囲み、
- 例外発生時はトランザクションを ROLLBACK
- どの
mailidの送信で失敗したかをログやエラーに残す
といった対応が必要です。上記サンプルでは RAISERROR で上位にエラーを投げていますが、別途「独自エラーログテーブル」を用意して INSERT してもよいでしょう。
平文パスワードのメール送信は避ける
サンプルでは便宜上 <TEMP_PASSWORD> としてパスワードを埋め込んでいますが、実運用では 平文パスワードをメールに載せる設計は避けるべきです。
- 一時パスワードと初回ログイン時の強制変更を組み合わせる
- パスワードではなく「パスワードリセット用のトークン付き URL」を送る
- トークンには有効期限とワンタイム性を持たせる
など、セキュリティ要件を満たした仕組みを別途設計することをおすすめします。
Database Mail 側のチェックポイント
アプリ側の実装が正しくても、Database Mail の設定周りでつまずいているケースも多々あります。代表的なチェックポイントを整理します。
Database Mail の有効化
Database Mail は、高度な構成オプション 'Database Mail XPs' を有効化する必要があります。
EXEC sp_configure 'show advanced options', 1;
RECONFIGURE;
EXEC sp_configure 'Database Mail XPs', 1;
RECONFIGURE;
これを実行したあと、SQL Server サービスの再起動が必要になる場合もあるため、環境の運用ルールに従って作業してください。
プロファイル・アカウント・権限の確認
Database Mail では「アカウント」と「プロファイル」が分かれており、
- アカウント：SMTP サーバーや認証情報を表す
- プロファイル：アカウントを束ねた論理的な送信元
という関係になっています。
また、sp_send_dbmail を実行するユーザーは、msdb データベースの DatabaseMailUserRole ロールに属している必要があります。
msdb.dbo.sysmail_help_profile_spでプロファイルの存在と設定を確認msdb.dbo.sysmail_help_account_spでアカウント設定（SMTP サーバーなど）を確認- 必要なログイン／ユーザーが
DatabaseMailUserRoleに属しているかを確認
テストメールでの動作確認
SSMS の「Database Mail」から GUI でテストメールを送るか、単純な sp_send_dbmail の呼び出しで動作確認を行います。
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'Elections_Office',
@recipients = N'[email protected]',
@subject = N'Test',
@body = N'Hello';
これが成功しない場合は、ストアドプロシージャ側の前に、Database Mail の設定・SMTP 経路に問題があると判断できます。
送信状況とエラーログの確認
Database Mail は msdb 内の以下のビューで状態を確認できます。
-- 送信キューおよび履歴
SELECT TOP (50) *
FROM msdb.dbo.sysmail_allitems
ORDER BY send_request_date DESC;
-- エラーログ
SELECT TOP (50) *
FROM msdb.dbo.sysmail_event_log
ORDER BY log_date DESC;
ここにエラーが記録されている場合、エラーメッセージに従って SMTP 設定やネットワーク設定を見直します。
動作確認チェックリスト（まとめ）
- シンプルな sp_send_dbmail テストが成功するか
- Database Mail プロファイル／アカウントが存在し、正しい SMTP 情報になっているか
- 実行ユーザーが msdb の DatabaseMailUserRole に所属しているか
- Notifications テーブルに想定通りの件数・データが INSERT されているか
IsSent = 0の行だけが送信対象になっているか- 送信後に
IsSent = 1、SentAtが正しく更新されているか - sysmail_allitems / sysmail_event_log にエラーが残っていないか
- 高頻度ジョブの場合、二重送信が発生していないか（READPAST の効き具合）
同一本文を複数宛先にまとめて送る場合（参考）
今回のように本文が「宛名入り」で個別化されている場合は 1人1通が必須ですが、例えば「システムメンテナンス告知」など本文が完全に共通でよいケースでは、SQL Server 2017 以降の STRING_AGG を使って複数宛先を 1 通にまとめることもできます。
DECLARE @recipients nvarchar(max);
SELECT @recipients = STRING_AGG(Email, ';')
FROM dbo.AuthorizedUsers
WHERE IsActive = 1;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = N'Elections_Office',
@recipients = @recipients,
@subject = N'【重要】メンテナンスのお知らせ',
@body = N'本日 21:00 よりシステムメンテナンスを実施します。';
ただし、
- 誰に送ったかの追跡が Notifications テーブル側ではできなくなる
- BCC を使わないと宛先が全員に丸見えになる
といったデメリットもあるため、個人情報や通知要件を踏まえて慎重に採用してください。
現場でよくあるアンチパターンと改善策
アンチパターン 1：アプリから直接 sp_send_dbmail を多用する
業務アプリケーションから直接 sp_send_dbmail を多用すると、アプリのトランザクションとメール送信が不必要に結びつき、例外処理が複雑になりがちです。
改善策としては、
- アプリからは「通知キュー（Notifications 等）への INSERT だけ」を行う
- SQL Agent ジョブで定期的に「キューを読んで送信」するバッチを回す
という二段構えにすることで、アプリのレスポンスとメール送信を疎結合にできます。
アンチパターン 2：大量のメールを 1 トランザクションでまとめて送る
数千件のメール送信を 1 トランザクションにまとめてしまうと、途中で 1 件失敗しただけで全体がロールバックされ、送信履歴も残らない最悪の状況になります。
改善策としては、
- 本記事のサンプルのように「1 件ごとにトランザクション」を切る
- どうしてもまとめたい場合でも、数十件単位など適切な「バッチサイズ」を設ける
といった設計が現実的です。
アンチパターン 3：Database Mail のログを見ていない
「メールが届かない」とき、アプリ側のログばかり眺めていて、Database Mail の sysmail_allitems / sysmail_event_log を確認していないケースもよくあります。
sp_send_dbmail は「キュー投入まで」は成功しているのに、SMTP 側で弾かれているケースも多いため、必ず msdb 側のログも併せて確認する運用を徹底しましょう。
まとめ
sp_send_dbmail でメールが送信されない／想定通りに送られない原因の多くは、
- 受信者アドレス変数の扱いミス（NULL 連結・全件連結）
- カーソル設計の不備（FETCH した値を使わず別 SELECT で上書き）
- 送信と IsSent 更新を別々に実行していることによる不整合
- 通知作成をカーソルで行うなど、セットベースで書ける部分まで逐次処理にしていること
といった、実装上のアンチパターンに起因します。
これらを避けるためには、
- 通知の作成は集合指向で一括 INSERT する
- 送信は 1 レコード＝1 通の前提でカーソル処理し、sp_send_dbmail に渡す
- 送信とフラグ更新を同一トランザクションで扱う
- Database Mail の設定・権限・ログを必ず確認する
- 平文パスワードをメールで送らないなど、セキュリティ面も考慮する
といった基本方針を徹底することが重要です。
既存のストアドプロシージャでトラブルが起きている場合は、まず Notifications テーブルの設計とカーソル周りのコードを見直し、本記事のサンプルのような構成に組み替えてみてください。それだけで、「メールが届かない」「一部だけ送れない」といった問題が大きく減るはずです。
コメント