SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software SQL Server 2016 Download and Tutorials
Development resources, articles, tutorials, samples, codes and tools for .Net, SQL Server, Windows, Windows Phone, SAP and ABAP, like SAP UI5, Screen Personas, etc.




download SQL Server 2016
download SQL Server 2014
download SQL Server 2012



SQL Server 2016 OpenJSON Error

SQL Server 2016 introduce JSON support for developers to parse JSON string from a variable into table data using SELECT OPENJSON command and export database data as JSON formatted string.

Recently while I was trying to improve my T-SQL development know-how on SQL Server 2016 JSON support, I experienced following error:
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Although the error reminds me incorrect usage of CTE (Common Table Expressions) statements, the reason is different.

Let's export databases names created on local SQL Server 2016 instance as JSON formatted string. And then try to parse the JSON string into row based data to import into SQL Server.

declare @jsonstring nvarchar(max)

set @jsonstring = (select name from sys.databases for json auto)

select name
from openjson (@jsonstring, '$')
with (
 name sysname
) as SQLDatabaseList

SQL Server 2016 Select From OpenJSON syntax to parse JSON data

As T-SQL developers can see easily, the first two SQL statements are for creating a sample JSON string data.

The last SELECT statement with OPENJSON syntax is used to parse JSON data into structured row-based format in order to import into SQL Server database table.

Unfortunately, I got the following error message:

Msg 319, Level 15, State 1, Line 7
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

Don't get confused with the error message above during the execution of SQL SELECT command with OPENJSON
This error is thrown by SQL Server Engine because of the incompatible SQL Server version.

SQL Server 2016 supports exporting data from database into JSON string variables with the introduction of SQL Server 2016 CTP2

On the other hand, support for parsing JSON string or support for SELECT FROM OPENJSON command to parse data from Json formatted text variables and exprt into SQL Server database tables with the introduction of SQL server 2016 CTP3

So if you get the following error message, please check your installed SQL Server instance version and then download the latest SQL Server 2016 version and install it for the OpenJSON support.







Related SQL Resources

SQL Server Articles

SQL Server 2012

SQL Server Tools

SQL Blog

SQL Server 2008 Blog

Certification Exams Blog

Reporting Services Blog

Analysis Services Blog

MS SQL Server Forums







Copyright © 2004 - 2017 Eralper YILMAZ. All rights reserved.
Community Server by Telligent Systems