0

I need to create an Extended Event session to capture all the executed queries while my server is running and save them in a file under a folder with the database name.

DECLARE @file varchar(50);
declare @path nvarchar(250);
SET @file = 'D:\XE\ ' + DB_NAME()
set @path = 'D:\XE\ ' + DB_NAME() + 'day';
EXECUTE master.dbo.xp_create_subdir @file
CREATE EVENT SESSION newSession1
ON SERVER
ADD EVENT sqlserver.sql_statement_completed
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, 
     sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username )
    WHERE ( sqlserver.database_name = 'School' )
    ),
ADD EVENT sqlserver.sql_statement_starting
(    ACTION (sqlserver.sql_text, sqlserver.tsql_stack,
     sqlserver.client_app_name, sqlserver.client_hostname, sqlserver.username )
    WHERE ( sqlserver.database_name = 'School')    
    )
ADD target package0.event_file (SET filename = @path) -- problem
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=OFF)
GO

This code shows an error in set Filename = @path.

New contributor
Turkia Golli is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
1

You need to create the event session in dynamic SQL (or build the whole command in C#).

DECLARE @file nvarchar(500), @path nvarchar(500), @db sysname = DB_NAME(); 

SET @file = RTRIM(N'D:\XE\ ') + @db;
SET @path = @file + N'day'; 

EXEC master.dbo.xp_create_subdir @file;

DECLARE @sql nvarchar(max) = N'CREATE EVENT SESSION ' + @db + N'_Session1 
  ON SERVER 
  ADD EVENT sqlserver.sql_statement_completed 
  (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name, 
            sqlserver.client_hostname, sqlserver.username 
  ) WHERE ( sqlserver.database_name = N''' + @db + N''' ) ),
  ADD EVENT sqlserver.sql_statement_starting 
  (
    ACTION (sqlserver.sql_text, sqlserver.tsql_stack, sqlserver.client_app_name, 
            sqlserver.client_hostname, sqlserver.username 
  ) WHERE ( sqlserver.database_name = N''' + @db + N''')
)
ADD target package0.event_file
(
  SET filename = N''' + @path + N'''
) 
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=OFF);';

EXEC master.sys.sp_executesql @sql;
  • thank you so much , it works now , i should do the same for the database_name to be DB_NAME() ? – Turkia Golli yesterday
  • That probably makes sense, yeah. Edited. May also want to name each session so that it is correlated too. – Aaron Bertrand yesterday

Your Answer

Turkia Golli is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.