Dynamic SQL for execute ALTER EVENT SESSION

Question

Hi all, but ALTER EVENT SESSION command could by be a dynamic statements?
'Cause I got always err...
You need first.. a Login "Extended Event".

DECLARE @Path nvarchar(200)
SET @Path = N'C:\Traces\aaa.xel'
;
DECLARE @SQLString NVARCHAR(500);   
SET @SQLString = N'
ALTER EVENT SESSION [Logins] ON SERVER 
ADD TARGET package0.event_file (SET filename=N''' + @Path + ''' , max_file_size=100, max_rollover_files = 100)'
;
EXEC @SQLString
;

err is...

Msg 203, Level 16, State 2, Line 25
The name '
ALTER EVENT SESSION [Logins] ON SERVER
ADD TARGET package0.event_file (SET filename=N'C:\Traces\aaa.xel' , max_file_size=100, max_rollover_files = 100)' is not a valid identifier.

Alen

Answer 1

Enclose the variable in parenthesis so that the value is not interpreted as a stored procedure name:

EXEC (@SQLString);

Alternatively, use sp_executesql:

EXEC sp_executesql @SQLString;

Answer 2

In addition to Dan's post, this is what EXEC @VAR without parens means:

   DECLARE  @var sysname = 'sp_who'  
   EXEC @var  

That is, execute the stored procedure of which the name is in @VAR .

Answer 3

Hi，@Alen Cappelletti

Welcome to Microsoft T-SQL Q&A Forum!

Have you tried this?

EXEC master.sys.sp_executesql @sqlString;  

Best regards,
Bert Zhou

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.