Error number 208 state 1 class 16

Problem

It administrator runs an import from staging tables function, by running the following:
exec dbo.usp_triggerimportbatchjobs

An error appears.

Symptom

Msg 208, Level 16, State 1, Procedure usp_triggerimportbatchjobs, Line 44 Invalid object name ‘zspec’

Cause

The SQL login used in the stored procedure (for example ‘dbo’) cannot read the data inside the Controller database table «zspec».

Environment

Invalid SQL login configuration, relating to the owners of both the database tables and also the stored procedure.

Example

• Imagine a scenario where the Controller database tables owned by a SQL user called ‘controller‘
• Controller database stored procedures owned by user ‘dbo‘

For a visual description of this scenario, see below:

This problem occurs if the owner of ‘tables’ and ‘views’ and ‘stored procedures’ are not the same user.

• In other words, this Technote is caused by the owner of ‘tables’ not being the same as ‘stored procedures’.

Diagnosing The Problem

The following steps are based on SQL 2005:
1. Log onto the SQL Server as an administrator
2. Launch «SQL Server Management Studio»
3. Locate the Controller database, and expand ‘Tables’
4. Make a note of the owner (for example ‘fastnet’ or ‘dbo’) of these tables.

• TIP: The owner is the first word in the table name *before* the full-stop (.)
• For example, table ‘dbo.CRDARCRES’ is owned by ‘dbo’.

5. Expand ‘Views’ and make a note of the owner of the owner
6. Expand ‘Programmability — Stored Procedures’ and make a note of the owner of the owner.

Resolving The Problem

Ensure that all Controller database tables, stored procedures and views are owned by the same SQL login (for example ‘fastnet’ or ‘controller’ or whatever).

Steps:

WARNING: Before continuing, as a precaution please ensure the database has been backed up.

NOTE:

