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

by

The question:

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:AuditTEST_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.

The Solutions:

Below are the methods you can try. The first solution is probably the best. Try others if the first one doesn’t work. Senior developers aren’t just copying/pasting – they read the methods carefully & apply them wisely to each case.

Method 1

The Event I was missing: sqlserver.rpc_completed


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 Comment