Query not completely executed when used in DB job

Question

I have the following statement, that when executed directly in an query window sends me the correct result as email

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;

Result:

(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)

Now I wanted this to be executed on a regular basis (once a week). When I set up the corresponding Server Agent Job I do get an Email, but it seems that the query is not fully executed.

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

(0 rows affected)

Does someone have any ideas why or how I can solve this?

Answer 1

I've now found a solution, but not with the SQL Server Agent. I've created a Scheduled Task and a Powershell script to execute the query. Now I get the mail with the correct result.

Answer 2

Varchars for querys run from Agent are limited in size. So your dynamic code (the parameter @query) is being truncated. Solution: preface your code with SET TEXTSIZE nnnn. Read more here: https://www.sqlservercentral.com/forums/topic/job-truncates-varcharmax-to-512-characters-run-script-from-ssms-does-not