SQL Server代理作业中sp_send_dbmail查询未完全执行问题求助

我有如下SQL语句，直接在查询窗口执行时能正确返回结果并发送邮件：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Server Agent Notification',  
    @recipients = '<EMAIL ADDRESS>',  
    @body = 'Please see INT overflow candidates attached',
    @query = '
        declare @sql nvarchar(2000)=N''
        IF ''''?'''' LIKE ''''PROD_%''''
        BEGIN
            USE [?];
            SELECT
                DB_NAME()                                                         AS DB
               ,OBJECT_SCHEMA_NAME(object_id)                                     AS SchemaName
               ,OBJECT_NAME(object_id)                                            AS TableName
               ,name                                                              AS ColumnName
               ,TYPE_NAME(system_type_id)                                         AS ColumnType
               ,CAST(Seed_Value AS BIGINT)                                        AS Seed_Value
               ,CAST(Increment_Value AS BIGINT)                                   AS Increment_Value
               ,POWER(2.0, (max_length * 8 - 1))                                  AS MaxSize
               ,CAST(Last_value AS BIGINT)                                        AS Last_value
               ,CAST(Last_value AS BIGINT) / POWER(2.0, (max_length * 8 - 1))     AS ratio
            FROM sys.identity_columns
                WHERE TYPE_NAME(system_type_id) = ''''int'''';
        END''

        DECLARE @ratios TABLE (
            DB NVARCHAR(100),
            SchemaName NVARCHAR(29),
            TableName NVARCHAR(100),
            ColumnName NVARCHAR(100),
            ColumnType NVARCHAR(20),
            Seed_Value BIGINT,
            Increment_Value BIGINT,
            MaxSize BIGINT,
            Last_value BIGINT,
            ratio FLOAT);

        INSERT INTO @ratios EXEC sp_MSforeachdb @sql

        SELECT
            *
        FROM @ratios
        WHERE ColumnType = ''int''
            AND DB LIKE ''PROD_%''
            AND ratio >= 0.95',
    @execute_query_database = 'master',
    @subject = 'INT Overflow candidates',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'OverflowCandidates.csv',
    @query_result_header = 1,
    @query_result_width = 512,
    @query_result_separator = ';',
    @exclude_query_output = 1,
    @append_query_error = 1,
    @query_no_truncate = 0,
    @query_result_no_padding = 1;

执行结果：

(6546 rows affected)
DB                                                                                                   SchemaName                    TableName                                                                                            ColumnName               
                                                                             ColumnType           Seed_Value           Increment_Value      MaxSize              Last_value           ratio                   
---------------------------------------------------------------------------------------------------- ----------------------------- ---------------------------------------------------------------------------------------------------- ------------------------
---------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------
PROD_XXX                                                                                       dbo                           CPM#Balance_BAK_DBA_20220527                                                                         BalanceID               
                                                                             int                                     1                    1           2147483648           2147254332      0.99989321641623974
PROD_XXX                                                                                       dbo                           CPM#Balance_BAK_20220527_SE                                                                          BalanceID               
                                                                             int                                     1                    1           2147483648           2147254332      0.99989321641623974

(2 rows affected)

我希望每周定期执行该语句，于是创建了对应的SQL Server代理作业。虽然能收到邮件，但查询似乎未完全执行，返回结果如下：

(0 rows affected)
DB                                                                                                   SchemaName                    TableName                                                                                            ColumnName               
                                                                             ColumnType           Seed_Value           Increment_Value      MaxSize              Last_value           ratio                   
---------------------------------------------------------------------------------------------------- ----------------------------- ---------------------------------------------------------------------------------------------------- ------------------------
---------------------------------------------------------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- ------------------------

(0 rows affected)

请问该问题的原因是什么，以及如何解决？

核心原因 #

1. 权限差异：SSMS执行时用的是当前登录用户的凭据（拥有PROD_%数据库访问权限），而SQL Server代理作业使用的是代理服务账号，该账号大概率没有访问这些生产库的权限，导致无法读取sys.identity_columns数据，最终返回空结果。
2. sp_MSforeachdb的隐性缺陷：这个系统存储过程是未公开的，遇到无权限访问的数据库时会静默跳过，不会抛出错误，直接导致结果集为空，很难排查。

