Microsoft SQL Server - SQL Server Agent with scheduled email using the - msdb.dbo.sp_send_dbmail - stored procedure truncates body of email RRS feed

  • Question

  • I have the below T-SQL script in SQL Server Agent, and it is scheduled to run at 7 AM and 7 PM everyday.

    DECLARE @Report_output nvarchar(4000);
    DECLARE @HourLapse_output real;
    DECLARE @ServerName_output nvarchar(50);
    DECLARE @Subject_output nvarchar(4000);
    EXEC dbo.usp_ABC
    @Report = @Report_output OUTPUT,
    @HourLapse = @HourLapse_output OUTPUT,
    @ServerName = @ServerName_output OUTPUT;
    SET @Subject_output = CONCAT('ABC Notification: ........Log table in the ', @ServerName_output, ' 
    server loaded with .....');
    EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = '',  
    @recipients = '',
    @body_format = 'HTML',
    @body = @Report_output,  
    @subject = @Subject_output; `

    I run a SQL Server stored procedure called dbo.usp_ABC with output parameters; I capture those output parameters using variables, and feed the variables into the msdb.dbo.sp_send_dbmail system stored procedure.

    The stored procedure dbo.usp_ABC executes perfectly. The system stored procedure - msdb.dbo.sp_send_dbmail - ALSO captures the @Report_output variable in its body CORRECTLY.

    When I run the stored procedure msdb.dbo.sp_send_dbmail outside of the SQL Server Agent, I am getting the entire body in the email without any truncation.

    However, the issue seems to with the SQL Server Agent, on how it takes the body of the email. The body is truncated to about 262-odd characters, when I receive the email via SQL Server Agent. The last 15-odd characters in the body are truncated.

    I need the entire body in the email. Any suggestion ?

    (The subject of the email is fine though)

    • Edited by sph1777 Friday, December 20, 2019 3:45 PM
    Thursday, December 19, 2019 8:02 PM

All replies

  • Are you having the same issue if you change the @body_format = 'TEXT'? 
    What do you get when you run it from SSMS?

    Thursday, December 19, 2019 8:32 PM
  • Ok, just read your answer now; will try and get back

    • Edited by sph1777 Thursday, December 19, 2019 9:34 PM
    Thursday, December 19, 2019 9:32 PM
  • It works fine in SSMS when I use TEXT (instead of HTML).

    I get the full body without truncation.

    I will test in SQL Server Agent too and get back.

    • Edited by sph1777 Thursday, December 19, 2019 10:06 PM
    Thursday, December 19, 2019 9:56 PM
  • Just tried in SQL Server Agent schedule. The problem persists. The email body gets truncated to around 262-odd characters, even after I changed the body format to TEXT (instead of HTML).

    The stand-alone system stored procedure - msdb.dbo.sp_send_dbmail  - works fine, no truncation in email body, when the body format is TEXT (the stored procedure alone was successful earlier too, when the body format was HTML)

    So some inherent problems with SQL Server Agent, when the body of the email exceeds a particular length, am I right ? This problem is regardless of the body format of the email, be it TEXT or HTML.

    Any work-around for this ?

    • Edited by sph1777 Friday, December 20, 2019 3:47 PM
    Friday, December 20, 2019 3:41 PM
  • Any suggestion ? It has been a few days since I posted this !
    Tuesday, December 24, 2019 4:11 AM