Issue with STRING_AGG WITHIN GROUP and Visual Studio

Question

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.

Answer 1

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://docs.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.

Answer 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.