0

I set up an Extended Events session to capture SQL Server error events.

Below is the TSQL I used to create the XE session:

CREATE EVENT SESSION [ErrorCapture] ON SERVER 
ADD EVENT sqlserver.error_reported(
    ACTION(sqlserver.client_hostname
    ,sqlserver.database_id
    ,sqlserver.sql_text
    ,sqlserver.username)
    WHERE ([severity]>=(11)))
ADD TARGET package0.event_file(SET filename=N'D:\ErrorCapture.xel')
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

Using SSMS, I am able to view all of the available event data, including sql_text. However, when using TSQL to query the event file, data for the sql_text action shows up as NULL. Below is the query used. Any thoughts on why this might be?

with ErrorCaptureEventData as
(
  select convert(xml, event_data) as EventData
  from sys.fn_xe_file_target_read_file(N'D:\ErrorCapture*.xel', NULL, NULL, NULL)
) 
select EventData.value('(event/@timestamp)[1]', 'datetime2(7)') as TimeStamp
       ,EventData.value('(event/data[@name="error_number"]/value)[1]', 'int') AS [Error_Number]
       ,EventData.value('(event/data[@name="message"]/value)[1]', N'nvarchar(max)') AS [Error_Message]
       ,EventData.value('(event/data[@name="sql_text"]/value)[1]', N'nvarchar(max)') AS SQL_Text
       ,EventData.value('(event/data[@name="statement"]/value)[1]', N'nvarchar(max)') AS SQL_Statement
       ,EventData.value('(event/data[@name="severity"]/value)[1]', N'nvarchar(max)') AS Severity
       ,DB_NAME(EventData.value('(event/action[@name="database_id"]/value)[1]', N'nvarchar(max)')) AS [DB_Name]
       ,EventData.value('(event/action[@name="username"]/value)[1]', N'nvarchar(max)') AS [User_Name]
 from ErrorCaptureEventData
 order by timestamp desc
4

You're parsing the XML wrong. Since sql_text is an action, you need to do this:

value('(action[@name="sql_text"]/value)[1]', 'NVARCHAR(MAX)')

2
  • Thanks, Erik! That solved my issue. Changed that select item as below: – Michael Brule Jan 13 at 17:36
  • EventData.value('(event/action[@name="sql_text"]/value)[1]', N'nvarchar(max)') AS SQL_Text – Michael Brule Jan 13 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.