0

I am attempting to use OPENJSON in a database that is running on SQL Server 2016, and get the following error when running this simple test query (which works fine on a different 2016 database)

select * from OPENJSON('{ "test": "test" }')

Invalid object name 'OPENJSON'.

I know about the compatibility level settings, but that doesn't seem to be the case on this database. It's compatibility level is already set to 130. This particular database was migrated from an old 2008R2 database. Is there something else we need to do to access the OPENJSON function?

EDIT

As a test, I created a new empty database on the same database server, and the above query works fine. So the database server doesn't seem to be the issue, it's something related to the one database we migrated.

If it matters, I'm connected as the SA account.

mituw16
  • 5,126
  • 3
  • 23
  • 48
  • What is `select @@VERSION`? – Martin Smith Jul 19 '19 at 13:10
  • @MartinSmith `Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Enterprise Edition (64-bit) on Windows Server 2016 Standard 6.3 (Build 14393: ) (Hypervisor) ` – mituw16 Jul 19 '19 at 13:10
  • 1
    Sounds like the database really isn't in compatibility 130. What does `SELECT compatibility_level FROM sys.databases WHERE name = 'YourDatabase';` return? – Thom A Jul 19 '19 at 13:11
  • @Larnu `SELECT compatibility_level FROM sys.databases WHERE name = 'MyDB';` returns `130` The same query against the new empty test DB I created also returns `130` – mituw16 Jul 19 '19 at 13:12
  • Then the above will work. I *assume* your connected to the DB, and not querying it from another database (not in compatibility 130). – Thom A Jul 19 '19 at 13:13
  • @Larnu Yes. I am directly connected to it as the `SA` account. I would think it would work too. I'm at a loss as to why it's not. – mituw16 Jul 19 '19 at 13:14
  • Okay, this is even weirder I just ran `ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 130` , even though checking the compatibility level from the above query returns 130, and now it works.. WTF Microsoft?! – mituw16 Jul 19 '19 at 13:15
  • I *assume* `SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME();` returns `130` too? – Thom A Jul 19 '19 at 13:16
  • looks like maybe some bug there. https://feedback.azure.com/forums/908035-sql-server/suggestions/32902603-invalid-object-name-openjson-when-in-compatibili – Martin Smith Jul 19 '19 at 13:17
  • 1
    Thanks for the help @Larnu and Martin Smith... This was really weird and baffling – mituw16 Jul 19 '19 at 13:19
  • @Larnu if you were interested, I added an edit to the answer I put that I *think* is what was causing this weirdness – mituw16 Jul 19 '19 at 16:59
  • @MartinSmith if you were interested, I added an edit to the answer I put that I think is what was causing this weirdness – mituw16 Jul 19 '19 at 17:00

1 Answers1

2

The database's reported compatibility level is 130 from both SSMS gui and by running SELECT compatibility_level FROM sys.databases WHERE name = 'MyDB';

For no reason other than to test, I ran ALTER DATABASE MyDB SET COMPATIBILITY_LEVEL = 130 , and now everything works. I don't know what would cause that.

EDIT

I think I know what caused the weird behavior now. The database that was exhibiting this behavior is a dev database that is created every single morning via replication from the prod database. After some more researching, the prod database was not set to compatibility level 130, but rather 100. I'm thinking that when replication occured and restored the dev DB from the prod log files, even though the dev db was set to 130, something was mismatched between the two. I've since upped prod to 130 as well and all should be good going forward.

mituw16
  • 5,126
  • 3
  • 23
  • 48