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

"Format SQL" fails on query hint

Don't know if this is due to a recent upgrade, or due to a specific query. But here are the steps to reproduce.
(My configuration: SSMS 19.2.56.2; SQL Prompt 10.14.3.4664; SQL Server 2022, RTM-CU8-GDR, 16.0.4080.1, running on locally on my laptop).

Open a new query editor window, connect to AdventureWorks2017, type this query:

SELECT     soh.SalesPersonID,
           sod.OrderQty,
           soh.OrderDate
FROM       Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
   ON      sod.SalesOrderID = soh.SalesOrderID
WHERE      sod.UnitPrice * sod.OrderQty > 5000
ORDER BY   soh.SalesPersonID;

Hit Ctrl-K - Ctrl-Y (or select Format SQL from the SQL Prompt menu) - works.

Change the query window to:

SELECT     soh.SalesPersonID,
           COUNT_BIG (*)       AS AmtSales,
           SUM (sod.OrderQty)  AS TotalQty,
           MAX (soh.OrderDate) AS LastSale
FROM       Sales.SalesOrderHeader AS soh
INNER JOIN Sales.SalesOrderDetail AS sod
   ON      sod.SalesOrderID = soh.SalesOrderID
WHERE      sod.UnitPrice * sod.OrderQty > 5000
GROUP BY   soh.SalesPersonID
ORDER BY   soh.SalesPersonID
OPTION (ORDER GROUP);

Try Ctrl-K, Ctrl-Y (or Format SQL) again. Result: error.






--
Hugo Kornelis
(SQL Server MVP, 2006-2016 + 2019-now // Friend of Red Gate)

Answers

Sign In or Register to comment.