Stuck with invalid object name openjson sql server and not sure what to do? Our SQL Server Support team is here to lend a hand with your queries and issues.

Invalid object name openjson SQL server

If you encounter error Msg 208, Level 16 “Invalid object name ‘OPENJSON’.”, you are probably trying to use the OPENJSON() function on a database with a compatibility level of less than 130.

OPENJSON() is only available under compatibility level 130 or higher.

How to fix Invalid object name openjson SQL server?

To fix this, either increase the compatibility level of your database to 130 or higher, or change to a database that already has the appropriate compatibility level.

Check the Compatibility Level of the Database

You can query sys.databases to check the compatibility level of the database.

SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 120                   |
+-----------------------+

As suspected, this database has a compatibility level of less than 130.

Solution 1

The most obvious solution is to increase the compatibility level of the database for which you’re trying to run OPENJSON() against.

ALTER DATABASE Pets  
SET COMPATIBILITY_LEVEL = 150;

Running that code will increase the database’s compatibility level to 150, which is more than high enough to support the OPENJSON() function.

If we check the compatibility level again, we can see that it’s increased to 150.

SELECT compatibility_level
FROM sys.databases
WHERE name = 'Pets';

Result:

+-----------------------+
| compatibility_level   |
|-----------------------|
| 150                   |
+-----------------------+

Now we can run the original code without error.

USE Pets;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+

Solution 2

If for some reason you can’t, or don’t want to, change the compatibility level of the database, you could switch to a database that already has the appropriate compatibility level.

Obviously, this may or may not be suitable, depending on whether you need to insert your parsed JSON into the database or not.

Anyway, to do this, you could query sys.databases for a suitable database.

SELECT 
    name,
    compatibility_level
FROM sys.databases;

Result:

+--------------------+-----------------------+
| name               | compatibility_level   |
|--------------------+-----------------------|
| master             | 150                   |
| tempdb             | 150                   |
| model              | 150                   |
| msdb               | 150                   |
| Music              | 150                   |
| Test               | 150                   |
| WideWorldImporters | 130                   |
| World              | 140                   |
| Pets               | 120                   |
+--------------------+-----------------------+

Fortunately in this case, all other databases are 130 or higher. So we could switch to any one of them.

USE World;
SELECT * FROM OPENJSON('["Cat","Dog","Bird"]');

Result:

+-------+---------+--------+
| key   | value   | type   |
|-------+---------+--------|
| 0     | Cat     | 1      |
| 1     | Dog     | 1      |
| 2     | Bird    | 1      |
+-------+---------+--------+

Conclusion

To conclude, our Support Engineers gave us a closer look at the cause behind Invalid object name openjson SQL server error. Additionally, we also got a look at two different ways to resolve the issue.