SQLPrompt does not understand SQL 2022 syntax?

Win32nipuh

Try this simple SQL 2022 code
declare @x int=1 
declare @xx   int=2
if @x is not distinct from @xx print '=' else print '<>' 

SQL Prompt generates syntax error.

Can it be fixed soon?

Victoria W

Hi @Win32nipuh
 
I've tried your code on my SQL Server 2022 instance with SQL Prompt v10.14.15 and it appears to be formatting my code as expected.
 
Can you please confirm your SQL Prompt & SSMS versions you are running when this error occurs and also the error message you are receiving. Can you also advise what action in SQL Prompt you are conducting when it appears
 


If you need to patch to the latest version of SQL Prompt you can access v10.14.20 here:
https://download.red-gate.com/checkforupdates/SQLPrompt/SQLPrompt_10.14.20.9646.exe

Win32nipuh

Check it here and let me know about results please:
https://www.red-gate.com/products/sql-prompt/#format-sql

Jon_Kirkwood

Hi there, I understand that the error being displayed there is coming from SQL Server directly and isn't being generated by SQL Prompt

The IS NOT DISTINCT syntax is new for SQL Server 2022 & Azure SQL instances and is anticipated to throw a syntax error if executed against older versions of SQL Server.

I suspect our online SQL formatter is not using SQL Server 2022 and this is why the syntax error occurs.
Are you using SQL Prompt as a tool deployed to SSMS, VS or ADS on your local device?


If this is the error you are receiving in your environment, then I suspect you are running this against an invalid version of SQL Server to parse this code
https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16


Are you receiving any other errors than 'Incorrect syntax near distinct' when formatting your code?

Win32nipuh

I click Post Comment  but it disappeared and not posted.
----
I am looking for tool and try different, SQL Prompt is one of them.
We have used Poors Man formatter but it does not understand even more syntax .
We are working with SQL 2022 and Azure so we need the tool to format T-SQL to use it in SSMS 19-20, VS 2022, Azure Data Studio. VSC will be good.

Win32nipuh

Hi Victoria, please try in the online formatter provided by RG. What is the result?
FYI: the link https://www.red-gate.com/products/sql-prompt/#format-sql

Win32nipuh

Jon_Kirkwood said:

Hi there, I understand that the error being displayed there is coming from SQL Server directly and isn't being generated by SQL Prompt

The IS NOT DISTINCT syntax is new for SQL Server 2022 & Azure SQL instances and is anticipated to throw a syntax error if executed against older versions of SQL Server.

I suspect our online SQL formatter is not using SQL Server 2022 and this is why the syntax error occurs.
Are you using SQL Prompt as a tool deployed to SSMS, VS or ADS on your local device?


If this is the error you are receiving in your environment, then I suspect you are running this against an invalid version of SQL Server to parse this code
https://learn.microsoft.com/en-us/sql/t-sql/queries/is-distinct-from-transact-sql?view=sql-server-ver16


Are you receiving any other errors than 'Incorrect syntax near distinct' when formatting your code?

Hi Jon, 
I do not use SQL Prompt, I am trying different tools to select what is suitable for us. We have used Poors Man formatter but it does not understand even more syntax constructions. So I have tried your online formatter and wrote the request. We need a tool for formatting T-SQL in SSMS, VS, Azure Data Studio. And also in VSC will be good but it is not mandatory for now. We are using SQL 2022 on-premise and Azure.
Regards.

Jon_Kirkwood

Excellent to hear, SQL Prompt could certainly meet your requirements. 

We have support for the following SQL Server versions:

SQL Prompt can connect to the following versions of SQL Server:

• SQL Server 2022
• SQL Server 2019
• SQL Server 2017
• SQL Server 2016
• SQL Server 2014
• SQL Server 2012
• Azure SQL Database
• SQL Server on Azure VM
• Google Cloud SQL for SQL Server & Virtual Machines
• Amazon SQL Server RDS & Virtual Machines

We also support through SSMS2016 through to SSMS20 and VS2019-2022
More details available here

There is also a beta extension of SQL Prompt for Azure Data Studio. This isn't a fully fledged offering of SQL Prompt however the developers are trying to keep similar feature sets and formatting rules consistent between all offerings of SQL Prompt.

If you haven't already you can download a trial of our tools and if wanting a longer trial or discuss further I would recommend reaching out to our sales team to discuss further
Trial of our SQL Toolbelt Essentials bundle including SQL Prompt - link
Contact our sales team - sales@red-gate.com