August 22, 2018 at 3:26 pm
create table #a (a int, b varchar(1000), c int)
insert #a select 1,'a',1 union select 2,'b',1
select string_agg(b,'') within group (order by b)
from #a
group by c
This statement runs fine when run from SSMS on some users machines. It doesn't seem that the version of SSMS matters. On machines that the query does not work, we have upgraded them to the latest version, tried to rebuild the install, and unistalled and installed with no luck. It seems to be a syntactical check in SSMS that is failing because if the table in the statement doesn't exist then the error is still thrown. The error is as follows:
Line 73, column 1
Incorrect syntax near from.
but is always the first character after the closing parenthesis of the order by clause.
If anyone has run into this issues and resolved it, I would love to get the resolution.
Thanks,
Kevin
August 22, 2018 at 3:31 pm
You did check that they're all running on a SQL 2017 Server, right? And that the compatibility mode is set correctly?
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 22, 2018 at 3:32 pm
Are all users connecting to the same SQL Server? Are they all running queries in the same database? If not, are all databases at the latest compatibility level?
February 1, 2019 at 10:32 am
I resurrected my slqservercentral account to respond to this old post because I tore my hair out with this issue. I experienced this issue using SSMS 17.9.1, connecting to a SQL Server 2017 database, with the correct compatability levels yada yada. I could run the query in other tools, such as DBeaver, just fine, and other users in SSMS were unaffected. Finally, I realized that I was still connecting to the db with the following: column encryption setting=enabled because I was multi-tasking and doing work in another db that had sensitive fields like SSN encrypted using MSSQL Always-On encryption. This was triggering the SSMS parameterization features that allow one in SSMS to debug things in these types of DBs. As soon as I took that back out of my connection parameters, I was able to run this query in SSMS without it throwing this syntax error.
It is a bit tricky to repeat this bug, but I was able to replicate the error by doing the following:
Connect to the server with column encryption setting=enabled
Open a new query in the database using column encryption and query one of its encrypted columns using a parameter substitution query.
Open a new query in an unrelated database and run a string_agg query with a "within group (order by )" clause.
"Syntax error near..." will pop.
To clear it up:
Disconnect any tabs using the column encryption setting.
Reconnect tab making sure that setting is not present in the options menu.
Run query -> should execute as expected.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply