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
Have you tried to set up ‘ @query_result_no_padding ‘ also?