SQL Server Extended Events odd behavior trying to capture statements coming from SAS

All we need is an easy explanation of the problem, so here it is.

The problem: the Extended Events (EE) session we created to capture queries against a certain table is not working when the request comes from SAS.

The EE code:

CREATE EVENT SESSION [TEST_Capture_Queries] ON SERVER 
ADD EVENT sqlserver.sql_statement_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%schema%table%'))),
ADD EVENT sqlserver.sql_batch_completed(
    ACTION(sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.query_hash,sqlserver.query_plan_hash,sqlserver.sql_text,sqlserver.username)
    WHERE ([sqlserver].[like_i_sql_unicode_string]([sqlserver].[sql_text],N'%schema%table%')))
ADD TARGET package0.event_file(SET filename=N'D:\Audit\TEST_Capture_Queries.xel',max_rollover_files=(10))
WITH (MAX_MEMORY=4096 KB,EVENT_RETENTION_MODE=ALLOW_SINGLE_EVENT_LOSS,MAX_DISPATCH_LATENCY=30 SECONDS,MAX_EVENT_SIZE=0 KB,MEMORY_PARTITION_MODE=NONE,TRACK_CAUSALITY=OFF,STARTUP_STATE=ON)
GO

What we see using sp_whoisactive for the SQL Login originating from SAS:

SELECT * FROM schema.table

What we get in EE file for that same login:

-- sql_statement_completed.sql_text
EXEC sp_executesql N'set implicit_transactions off select USER_NAME() select usertype,type,name from systypes where usertype>=257' 
-- sql_batch_completed.batch_text
EXEC sp_executesql N'set implicit_transactions off select USER_NAME() select usertype,type,name from systypes where usertype>=257' 

I’m not especially SAS-savvy but the EE session is definitely capturing queries (from SSMS and app servers) that reference schema.table. I figure I’m missing an Event but the trial and error approach has yet to produce a result. I’d hate to have to dust off and spin up SQL Profiler so any insights would be appreciated.

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

The Event I was missing: sqlserver.rpc_completed

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply