Start SQL Server extended event session using c#

Question

I need to start a SQL Server extended event session through my C# code.

I have this code to create a session and then to execute it, but it is not working, any help please?

Code :

SQL statements to create a session

string sql = "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=ON);';

EXEC master.sys.sp_executesql @sql;"

string sql1=" DECLARE @fichier nvarchar(500),@database sysname = DB_NAME(); 


DECLARE @sql1 nvarchar(max)  =N'ALTER EVENT SESSION  ' + @database + N'_Session ON SERVER STATE = START  '

EXEC master.sys.sp_executesql @sql1;"

Please do not build SQL statements in C# strings. This will make your code suspectable to SQL injection, which is very unsafe. Use something like EntityFramework to communicate with the database, or use stored procedures. — Sagar Patil, Jul 20 '20 at 23:26
Entity Framework: https://learn.microsoft.com/en-us/ef/, Stored Procedures: https://www.w3schools.com/sql/sql_stored_procedures.asp — Sagar Patil, Jul 20 '20 at 23:35
Uncorrect Syntax near @db+N'.. even though the query works fine in SSMS — Turkia Golli, Jul 21 '20 at 09:44

score 0 · Answer 1 · answered Jul 20 '20 at 23:15

0

I think the problem here is that you've put your SQL execution statements into your C# strings. Instead, you should use the framework's System.Data.SqlClient methods to execute your query. See the following:

How to directly execute SQL query in C#?

answered Jul 20 '20 at 23:15

STLDev

5,950
25
36

i am using the same method as the link but it is returning an uncorrect syntax exception even though it works on SSMS – Turkia Golli Jul 21 '20 at 01:20

Start SQL Server extended event session using c#

1 Answers1