What are the challenges you face when working across database platforms? Take the survey
Options

Formatting CREATE EVENT SESSION introduces syntax error

jgonneringjgonnering Posts: 36 Bronze 4
edited January 26, 2017 10:14AM in SQL Prompt
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

Comments

  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5
    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
    Eddie Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    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.
  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5
    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 Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    Eddie DEddie D Posts: 1,792 Rose Gold 5
    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 Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
  • Options
    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.
  • Options
    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
  • Options
    Yes, I can confirm that the build fixes this issue. Thank you!
Sign In or Register to comment.