Extended Events - Filtering based on sql_text

  • 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

  • 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.

  • 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.

  • 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%')

    )

  • Hi,

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

    because, this script not working.

    Thanks

  • 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.

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

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