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



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



  • The Event I was missing: sqlserver.rpc_completed




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2