QUERY_CAPTURE_MODE = CUSTOM

torsten.strauss

Hi Redagte Team,

SQL Prompt will raise on error formatting the following code:

ALTER DATABASE Current

SET QUERY_STORE (OPERATION_MODE = READ_WRITE, QUERY_CAPTURE_MODE = CUSTOM)

GO

Thanks for fixing!

squigley

Good Afternoon,

Thanks for the inquiry on your end and sorry to hear about your issue with SQL Prompt.

I was able to replicate using the same code that you mentioned so I'll be sure to escalate this to the developers and let you know of our next steps.

squigley

After some further testing, we noticed that the particular SQL Code being tested might not be valid:

Do you see any similar issues with valid SQL Code?

Thanks!

torsten.strauss

Hi!
The syntax is valid in SQL Server 2019.

squigley

Good Morning!
Thanks for your patience with this.
I was able to hear back from the developers and can confirm that this should work better now.

Can you try upgrading to the below version and see if that helps?
https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.1.7.15015.exe

Thanks!

torsten.strauss

Hi!

Unfortunately, SQL Prompt will still fail to format the code

ALTER DATABASE WideWorldImporters SET QUERY_STORE = ON (

QUERY_CAPTURE_MODE = CUSTOM,

QUERY_CAPTURE_POLICY =(

    EXECUTION_COUNT = 10,

    TOTAL_COMPILE_CPU_TIME_MS = 1000,

    TOTAL_EXECUTION_CPU_TIME_MS = 100,

    STALE_CAPTURE_POLICY_THRESHOLD = 1 day)

)

GO

TomW

Hi Torsten,

Unfortunately, this issue is caused by a parsing error in the Microsoft T-SQL parser that we use for formatting, so there's not a huge amount we can do except report the issue to Microsoft.

Thanks,
Tom