• The following method generates SQL statements which contain pre-formatted sp_changeobjectowner scripts for each user table, stored procedure and view in the database
• The method then executes these scripts to change the owner
• The following instructions are based on SQL 2005 and 2008.
• In the unlikely event you are using SQL 2000, see separate IBM Technote #1335618.
• Please change all references to ‘username_to_be_used‘ to the new object owner (for example ‘controller‘ in Scenario #1 above)
• To be clear username_to_be_used is preceded by two single quotes and then three single quotes.

** PART ONE

: Creating a new schema

Let us assume that you want to use a SQL login called «cognos».

• Naturally, please change the instructions your your preferrred SQL login.

1. Ensure no users are in Controller
2. Log onto the SQL Server as an administrator
3. Launch «SQL Server Management Studio»
4. Right-click on the Controller database, and choose Expand the section ‘Security’
5. Notice how there is a user called «cognos», but there is NOT a schema called «cognos»:

6. Right-click on ‘Schemas’ and choose ‘New Schema’
7. Fill in both the ‘Schema name’ and ‘Schema owner’ sections with ‘cognos’ (or the name of your SQL login), and then click OK
8. It should look similar to this now:


TIP: If you do not perform the above steps, then when you try the steps inside ‘part two’ you will get the following error message:

Msg 15411, Level 11, State 1, Procedure sp_changeobjectowner, Line 111
Database principal or schema ‘cognos’ does not exist in this database.

9. Open «Users» and double-click on «cognos«
10. Change the «default schema» from the current (dbo) to «cognos«:

11. Finally, to remove orphaned users, right-click on the Controller database, and choose ‘New Query’
12. Copy the following into the blank/white window.

EXECUTE sp_change_users_login ‘Update_One’, ‘cognos‘, ‘cognos‘
GO

Again, remember to change *both* entries of «cognos» to whatever your SQL login is called.

13. Click «Execute«.

** PART TWO

: Changing the database

table

owner

1. Right-click on the Controller database, and choose ‘New Query’
2. Copy and paste the following text into the blank/white window:

select ‘exec sp_changeobjectowner [‘ + rtrim(c.name) + ‘.’ + rtrim(a.name) + ‘],
»username_to_be_used»’
from sysobjects a, sysusers c where a.type = ‘U’ and a.uid = c.uid

IMPORTANT: Make sure that you have changed the entry username_to_be_used to be your SQL login (for example cognos)

3. Click «Execute». You will see many new entries appear inside the ‘results’ Window.
4. Delete the text (SQL statements) from the Window that we have been using (so it is now blank again)
5. Right-click on the ‘results’ Windows and choose select all (to highlight all items in the results grid)
6. Right-click and choose copy, to copy all of the the resulting text (from the results grid) and then paste it into the «query» Windows
7. Click «Execute» (to run all these multiple scripts).

** PART THREE

: Changing the database

stored procedure

owner

1. Right-click on the Controller database, and choose ‘New Query’
2. Copy and paste the following text into the blank/white window:

select ‘exec sp_changeobjectowner [‘ + rtrim(c.name) + ‘.’ + rtrim(a.name) + ‘],
»username_to_be_used»’
from sysobjects a, sysusers c where a.type = ‘P’ and a.uid = c.uid

IMPORTANT: Make sure that you have changed the entry username_to_be_used to be your SQL login (for example cognos)

3. Click «Execute». You will see many new entries appear inside the ‘results’ Window.
4. Delete the text (SQL statements) from the Window that we have been using (so it is now blank again)
5. Right-click on the ‘results’ Windows and choose select all (to highlight all items in the results grid)
6. Right-click and choose copy, to copy all of the the resulting text (from the results grid) and then paste it into the «query» Windows
7. Click «Execute» (to run all these multiple scripts).

** PART FOUR : Changing the database view owner

1. Right-click on the Controller database, and choose ‘New Query’
2. Copy and paste the following text into the blank/white window:

select ‘exec sp_changeobjectowner [‘ + rtrim(c.name) + ‘.’ + rtrim(a.name) + ‘],
»username_to_be_used»’
from sysobjects a, sysusers c where a.type = ‘V’ and a.uid = c.uid

Make sure that you have changed the entry username_to_be_used to be your SQL login (for example cognos)

3. Click «Execute». You will see many new entries appear inside the ‘results’ Window.
4. Delete the text (SQL statements) from the Window that we have been using (so it is now blank again)
5. Right-click on the ‘results’ Windows and choose select all (to highlight all items in the results grid)
6. Right-click and choose copy, to copy all of the the resulting text (from the results grid) and then paste it into the «query» Windows
7. Click «Execute» (to run all these multiple scripts).

In this article we explain about ERROR 208, why it occurs and how to deal with it.

We often come across situations which seem to be very complicated and challenging but are actually very easy to get out of; provided we think logically. One such situation a lot of SQL Server DBA’s come across is the SSMS error 208, i.e. the ‘Invalid Object Name’ error. This is a very commonly occurring error which ends up confusing a lot of DBAs. So today, we will not only provide you with a solution to this problem, but also suggest you ways to avoid it from occurring in future.

What is Error 208 in SSMS

A lot of us or those DBAs who are not very experienced with using SQL Server, usually opt for exploring the software with the help of SQL Server Management Studio (SSMS). And while you are at it, you might click at a given object to know more about it, and suddenly come across a pop up stating ERROR 208! This is a point where a lot of you might get stuck. But after reading this article we hope things would get better as you would get a greater understanding of the subject.

What is most perplexing about this error to majority of DBAs is the fact that the object is visible to them in SSMS Object Explorer window but when they attempt to select it using T- SQL, they witness the error. This is the point where a lot of DBAs switch to extreme and desperate measures like restarting SSMS multiple times to furiously refreshing queries, restarting databases, etc. But to their sorrow, none of this will provide them with the solution they are looking for.

These practices fail to work because none of these is addressing the root cause of the error which is nothing but simple mismatch in database context.

How to Solve Error 208 in SSMS

Now that you know what is causing the error, finding the solution should not take long. The most common reason for this context mismatching error to occur is the fact that the default database context has been set to master. Well in 99 out of 100 cases, this ends up being the cause. And if this really is what is causing the error to appear in your system, then a very basic thing for you to do is changing the context of the database. And while you are at it, change it in a way that the context becomes similar to the current database object that is in use.

Once you perform this step, you will be glad to see that the error will appear no longer, and all other functions will also happen smoothly. This is one of those errors that almost every single DBA would have experienced, but few would actually know a way out of this situation without considering a repair sql action. This article would hopefully be of help to all of them. Apart from the solution given above, there is one more option that DBAs can try out; this involves prefixing multipart database name to the database that is in use. Or you might also want to change the database that opens by default once you login SSMS.

Author Introduction:

Victor Simon is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including corrupted mdb and sql recovery software products. For more information visit https://www.datanumen.com/

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.

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.

Example of Error

Here’s an example of some basic code that will cause this error.

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

Result:

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

When your database compatibility level is lower than 130, SQL Server can’t find and run the OPENJSON() function.

In my case, the database I was trying to run this on had a compatibility level of 120.

Check the Compatibility Level of the Database

You can query sys.databases to check the compatibility level of the database (or all databases if you prefer).

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      |
+-------+---------+--------+