57

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.

Improve this question
2
  • 1
    Can you post all the SQL?
    – Mike Miller
    Commented Apr 7, 2016 at 8:35
  • 1
    also select @@version info will help
    – TheGameiswar
    Commented Apr 7, 2016 at 8:40

1 Answer 1

Reset to default
119

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

Improve this answer
6
  • Thanks Jovan, it was due to compatibility level. It works with 130.
    – Iva
    Commented Apr 7, 2016 at 9:08
  • 2
    Now My Error is "Valid values of the database compatibility level are 90, 100, or 110".
    – Durgesh Pandey
    Commented Sep 28, 2017 at 5:49
  • Are you talking about Azure SQL Database or some SQL Server version? Azure SQL should have all compatibility levels available.
    – Jovan MSFT
    Commented Sep 29, 2017 at 12:25
  • For extra info, read: asptricks.net/2017/07/invalid-object-name-openjson-in-sql.html
    – Jacob van Lingen
    Commented Feb 21, 2018 at 15:11
  • 2
    @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).
    – Davos
    Commented Mar 6, 2018 at 0:53

Not the answer you're looking for? Browse other questions tagged or ask your own question.