sp_send_dbmail XML attachment line break issue

I was initially working on creating an event notification that sends me an email alert for our dba team making it work under in a tsql procedure

so once they receive email alert they can open dead lock graph directly from the email account only if ssms is installed on the machine

 

First I tried some thing like this below then I noticed


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1

Then I realized that output text is getting truncated to 256 charecters then I used @query_no_truncate = 1 to avoid the truncation


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1
,@query_no_truncate = 1

Once I opened the xml attachment file I noticed that there are some weird line breaks for about every 256 characters then I realized that I might need to use @query_result_width with default of 256 with max of 32767


EXEC msdb.dbo.sp_send_dbmail @recipients = ‘sqljunkieshare@gmail.com’
,@subject = ‘test’ — Subject defined above
,@body = ‘test’
,@query_result_header =0
,@query_attachment_filename=‘tst.xdl’
,@query = ‘SET NOCOUNT ON select convert(NVARCHAR(MAX),deadlock_graph) from testdb.dbo.DeadlocksReports where deadlock_id = 20’
, @attach_query_result_as_file = 1
,@query_result_width = 32767
,@query_no_truncate = 1

seems to work as my xml output doesnt seem to exceed 32767 characters

 

dead lock graph email attachment

 

actual dead lock graph

Advertisements

One thought on “sp_send_dbmail XML attachment line break issue

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s