Formatting CREATE EVENT SESSION introduces syntax error

jgonnering

I scripted an event session I created (below)

CREATE EVENT SESSION [VMS] ON SERVER
ADD EVENT sqlserver.rpc_completed(SET collect_statement=(1)
ACTION(sqlos.task_time,sqlserver.client_app_name,sqlserver.client_hostname,sqlserver.database_name,sqlserver.nt_username,sqlserver.sql_text)
WHERE ([sqlserver].[nt_user]<>N'CORPRMSSQL_REPL_001'))
ADD TARGET package0.ring_buffer
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

When I format with SQL Prompt, it adds brackets (see red below) around the collect_statement causing this error:
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near 'collect_statement'.


CREATE EVENT SESSION [VMS]
ON SERVER
ADD EVENT [sqlserver].[rpc_completed]
(SET
/color]collect_statement[color=#FF0000 = (1)
ACTION
(
[sqlos].[task_time],
[sqlserver].[client_app_name],
[sqlserver].[client_hostname],
[sqlserver].[database_name],
[sqlserver].[nt_username],
[sqlserver].[sql_text]
)
WHERE ([sqlserver].[nt_user] <> N'CORPRMSSQL_REPL_001')
)
ADD TARGET [package0].[ring_buffer]
WITH
(
MAX_MEMORY = 4096KB,
EVENT_RETENTION_MODE = ALLOW_SINGLE_EVENT_LOSS,
MAX_DISPATCH_LATENCY = 30 SECONDS,
MAX_EVENT_SIZE = 0KB,
MEMORY_PARTITION_MODE = NONE,
TRACK_CAUSALITY = OFF,
STARTUP_STATE = OFF
);
GO

Eddie D

Hi, thank you for your forum post and sorry to see that you have encountered a problem.

What version of SQL Prompt are you using?

What version of SSMS are you using?

I ask the above questions as I cannot recreate the reported problem using SQL Prompt V7.2.4.291 or V7.3.0.775 with SSMS 2016 V13.0.16100.1. I do not experience the [ ] brackets around the 'SET collect_statement' parameter:

CREATE EVENT SESSION [VMS] ON SERVER
ADD EVENT sqlserver.rpc_completed ( SET collect_statement = ( 1 )
ACTION ( sqlos.task_time, sqlserver.client_app_name,
sqlserver.client_hostname, sqlserver.database_name, sqlserver.nt_username,
sqlserver.sql_text )
WHERE ( [sqlserver].[nt_user] <> 'Domainuser' ) )
ADD TARGET package0.ring_buffer
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

Many Thanks
Eddie

jgonnering

I am using SQL Prompt V7.3.0.775 with SSMS 2014 V12.0.4100.1

In SQL Prompt options under Special Characters, I have "Enclose identifiers within square brackets []" checked.

Eddie D

Hi, thank you for your reply.

Turning on the "Enclose identifiers within square brackets []" SQL Prompt options ->Inserted code ->Special characters, made no actual difference for me.

I believe I have a reproduction of the problem.

If you edit the SQL Prompt Options ->Format ->Actions -> Is the "Add/remove square brackets" option turned on with the "Add brackets to all identifiers" selected?

Many Thanks
Eddie

Eddie D

Hi, thank you for your reply.

Turning on the "Enclose identifiers within square brackets []" SQL Prompt options ->Inserted code ->Special characters, made no actual difference for me.

I believe I have a reproduction of the problem.

If you edit the SQL Prompt Options ->Format ->Actions -> Is the "Add/remove square brackets" option turned on with the "Add brackets to all identifiers" selected?

Many Thanks
Eddie

jgonnering

Sorry for the delay in responding. Yes, I have the "Add/remove square brackets" checked and the "Add brackets to all identifiers" radio button selected.

Harry Frankish

Hi jgonnering

Square brackets should no longer be added to the event customizable attributes in this build: http://download.red-gate.com/EAP/SQLPro ... .0.245.exe

Please let us know if this fixes the problem for you.

Cheers
Harry

jgonnering

Yes, I can confirm that the build fixes this issue. Thank you!