Search code examples
t-sqlvisual-studio-2017sql-server-data-tools

SSDT Error on build: Expected XML but encountered JSON instead

I'm using the very latest version of Visual Studio Professional 2017 (v15.9.9) with the latest SQL Server Data Tools (v15.1.61903.01040)

I create a new stored procedure in my database project, for example, with the following code:

create proc [dbo].[MyProc] as
select 'Test' as [TestColumn] FOR JSON AUTO

It won't compile with the errors below, and the keyword JSON is highlighted:

SQL46005: Expected XML but encountered JSON instead
SQL80001: Incorrect syntax near 'JSON'

It seems strange that this doesn't compile, when it's perfectly valid SQL (or am I mistaken? Is there something I'm missing? Or is it genuinely not supported?)

This issue seemed to exist for older versions of SSDT before 2016, but was reported as resolved back then, is this perhaps a recent regression? I can't find any other instances of this error or anything like it.

Solution

  • If you have the "Target Platform" on the .sqlproj properties set to SQL 2014 or less or "Microsoft Azure SQL Database" then this will happen, the compatibility level doesn't affect it (or at least setting my project to 2014 compatibility didn't).

    This works for me with SQL 2016+ and Azure v12 with SSDT "15.1.61902.21100"

    Hope it helps!

    ed