Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Xml Query Expand / Collapse
Author
Message
Posted Wednesday, February 5, 2014 7:42 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Yesterday @ 2:09 AM
Points: 8, Visits: 393
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



Post #1538183
Posted Sunday, March 23, 2014 12:43 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 12:54 PM
Points: 2,008, Visits: 5,482
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
Post #1553797
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse