Sunday 23 July 2017

Invalid object name 'OPENJSON' in SQL Server 2016

While i was tying a query which was working perfectly on SSMS, but recently i tried on a Database, and i was getting some error with OPENJSON

Error Message:

Msg 208, Level 16, State 1, Line 179
Invalid object name 'OPENJSON'.


SQL Server Query:


DECLARE @json NVARCHAR(MAX)
SET @json =
N'[
       { "id" : 2,"info": { "name": "Raj", "surname": "Thakur" }, "age": 28 },
       { "id" : 5,"info": { "name": "Raja", "surname": "Babu" }, "dob": "1996-08-07" }
 ]'  ;
SELECT *
FROM
    OPENJSON(@json)
    WITH
    (
        id INT '$.id',
        firstName NVARCHAR(50) '$.info.name',
        lastName NVARCHAR(50) '$.info.surname',
        age INT,
        dob DATETIME2 '$.dob'
    );


Solution:

1.You need to ensure that you have SQL server 2016 installed.

SELECT @@VERSION
Microsoft SQL Server 2016 (SP1-GDR) (KB3210089) - 13.0.4202.2 (X64)   Dec 13 2016 05:22:44   Copyright (c) Microsoft Corporation  Express Edition (64-bit) on Windows 10 Pro 6.3 <X64> (Build 10240: ) 

2. Make sure you have database with compatibility_level 130

SELECT compatibility_level FROM sys.databases WHERE name = 'DB2016';  
120

3. Change compatibility_level of database
ALTER DATABASE 'DB2016' SET COMPATIBILITY_LEVEL = 130;



Summary