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?