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 = 'noreply@abc.net',
@recipients = 'abc@abc.net',
@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 problem persists even if I change the @body_format to TEXT, instead of HTML.
(The subject of the email is fine always)
