Formatting CREATE EVENT SESSION introduces syntax error
jgonnering
Posts: 36 Bronze 4
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
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
Comments
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
In SQL Prompt options under Special Characters, I have "Enclose identifiers within square brackets []" checked.
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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
Senior Product Support Engineer
Redgate Software Ltd
Email: support@red-gate.com
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