Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I'm capturing extended events in SQL server. Trying to analyse the trace - converting the event data to xml an error comes up:

Msg 9420, Level 16, State 1, Line 28
XML parsing: line 1, character 731, illegal xml character

Investigating the issue, I find that there is a character of ascii number 3 in the sql_text action value. This value is passed by a client in form of dynamic SQL. This is of little concern here. The problem is that XE cannot handle weird characters in sql_text.

This is how I get to the data causing the trouble:

declare @data nvarchar(max)
declare @id int
SET NOCOUNT ON

if object_id('tempdb.dbo.#xerrors') is null --drop table #xerrors
begin
    select CAST(null as XML) as xml_event_data, * 
    into #xerrors
    from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet', 
                '...\Log\error_*.xem', 
                null, 
                null
            ) 

    alter table #xerrors
    add id int identity(1,1) primary key not null

end

while exists(select 1 from #xerrors where (@id is null or ID > @id) and xml_event_data is null)
begin
    select top 1 @id = id, @data = event_data from #xerrors where (@id is null or ID > @id) and xml_event_data is null order by id

    print @id

    update #xerrors set xml_event_data = CONVERT(xml, event_data) where id = @id

end

select ascii(SUBSTRING(event_data, 731, 1)), event_data from #xerrors where id = 11253

And my session setup is:

-- TRIES TO ELIMINATE PREVIOUS SESSIONS
if exists(select 1 from sys.dm_xe_sessions xes where xes.name = 'error_capture')
      DROP EVENT SESSION error_capture ON SERVER
GO


-- CREATES THE SESSION
CREATE EVENT SESSION error_capture ON SERVER
ADD EVENT sqlserver.error_reported
(
    ACTION (
        sqlserver.tsql_stack, 
        sqlserver.sql_text,
        sqlserver.client_hostname,
        sqlserver.client_pid,
        sqlserver.client_app_name,
        sqlserver.is_system,
        sqlserver.nt_username,
        sqlserver.username,
        sqlserver.transaction_id,
        sqlserver.session_id,
        sqlserver.database_id,
        sqlserver.database_context
    )
    --actions
    WHERE Severity > 10
)
ADD TARGET package0.asynchronous_file_target
-- CONFIGURES THE FILE TARGET
(set filename = '...\Log\error.xet' , metadatafile = '...\Log\error.xem')
GO

-- STARTS THE SESSION
ALTER EVENT SESSION error_capture ON SERVER STATE = START

Do you consider this a bug in the XE framework or am I doing something wrong? I believe all events should be valid XML...

The file paths have been intentionaly shortened

share|improve this question
    
Nice article ..While running a script to find out the trouble data, getting an error Msg 102, Level 15, State 1, Line 28 Incorrect syntax near '@id'. Any suggestions on how to fix it ? –  user22918 Apr 23 '13 at 16:13
    
Hi @sqldba, I just tried the code in SQL2008 express without the error you mention. Which is your SQL version? Copy-paste issue perhaps? –  Robert Ševčík - Robajz Apr 28 '13 at 15:19

1 Answer 1

up vote 1 down vote accepted

Since there are no takers yet, I offer a guesswork answer: Yes, I think it's a bug.

Workaround: replace the illegal character once you know which one it is before converting the data to XML like this:

select xml_event_data = CONVERT(xml, replace(event_data,CHAR(3),'')), *
from sys.fn_xe_file_target_read_file
            (
                '...\Log\error_*.xet', 
                '...\Log\error_*.xem', 
                null, 
                null
            ) 
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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