STRING_AGG WITHIN GROUP和Visual Studio的问题 [英] Issue with STRING_AGG WITHIN GROUP and Visual Studio

问题描述

我正在使用Visual Studio 2015，并且SQL Server数据库项目中的STRING_AGG WITHIN GROUP出现问题.

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]

此代码给出错误SQL46010:AS附近的语法不正确.没有

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

WITHIN GROUP (ORDER BY [Country])

没问题.

目标平台设置为

Microsoft Azure SQL Database V12

调试目标连接设置为本地SQL Server vNext数据库.

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

在SQL Server Management Studio中，这段代码可以毫无问题地执行.在SSMS或代码中运行存储过程可以得到预期的正确结果(每个大洲1行，其中每个大洲的国家/地区列表由按国家/地区排序的逗号分隔).

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

与此同时，我安装了Visual Studio 2017，希望这样做可以消除错误，但没有运气.

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

已安装SQL Server数据工具17(内部版本14.0.61704.140).

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

推荐答案

我遇到了类似的问题&怀疑这可能是兼容性问题，如下所述.我还没有意识到，每个SQL Server数据库都有它自己的COMPATIBILITY_LEVEL，这会影响该数据库的工作方式.

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.

从sys.databases中选择名称，COMPATIBILITY_LEVEL； 显示我的数据库的COMPATIBILITY_LEVEL为100(即与SQL Server 2008兼容).

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

正在跑步

ALTER DATABASE <DATABASE_NAME>
SET COMPATIBILITY_LEVEL = 110

(或120或130)，因此它现在与SQL Server 2012+兼容，包含STRING_AGG函数的WITHIN GROUP子句的查询开始工作.

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

这篇关于STRING_AGG WITHIN GROUP和Visual Studio的问题的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！