If you’re trying to run some OPENJSON() code in SQL Server, but you’re getting error Msg 319, Level 15 “Incorrect syntax near the keyword ‘with'”, one possibility is that you really do have a syntax error.

But if you’ve check and double checked, and you’re convinced there’s no syntax error, it could actually be that the error is a side effect of having the wrong database compatibility level.

Normally you’d get error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.” when using a database compatibility level of lower than 130, but in some cases, SQL Server finds a problem with the WITH clause first.

I encountered this error when running valid OPENJSON() code, but on a database where the compatibility level was only 120.

OPENJSON() is only available on databases with a compatibility level of 130 or greater.

When I checked my database compatibility level, I saw that it was 120. I immediately increased it to 150 and I no longer got the error.

Example of the Error

Here’s an example of code that produces this error when the database compatibility level is lower than 130.

DECLARE @json NVARCHAR(4000) = N'{ 
    "pets" : {
            "cats" : [
            { "id" : 1, "name" : "Fluffy", "sex" : "Female" },
            { "id" : 2, "name" : "Long Tail", "sex" : "Female" },
            { "id" : 3, "name" : "Scratch", "sex" : "Male" }
        ],
            "dogs" : [
            { "id" : 1, "name" : "Fetch", "sex" : "Male" },
            { "id" : 2, "name" : "Fluffy", "sex" : "Male" },
            { "id" : 3, "name" : "Wag", "sex" : "Female" }
        ]
    }
}'

SELECT *
FROM OPENJSON(@json, '$.pets.dogs')
WITH  (
        [id]    int,  
        [name]  varchar(60), 
        [sex]   varchar(6)
    );

Result:

Msg 319, Level 15, State 2, Line 17
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.

Fix the Error

You can easily fix this error by changing the database’s compatibility level to 130 or higher.

-- Change compatibility level
ALTER DATABASE Pets  
SET COMPATIBILITY_LEVEL = 150;

-- Check compatibility level
SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 150                   |
+-----------------------+

Alternatively, if you don’t want to change this, you could switch to a database that you know has a suitable compatibility level.

Hopefully this post helps someone out there that encounters the same error.