February 5, 2014 at 7:42 am
Hi,
I would like to send an email to a customer with deadlock information.
I did set up an event notification to capture Deadlock information and I do write it to a table in XML format (service broker)
Now I would like to send an email with the information of the deadlock.
I could use a query wit a lot of unions, but I'm sure there is a far better way to do this.
Could someone point me in the right direction?
My current solution:
declare @m nvarchar(max)
set @m =
N'<H2>Deadlock notification</H2>' +
N'<table border = "1">' +
N'<tr><th>LoginName</th><th>dbname</th><th>isolation</th><th>clientapp</th><th>hostname</th><th>Statement</th></tr>' +
CAST((
select
td = LoginName,''
,td = dbname,''
,td = isolation,''
,td = clientapp,''
,td = hostname,''
,td = Statement,''
from
(select
xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[1]','varchar(max)') as LoginName
,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[1]','varchar(max)')) as dbname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[1]','varchar(max)') as Isolation
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[1]','varchar(max)') as clientapp
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[1]','varchar(max)') as hostname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[1]','varchar(max)') as Statement
from dbo.tbl_deadlocknotification
union all
select
xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[2]','varchar(max)') as LoginName
,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[2]','varchar(max)')) as dbname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[2]','varchar(max)') as Isolation
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[2]','varchar(max)') as clientapp
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[2]','varchar(max)') as hostname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[2]','varchar(max)') as Statement
from dbo.tbl_deadlocknotification
union all
select
xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[3]','varchar(max)') as LoginName
,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[3]','varchar(max)')) as dbname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[3]','varchar(max)') as Isolation
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[3]','varchar(max)') as clientapp
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[3]','varchar(max)') as hostname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[3]','varchar(max)') as Statement
from dbo.tbl_deadlocknotification
union all
select
xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@loginname)[4]','varchar(max)') as LoginName
,db_name(xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@currentdb)[4]','varchar(max)')) as dbname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@isolationlevel)[4]','varchar(max)') as Isolation
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@clientapp)[4]','varchar(max)') as clientapp
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/@hostname)[4]','varchar(max)') as hostname
,xml_message_body.value('(/EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process/inputbuf)[4]','varchar(max)') as Statement
from dbo.tbl_deadlocknotification) as tbl_to_format
where LoginName <> statement
for XML path('tr'),Type ) AS NVARCHAR(MAX) ) + N'</table>';
exec msdb.dbo.sp_send_dbmail
@recipients = @recipients
,@subject = 'Deadlock info'
,@body = @m
,@body_format = 'HTML'
Greetings,
StriX
March 23, 2014 at 12:43 am
A better way is to use the nodes method
http://technet.microsoft.com/en-us/library/ms188282.aspx
A good article on this: Ad-Hoc XML File Querying by Seth Delconte
https://www.simple-talk.com/content/article.aspx?article=1756
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply