\nI've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However, you might be one of the early adopters who made up their own scripts to define extended event sessions, and use hardcoded scripts to harvest the results. So, you may run into this problem if you're using what I call "lazy XML" in the event harvesting script. Take, as an example, an extended event session defined as follows with 3 actions:\n<\/p>\n
\ncreate event session errorsession on server
\nadd event sqlserver.error_reported
\n(
\naction
\n(
\npackage0.callstack,
\nsqlserver.session_id,
\nsqlserver.sql_text
\n)
\nwhere error = 547 and package0.counter <= 3
\n)
\nadd target package0.ring_buffer
\nwith (max_dispatch_latency = 1 seconds)
\ngo\n<\/p>\n
\nIn previous versions, you could pretty much depend on the XML presenting the actions in order. So the following XML would return a subset of the event information in rows and columns.\n<\/p>\n
\nSELECT \nThe XML above is based on the assumption that action 2 is the sqlserver.session_id and action 3 is the sqlserver.sql_text, because it doesn't check names, just blindly uses the ordinal number in XPath. It makes the XML easier to write and a bit faster to execute, but its "lazy" XML. This order of actions was a valid assumption in SQL Server 2008; its not valid in SQL Server 2012. The data fragment containing the action data appears like this:\n<\/p>\n \n <action name="sql_text" package="sqlserver"> \nSo now, sql_text (the third action defined) is the first action presented. The fragile harvesting script will break. So, to be one the safer side, if you have any such scripts change them to actually look for the element for want using a named XPath predicate, like this:\n<\/p>\n \nSELECT \nNote that it's probably better to do this with the "data" items too (ErrorNumber in the query above), although you may be a little safer with these, as they have a defined schema per-event. But BOL does point out that events have a "versioned" schema. The actions can be defined in any order, so make your scripts more robust. Use XPath predicates. And if you're not on SQL Server 2012 yet, change your scripts now.\n<\/p>\n \n@bobbeauch<\/p>\n","protected":false},"excerpt":{"rendered":" I've said before how thrilled I am will the new extended event UI in SSMS for SQL Server 2012. However, you might be one of the early adopters who made up their own scripts to define extended event sessions, and use hardcoded scripts to harvest the results. So, you may run into this problem if […]<\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[15,31,37],"tags":[],"class_list":["post-501","post","type-post","status-publish","format-standard","hentry","category-extended-events","category-sql-server-2012","category-sql-server-xml"],"yoast_head":"\n
\n Data2.Results.value ('(data\/.)[1]<\/strong>', 'int') AS ErrorNumber,
\n Data2.Results.value ('(action\/.)[2]<\/strong>', 'int') AS Session,
\n Data2.Results.value ('(action\/.)[3]<\/strong>', 'nvarchar(max)') AS SQLStatement
\nfrom
\n(
\nselect CAST(xet.target_data as xml) as data
\nfrom sys.dm_xe_session_targets xet
\njoin sys.dm_xe_sessions xe
\non (xe.address = xet.event_session_address)
\nwhere xe.name = 'errorsession') events
\ncross apply Data.nodes ('\/\/RingBufferTarget\/event')
\n AS Data2 (Results)\n<\/p>\n
\n <type name="unicode_string" package="package0" \/>
\n <value>
\n DELETE pubs.dbo.jobs
\n <\/value>
\n <\/action>
\n <action name="session_id" package="sqlserver">
\n <type name="uint16" package="package0" \/>
\n <value>53<\/value>
\n <\/action>
\n <action name="callstack" package="package0">
\n <type name="callstack" package="package0" \/>
\n <value>..callstack elided…<\/value>
\n <\/action>\n<\/p>\n
\n Data2.Results.value ('(data[@name="error_number"]\/.)[1]<\/strong>', 'int') AS ErrorNumber,
\n Data2.Results.value ('(action[@name="session_id"]\/.)[1]<\/strong>', 'int') AS Session,
\n Data2.Results.value ('(action[@name="sql_text"]\/.)[1]<\/strong>', 'nvarchar(max)') AS SQLStatement
\nfrom
\n(
\nselect CAST(xet.target_data as xml) as data
\nfrom sys.dm_xe_session_targets xet
\njoin sys.dm_xe_sessions xe
\non (xe.address = xet.event_session_address)
\nwhere xe.name = 'errorsession') events
\ncross apply Data.nodes ('\/\/RingBufferTarget\/event')
\n AS Data2 (Results);\n<\/p>\n