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