解决步骤 #

1. 检查并配置代理服务账号权限 #

• 给代理服务账号授予所有PROD_%数据库的读取权限：可以添加db_datareader角色，或者更精细地授予SELECT ON sys.identity_columns权限。
• 确认该账号在master库中有执行sp_MSforeachdb的权限（默认通常已有，但需验证）。

2. 替换sp_MSforeachdb为显式游标（推荐方案） #

由于sp_MSforeachdb的不确定性，改用显式游标遍历目标数据库，同时加入错误捕获，方便排查问题。修改后的完整SQL如下：

EXEC msdb.dbo.sp_send_dbmail
    @profile_name = 'SQL Server Agent Notification',  
    @recipients = '<EMAIL ADDRESS>',  
    @body = '请查看附件中的INT溢出候选对象',
    @query = '
        DECLARE @ratios TABLE (
            DB NVARCHAR(100),
            SchemaName NVARCHAR(29),
            TableName NVARCHAR(100),
            ColumnName NVARCHAR(100),
            ColumnType NVARCHAR(20),
            Seed_Value BIGINT,
            Increment_Value BIGINT,
            MaxSize BIGINT,
            Last_value BIGINT,
            ratio FLOAT);

        DECLARE @dbName NVARCHAR(100)
        DECLARE dbCursor CURSOR FOR
            SELECT name FROM sys.databases WHERE name LIKE ''PROD_%'' AND state = 0 -- 仅遍历在线数据库

        OPEN dbCursor
        FETCH NEXT FROM dbCursor INTO @dbName

        WHILE @@FETCH_STATUS = 0
        BEGIN
            BEGIN TRY
                DECLARE @sql NVARCHAR(MAX) = N''
                    USE ['' + @dbName + N''];
                    SELECT
                        DB_NAME()                                                         AS DB
                       ,OBJECT_SCHEMA_NAME(object_id)                                     AS SchemaName
                       ,OBJECT_NAME(object_id)                                            AS TableName
                       ,name                                                              AS ColumnName
                       ,TYPE_NAME(system_type_id)                                         AS ColumnType
                       ,CAST(Seed_Value AS BIGINT)                                        AS Seed_Value
                       ,CAST(Increment_Value AS BIGINT)                                   AS Increment_Value
                       ,POWER(2.0, (max_length * 8 - 1))                                  AS MaxSize
                       ,CAST(Last_value AS BIGINT)                                        AS Last_value
                       ,CAST(Last_value AS BIGINT) / POWER(2.0, (max_length * 8 - 1))     AS ratio
                    FROM sys.identity_columns
                    WHERE TYPE_NAME(system_type_id) = ''''int'''';''
                
                INSERT INTO @ratios EXEC sp_executesql @sql
            END TRY
            BEGIN CATCH
                -- 可选：捕获错误并记录，便于排查权限问题
                INSERT INTO @ratios (DB, ColumnType)
                VALUES (@dbName, ''ERROR: '' + ERROR_MESSAGE())
            END CATCH

            FETCH NEXT FROM dbCursor INTO @dbName
        END

        CLOSE dbCursor
        DEALLOCATE dbCursor

        SELECT
            *
        FROM @ratios
        WHERE ColumnType = ''int''
            AND ratio >= 0.95',
    @execute_query_database = 'master',
    @subject = 'INT溢出候选对象',
    @attach_query_result_as_file = 1,
    @query_attachment_filename = 'OverflowCandidates.csv',
    @query_result_header = 1,
    @query_result_width = 512,
    @query_result_separator = ';',
    @exclude_query_output = 1,
    @append_query_error = 1,
    @query_no_truncate = 0,
    @query_result_no_padding = 1;

3. 测试作业执行 #

修改后重新配置代理作业，执行后查看邮件附件：

• 如果仍返回0行，检查结果集中是否有错误记录（ColumnType列会显示错误信息），根据提示调整权限。
• 如果返回预期结果，说明问题已解决。

内容的提问来源于stack exchange，提问作者scarabeaus