Extended Events - Filtering based on sql_text

Question

Extended Events - Filtering based on sql_text

winston Smith

SSCoach

Points: 19498
More actions
February 5, 2013 at 4:50 am

#270287

I need to leave a monitor runing long term to capture all calls to procs beginning with 'val_'
Extended events seem to be a good fit here, but when adding the sqlserver.sp_statement_starting event, I cant filter on the sql_text column:
CREATE EVENT SESSION CSP_Procs
ON SERVER
ADD EVENT sqlserver.sp_statement_starting
(
ACTION(sqlserver.nt_username, sqlserver.username, sqlserver.client_hostname, sqlserver.sql_text, sqlserver.database_id)
WHERE sqlserver.sp_statement_starting.sql_text LIKE '%val%'--this line is underlined in red by the parser
)
is it even possible to filter like this, as its pretty simple in profiler/server side tracing

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

SQLmountain SSCommitted Points: 1505 More actions · Answer 1

This is xml data. You are attempting to do a string comparison by using LIKE, and it expects a text data type. Try looking at how to perform this on xml data.

winston Smith SSCoach Points: 19498 More actions · Answer 2

DBA_bret (2/5/2013)
This is xml data. You are attempting to do a string comparison by using LIKE, and it expects a text data type. Try looking at how to perform this on xml data.

Il check that out. as it can accept <, >, =, != etc i assumed it might work like the where clause in a TSQL statement.

Richard Fryar Hall of Fame Points: 3457 More actions · Answer 3

Try this

CREATE EVENT SESSION CSP_Procs

ON SERVER

ADD EVENT sqlserver.sp_statement_starting

(

ACTION(sqlserver.nt_username, sqlserver.username, sqlserver.client_hostname, sqlserver.sql_text, sqlserver.database_id)

WHERE sqlserver.like_i_sql_unicode_string(sqlserver.sql_text, '%val%')

)

Check Your SQL Servers Quickly and Easily
www.sqlcopilot.com

New2SQL-343122 Default port Points: 1461 More actions · Answer 4

Hi,

Can you please share the compatible code for SQL 2K8 R2.

because, this script not working.

Thanks

nhapzz SSC Enthusiast Points: 144 More actions · Answer 5

Unfortunately the like predicate comparisons were only introduced with SQL Sever 2012.

I'm sorry for digging up this post, but this can help or at least get someone on the right track with extended events.