OPENJSON - Incorrect syntax near the keyword 'with'

Question

Wondering if anyone can work out what's wrong with this, I keep getting this error when reading the table from another database other than [HubSpotCache] (still in the same SQL instance).

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

SELECT
    J.label
FROM
    [HubSpotCache].dbo.[ContactProperties] C
CROSS APPLY 
    OPENJSON(C.[OptionsAggregate])
    WITH (
            label NVARCHAR(100) N'$."value"'
         ) AS J
WHERE 
    ISJSON(C.[OptionsAggregate]) > 0

Here's the value in OptionsAggregate column of the table

[
  {
    "label": "China",
    "value": "China",
    "displayOrder": -1,
    "doubleData": 0.0,
    "hidden": false,
    "readOnly": false
  },
  {
    "label": "Singapore",
    "value": "Singapore",
    "displayOrder": -1,
    "doubleData": 0.0,
    "hidden": false,
    "readOnly": false
  }
]

Check the compatibility level of the databases: SELECT name,compatibility_level FROM sys.databases - it should be at least 130 for openjson to be implemented — Søren Kongstad
– Søren Kongstad, Commented Apr 13, 2019 at 21:00
The title doesn't reflect the question. This has nothing to do with a CTE. — HoneyBadger
– HoneyBadger, Commented Apr 13, 2019 at 21:34
"it works when the query runs in [HubSpotCache] database but not when it's from another database on the same SQL instance." Then the database you're running the query on definitely has a compatibility setting that doesn't support the syntax you're using. — Thom A
– Thom A ♦, Commented Apr 13, 2019 at 21:36

Weihui Guo · Accepted Answer · 2019-08-01 13:14:43Z

16

Which version of SQL server you are using? If you have the right SQL Server version, then most likely it's the Compatibility Level as OPENJSON requires Compatibility Level 130, so check your compatibility level, if it is not 130 set it to 130 and try

edited Aug 1, 2019 at 13:14

Weihui Guo

4,0656 gold badges40 silver badges62 bronze badges

answered May 7, 2019 at 11:07

Srither Kumarraj

1613 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

A5H1Q Over a year ago

ALTER DATABASE <DatabaseName> SET COMPATIBILITY_LEVEL = 130 to set compatibility level

Collectives™ on Stack Overflow

OPENJSON - Incorrect syntax near the keyword 'with'

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related