52

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Iva
  • 523
  • 1
  • 4
  • 4

1 Answers1

113

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

Jovan MSFT
  • 13,232
  • 4
  • 40
  • 55
  • Thanks Jovan, it was due to compatibility level. It works with 130. – Iva Apr 07 '16 at 09:08
  • 2
    Now My Error is "Valid values of the database compatibility level are 90, 100, or 110". – Durgesh Pandey Sep 28 '17 at 05:49
  • Are you talking about Azure SQL Database or some SQL Server version? Azure SQL should have all compatibility levels available. – Jovan MSFT Sep 29 '17 at 12:25
  • For extra info, read: http://www.asptricks.net/2017/07/invalid-object-name-openjson-in-sql.html – Jacob van Lingen Feb 21 '18 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 Mar 06 '18 at 00:53
  • i have face the error "Valid values of the database compatibility level are 100, 110, or 120" and i have ssms 2018 – Rinku Choudhary Jul 23 '19 at 16:43