24

I am using Visual Studio 2015 and having an issue in my SQL Server database project with STRING_AGG WITHIN GROUP.

SELECT [Continent], 
STRING_AGG([Country], ', ') WITHIN GROUP (ORDER BY [Country]) AS CountryList
FROM [Country]
GROUP BY [Continent]

This code gives an error SQL46010: Incorrect syntax near AS. Without the 

WITHIN GROUP (ORDER BY [Country])

there is no problem.

The target platform is set to

Microsoft Azure SQL Database V12

The debug target connection is set to a local SQL Server vNext database.

Within the SQL Server Management Studio this piece of code executes without any problem. Running the stored procedure within SSMS or from code gives the expected and correct result (1 row per continent with a list of countries per continent separated by commas ordered by country).

Meanwhile I installed Visual Studio 2017 hoping this would eliminate the error, but no luck.

SQL Server Data Tools 17 (build 14.0.61704.140) is installed.

Improve this question
1
  • I tested this using the latest SSDT (version number 14.0.61705.170) and this does not show as an error. I believe updating to the latest from learn.microsoft.com/en-us/sql/ssdt/… should fix the issue for you. Commented Jul 14, 2017 at 0:09

4 Answers 4

Reset to default
30

I had a similar problem & suspect this could be a compatibility issue, as the following sorted it for me. What I hadn't realised was that each SQL Server database has it's own COMPATIBILITY_LEVEL which affects how that database can work. https://learn.microsoft.com/en-us/sql/t-sql/statements/alter-database-transact-sql-compatibility-level?view=sql-server-2017 explains how this database property can be queried & altered.

SELECT NAME, COMPATIBILITY_LEVEL FROM sys.databases; showed my database had a COMPATIBILITY_LEVEL of 100 (ie it was compatible with SQL Server 2008).

Having run

ALTER DATABASE <DATABASE_NAME>
SET COMPATIBILITY_LEVEL = 110

(or 120 or 130) so it was now compatible with SQL Server 2012+, the query that included the WITHIN GROUP clause of the STRING_AGG function started working.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

On SQL Azure this fixed my issue, although my error was different than the one in the question. It said "Syntax error near '('"
5

One other thing to note with the compatibility level, if your DB is at an earlier compatibility level than the SQL installation and you don't want to change it, you can just run the query from master and use the fully qualified path to the DB table you want to query and it will use the master DB's compatibility level (i.e. whatever the SQL installation version is).

e.g:

Change:

USE MyDB
GO
SELECT STRING_AGG(MyNames, ',') FROM dbo.MyTable

To:

USE Master
GO
SELECT STRING_AGG(MyNames, ',') FROM MyDB.dbo.MyTable
Improve this answer

2 Comments

While true, if you want the query in a stored proc, the stored proc has to live in the master DB, which is rarely desirable. Now end users need ability to execute this procedure from master, so they need users mapped to master with permissions, etc. Big mess. Workaround is to create another DB with sufficient compatibility level and put the SP there.
Obviously if you are changing an application or writing stored procs that need to use the syntax you would just update the DB compatibility level and eat any regression testing and/or paper work required to implement the change in a production environment. But if you just want to run a one off query on an older system, this might save you some overhead.
2

Problem

When I tried to build my database project in Visual Studio 2017 I got an error like:

"syntax error near ')'". This appeard after my STRING_AGG()implementation when using the WITHIN GROUP() clause.

Solution

I solved this issue by changing the Target Platform under "Project --> " Properties" in Visual Studio.

Changed this to SQL Server 2017, and now my database project builds without errors. Seems like the default target plattform for Visual Studio 2017 is SQL Server 2016.

Improve this answer

Comments

2

I had the same error when using STRING_AGG() and WITHIN GROUP():

Incorrect syntax near '('.

This was done in SSMS v18.5 on SQL Server 2019, but...the database had been restored from a BAK provided by a customer using an older version of MSSQL.

Despite this different spin on the issue in the OP, I found like others here, the solution was to check & increase COMPATIBILITY_LEVEL. This is easily done via the UI, as shown below. I needed only to change it from 100 to 150, and this resolved the issue.

enter image description here

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.