如何在SSMS Job中实现空表检测：发邮件并终止执行？

一、完善检测脚本 #

先把你写的动态SQL逻辑补全，实现执行查询、检测空表、发邮件并终止作业的完整逻辑，脚本如下：

-- 1. 生成目标表的计数查询语句
DECLARE @Result AS TABLE(Query varchar(max));
DECLARE @ExecuteQuery NVARCHAR(MAX);
DECLARE @CheckResults TABLE(TableName NVARCHAR(128), [Count] INT);

INSERT INTO @Result
SELECT CONCAT('select ''', TABLE_NAME, ''' as TableName, count(*) as Count from ', TABLE_NAME, ' where Executionid like ''%', CAST(GETDATE() AS DATE), 'T%''') 
FROM information_schema.tables 
WHERE TABLE_NAME NOT IN ('UPSalesAgreementLineStaging','UpLedgerTransV3Staging','AzureSQLMaintenanceLog','database_firewall_rules');

-- 2. 执行所有生成的查询，收集各表数据量
DECLARE QueryCursor CURSOR FOR SELECT Query FROM @Result;
OPEN QueryCursor;
FETCH NEXT FROM QueryCursor INTO @ExecuteQuery;

WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT INTO @CheckResults
    EXEC sp_executesql @ExecuteQuery;
    FETCH NEXT FROM QueryCursor INTO @ExecuteQuery;
END
CLOSE QueryCursor;
DEALLOCATE QueryCursor;

-- 3. 检测空表并执行告警+终止逻辑
IF EXISTS(SELECT 1 FROM @CheckResults WHERE [Count] = 0)
BEGIN
    -- 构造告警邮件内容
    DECLARE @MailBody NVARCHAR(MAX);
    SET @MailBody = '以下源表今日无数据，请排查：' + CHAR(13) + CHAR(10);
    SELECT @MailBody = @MailBody + '- ' + TableName + CHAR(13) + CHAR(10) FROM @CheckResults WHERE [Count] = 0;

    -- 发送告警邮件（替换为你的实际邮件配置）
    EXEC msdb.dbo.sp_send_dbmail
        @profile_name = '你的数据库邮件配置文件名',
        @recipients = '告警接收邮箱@xxx.com',
        @subject = 'Azure源表空表告警',
        @body = @MailBody;

    -- 抛出错误终止作业（错误级别16会让步骤失败，触发作业终止）
    THROW 50001, '检测到空表，作业已终止', 1;
END

二、配置SSMS作业步骤 #

1. 打开SSMS，展开「SQL Server代理」→「作业」，找到你的复制作业
2. 右键作业→「属性」→「步骤」→「新建」，创建检测步骤：
   • 步骤名称：比如「检测Azure源表是否为空」
   • 类型：Transact-SQL脚本(TSQL)
   • 数据库：选择你要检测的Azure关联数据库
   • 命令：粘贴上面的脚本，替换@profile_name和@recipients为你的实际配置
3. 调整步骤顺序：把这个检测步骤移到复制数据的步骤之前，确保先检测再执行复制
4. 设置失败行为：在「步骤属性」→「高级」里，将「失败时的操作」设为「退出报告失败」，保证空表检测失败后直接终止整个作业

三、前置准备（若未配置） #

如果还没配置数据库邮件，先在SSMS里操作：

• 展开「管理」→「数据库邮件」，按向导创建邮件配置文件和发送账户，测试确保能正常发邮件

内容的提问来源于stack exchange，提问作者Salah K.