OPENJSON does not work in SQL Server?

Question

I want to use JSON functions in SQL Server 2016, but when I try to execute OPENJSON function, I get the following error:

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

Why it does not work? I have SQL Server 2016 RC version.

Solution

Could you check compatibility level on database? OPENJSON is available under compatibility level 130. Could you try to execute:

ALTER DATABASE database_name SET COMPATIBILITY_LEVEL = 130

Also, if you are using JSON on Azure SQL Database, note that even new databases are created under 120 compatibility level so you should change it if you want to use OPENJSON. Also, if you are using it in Azure SQL Database, run select @@version to see is this V12 server. You should see something like:

Microsoft SQL Azure (RTM) - 12.0.2000.8 Mar 25 2016 15:11:30 Copyright (c) Microsoft Corporation

If you see some lower version (e.g. 11.xxx) you probably have database on old architecture where JSON is not supported.

Regards,

Jovan

Comments

[Mike Miller]

Can you post all the SQL?

[TheGameiswar]

also select @@version info will help

[Iva]

Thanks Jovan, it was due to compatibility level. It works with 130.

[Durgesh Pandey]

Now My Error is "Valid values of the database compatibility level are 90, 100, or 110".

[Jovan MSFT]

Are you talking about Azure SQL Database or some SQL Server version? Azure SQL should have all compatibility levels available.

[Jacob van Lingen]

For extra info, read: asptricks.net/2017/07/invalid-object-name-openjson-in-sql.ht‌​ml

[Davos]

@DurgeshPandey If you run SELECT @@VERSION you will see you are running SQL Server 2012 (compatibility level 110) so that error message is telling you that you can't change to a compatibility level for a version of SQL Server that is newer than what you have, i.e. SQL Server 2016 (compatibility level 130).

[Rinku Choudhary]

i have face the error "Valid values of the database compatibility level are 100, 110, or 120" and i have ssms 2018