SQL Server Performance Forum – Threads Archive

Getting weird result when sending @query alert

Hi guys, I am trying to send an email if full backup MP failed. I included the following script in the job step that alert me when the MP fails. unfortunatly I get weird things (like couple of lines with dashes) in the email + the format of the email does not look that good. I want to get an email that is readable. I also included one of the emails I receved below. any help and/or suggestions will be appreciated.
Job step that get triggered when MP failes: EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘[email protected]_Domain.com’,
@query=’select line3 as "Databases",Error_message from msdb.dbo.sysmaintplan_logdetail where succeeded = 0 AND datediff(dd,msdb.dbo.sysmaintplan_logdetail.start_time,getdate())=0′,
@body = ‘The backup Job failed in server SERVERNAME’,
@subject = ‘****SQL Server Message****** Backup Job failure******’,
@query_result_header =1,
@query_result_width = 250 ; The email I receive looks exactly like: The backup Job failed in server SERVERNAME
Databases
Error_message ———————————————————————————————————————————————————————————————————————————————————-
—— —————————————————————————————————————————————————————————————————————————————————
————-
Databases: master,model,msdb
Executing the query "EXECUTE master.dbo.xp_create_subdir N’Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup2\master’
" failed with the following error: "Error executing extended stored procedure: Invalid Parameter". Possible failure reasons: P (1 rows affected)

This is what you get if you included select statment. it will return same thing as you ran a Select statment. What you can do is
format a body the way you want and include it in the @body parameter
http://www.sql-server-performance.com/da_email_functionality.asp —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

Thanks dineshasanka. How can I include a query in the @body parameter?

get the query data into variables and format them the way you want and incldue that to query parameter —————————————-
Contributing Editor, Writer & Forums Moderator
http://www.SQL-Server-Performance.Com Visit my Blog at
http://dineshasanka.spaces.live.com/

I did the following but still receive almost same result declare @Q varchar(200) set @Q= ‘select line3 as "Databases",Error_message from msdb.dbo.sysmaintplan_logdetail where succeeded = 0 AND datediff(dd,msdb.dbo.sysmaintplan_logdetail.start_time,getdate())=0’
EXEC msdb.dbo.sp_send_dbmail
@recipients = [email protected]‘,
@[email protected],
@body = ‘The backup Job failed in server SERVERNAME’,
@body_format=’html’,
@subject = ‘****SQL Server Message****** Backup Job failure******’,
@query_result_header =1,
@query_result_width = 250 ;

You need to change the size of the columns…Line3 is nVarchar(512) so change it as per your needs and Errormessage is nVarchar(max) so change it you needed…
select Convert(Varchar(50), line3) as "Databases",
Convert(Varchar(500), Error_message ) As Errormessage
from msdb.dbo.sysmaintplan_logdetail
where succeeded = 0
AND datediff(dd,msdb.dbo.sysmaintplan_logdetail.start_time,getdate())=0′ MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

Thanks Mohammed.
I tried changing the size of columns but the problem is that when I decrease the column size, the dashes get decreased and the characters within the result set in the email get decreased as well. and when I increase the column size, dashes and result set of column increase too. I want to get rid of those dashes without changing the result set in the email. — this is what I put: declare @Q varchar(250) set @Q= ‘select Convert(Varchar(50), line3) as "Databases",
Convert(Varchar(300), Error_message ) As Errormessage
from msdb.dbo.sysmaintplan_logdetail where succeeded = 0 AND datediff(dd,msdb.dbo.sysmaintplan_logdetail.start_time,getdate())=0′
EXEC msdb.dbo.sp_send_dbmail
@recipients = ‘[email protected]‘,
@[email protected], –‘select line3 as "Databases",Error_message from msdb.dbo.sysmaintplan_logdetail where succeeded = 0 AND datediff(dd,msdb.dbo.sysmaintplan_logdetail.start_time,getdate())=0’,
@exclude_query_output =1,
@query_result_header =1,
@query_result_width = 250,
@body = ‘The backup Job failed in server SERVERNAME’,
@body_format=’HTML’,
@subject = ‘****SQL Server Message****** Backup Job failure******’;

I don’t think it can be like this can done exactly what you are looking for but you can send the results as an attachement or body with HTML format….. And also check the following options… [ @query_result_header = ] query_result_header
Specifies whether the query results include column headers. The query_result_header value is of type bit. When the value is 1, query results contain column headers. When the value is 0, query results do not include column headers. This parameter defaults to 1. This parameter is only applicable if @query is specified. [ @query_result_width = ] query_result_width
Is the line width, in characters, to use for formatting the results of the query. The query_result_width is of type int, with a default of 256. The value provided must be between 10 and 32767. This parameter is only applicable if @query is specified. [ @query_result_separator = ] ‘query_result_separator’
Is the character used to separate columns in the query output. The separator is of type char(1). Defaults to ‘ ‘ (space). MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided â€œAS ISâ€ with no warranties for accuracy.

]]>