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.
