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 use extended events to store database errors, like this:

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.asynchronous_file_target
(
    SET filename='J:\ServerXmlOutput\ErrorCapture.xel'
)
WITH 
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

This works. I have some users called test1, test2 and so on that work interactively, and produce several errors. I don't want to log these errors. I tried to change the WHERE clause like this:

WHERE 
(
    [severity] >= (11) AND sqlserver.username NOT LIKE 'test%'
)

I get these errors:

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'LIKE'.
Msg 102, Level 15, State 1, Line 17
Incorrect syntax near 'TARGET'.
Msg 319, Level 15, State 1, Line 21
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

How can I exclude the errors from some users from my logging?

share|improve this question
    
Actually, it works if I write: ... AND sqlserver.username <> 'test1' AND sqlserver.username <> 'test2' AND sqlserver.username <> 'test3' ... but this is impractical, is there a better solution? –  carlo.borreo Nov 8 '13 at 14:56
    
I see you posted at sqlblog.com/blogs/davide_mauri/archive/2013/03/17/… as well :-) –  Kin Nov 8 '13 at 17:55
    
@carlo.borreo no, there isn't –  swasheck Nov 8 '13 at 20:25

1 Answer 1

up vote 1 down vote accepted

Since you are using sql 2008R2, you cannot use like predicate comparisons as they were introduced with SQL Sever 2012.

If you are on SQL 2012, then you can use below query to find what is available :

select xp.name, xo.name, xo.description, xo.type_name, xo.type_size from
sys.dm_xe_objects xo
inner join sys.dm_xe_packages xp on xo.package_guid=xp.guid
where object_type like 'pred_compare'

SQL 2008 R2 does not have like_i_sql_unicode_string vs SQL 2012 has it.

enter image description here

So, below should work on SQL 2012

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) AND
            (NOT sqlserver.like_i_sql_unicode_string(sqlserver.username,N'test%'))

    )

) 
ADD TARGET package0.asynchronous_file_target
(
    SET filename='C:\ErrorCapture_Extended_Event.xel'
)
WITH 
(
    MAX_MEMORY=4096 KB,
    EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,
    MAX_DISPATCH_LATENCY=10 SECONDS,
    MAX_EVENT_SIZE=0 KB,
    MEMORY_PARTITION_MODE=NONE,
    TRACK_CAUSALITY=OFF,
    STARTUP_STATE=ON
);

Refer: Connect item Extended events predicate - NOT LIKE required

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.