Just a day before I got a very interesting email. Here is the email (modified a bit to make it relevant to this blog post).
“Pinal,
We are facing a very strange issue.
One of our query  related to backup files and backup set has stopped working suddenly in SSMS. It works fine in application where we have and in the stored procedure but when we have it in our SSMS it gives following error.
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘dbo.backupfile’.
Here are our queries which we are trying to execute.
SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;
This query gives us details related to backupset and backup files when the backup was taken.”
When I receive this kind of email, usually I have no answers directly. The claim that it works in stored procedure and in application but not in SSMS gives me no real data. I have requested him to very first check following two things:
- If he is connected to correct server? His answer was yes.
- If he has enough permissions? His answer was he was logged in as an admin.
This means there was something more to it and I requested him to send me a screenshot of the his SSMS. He promptly sends that to me and as soon as I receive the screen shot I knew what was going on.
Before I say anything take a look at the screenshot yourself and see if you can figure out why his queries are not working in SSMS. Just to make your life a bit easy, I have already given a hint in the image.
The answer is very simple, the context of the database is master database. To execute above two queries the context of the database has to be msdb. Tables backupset and backupfile belong to the database msdb only.
Here are two workaround or solution to above problem:
1) Change context to MSDB
Above two queries when they will run as following they will not error out and will give the accurate desired result.
USE msdb
GO
SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM dbo.backupfile;
2) Prefix the query with msdb
There are cases above script used in stored procedure or part of big query, it is not possible to change the context of the whole query to any specific database. Use three part naming convention and prefix them with msdb.
SELECT name, database_name, backup_size, TYPE,
compatibility_level, backup_set_id
FROM msdb.dbo.backupset;
SELECT logical_name, backup_size, file_type
FROM msdb.dbo.backupfile;
Very simple solution but sometime keeps people wondering for an answer.
Reference:Â Pinal Dave (https://blog.sqlauthority.com)
64 Comments. Leave new
Hi, Pinal
You saved me many hours! “2) Prefix the query with msdb” and “Use three part naming convention”!
thanks a lot!
Frank Wang
HI,
when i am doing Report server configuration for the existing database am getting this error.
ERROR: The database you have selected is not Report server database.Please select a SQL Server database that contains report server specific tables and stored procedures and try again.
can anyone help on this issue.
Thanks ,
Mallikharjuna
was facing same error tanks for the solution…..
Nice, your last tip made me try Customer.dbo.CUSTOMER instead of dbo.CUSTOMER and IT WORKED!!
in my case I was using generic sql to test my connection/jndi setup etc and for everyone’s edification the simple code:
SELECT * FROM Customer.dbo.CUSTOMER
Results
Zip: ${row.Zip}
Hi I have similar issue. Please see below my query
IF OBJECT_ID(N’tempdb..#lastweek’, N’U’) IS NOT NULL
DROP TABLE #lastweek;
declare @sql varchar(max)
create table #lastweek
(
[MY ID] varchar(15)
,NAME varchar(100)
,[EMMISSION Level]varchar(100)
,[Project]varchar(100)
,[LAST TOOL] varchar(100)
,[TOOLS Actual Date] Datetime
,[IMPLEMENTATION Actual Date] Datetime
)
set @sql=(‘insert into #mdslastweek select [MY ID], NAME, [EMMISION LEVEL], [Project],
[Last TOOLS], [TOOLS Actual Date], [IMPLEMENTATION Actual Date] from DWH_Data_CARS.dbo.tbl_CAR_TOOLS_’
+CONVERT(varchar(4),datepart(year,getdate()))
+case when datepart(month,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) else
+CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) end
+case when datepart(d,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) else
+CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) end)
I want to select from this "DWH_Data_CARS.dbo.tbl_CAR_TOOLS_20140609 which is the last week of today's data populated on that last week date.
That is why I have this query
FROM DWH_Data_Integrity.dbo.tbl_NTP3_MDS_'
+CONVERT(varchar(4),datepart(year,getdate()))
+case when datepart(month,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) else
+CONVERT(varchar(2),datepart(month,dateadd(d,-1,getdate()))) end
+case when datepart(d,dateadd(d,-1,getdate())) <10 then '0'+ CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) else
+CONVERT(varchar(2),datepart(d,dateadd(d,-1,getdate()))) end)
but now I am getting the following error: Msg 208, Level 16, State 1, Line 1
Invalid object name 'DWH_Data_CARS.dbo.tbl_CAR_TOOLS_20140615'.
I need help urgently thanks
Hi
Thanks a lot i have the same issue too, My question is why can not we use ‘Master’ context, can you give more explanation on that please.
Thanks
J
Janice,
Every table is identified with something called 4 part naming. ServerName.DatabseName.SchemaName.ObjectName. If first three are not defined, they default to current server, current database, default schema. That’s why if we give “dbo.backupset” in master database context, its assumed as master.dbo.backupset and searched under master database. Hence error.
As I said, you can use context of master database by using msdb.dbo.backupset
Thank you greatly..
I am glad that it helped you @Hawks2014
Invalid object name with link server . Query is working in my pc but when i try to run on client pc it gives error invalid object name . It is executed for the firsttime but when it try to insert data from the second table it gives this error can you please help me to resolve this.
i can resolve this by edit ->intelisence ->refresh local cahe or simply ctrl+shift+R
but i don’t need to do manually so can anybody help me to resolve thes.
@NetDev – I am unable to understand the situation. Do you have repro to share?
This is a good solution for queries running on database directly. But how do we know in our application which database and schema name to choose? I means dynamic native queries which we can use in our application.
You are for me a smashing star :)
I found my solution in 2 minutes and learned something new.
Thanks
Jan
Thanks for search engines for honoring my blogs and getting you to solution quicker.
Hi
Can any one help me?
I am trying to execute a simple update query
update xyz set abc=’dddd’ where aaa=’bbb’
still getting the error Msg 208, Level 16, State 1, Line 1
Invalid object name
check table, database, case sensitivity.
HI, i am facing same issue.
table name is correct, correct database is selected but still stored procedure gives “Invalid object name” error.
if i run query separately, select * from [tablename] , it works fine
only if i use in particular stored procedure, it gives error
can anyone help?
Is there any special character in the table name? or by any chance the table name is case-sensitive?
Sorry my mistake. that table does not work if i don’t use schema name with it. So now it is working in stored procedure with schema name
Thanks for the update Prajakta.
Hi Pinal, i have similar kind of case and i am actually not able to find exact cause of this issue.
Please help !
============================================
USE HR_Employee
GO
DECLARE @SQLQuery AS NVARCHAR(MAX)
DECLARE @PivotColumns AS NVARCHAR(MAX)
–Get unique values of pivot column
SELECT @PivotColumns= COALESCE(@PivotColumns + ‘,’,”) + QUOTENAME(ADA_DATE)
FROM (SELECT DISTINCT ADA_DATE FROM WrkDailyAttend_Web) AS PivotExample
SELECT @PivotColumns
–Create the dynamic query with all the values for
–pivot column at runtime
SET @SQLQuery = N’SELECT EMP_ID, ‘ + @PivotColumns + ‘
FROM [HR_Employee].[dbo].[PivotExample]
PIVOT (MAX(TIME_IN) FOR ADA_DATE IN (‘ + @PivotColumns + ‘)) AS P’
SELECT @SQLQuery
–Execute dynamic query
EXEC sp_executesql @SQLQuery
======================================================
Error message :
Msg 208, Level 16, State 1, Line 1
Invalid object name ‘HR_Employee.dbo.PivotExample’.
Can you verify the existence of that table?
hi sir i have one doubt.
if i open sql server with my local credentials it will open with sql server agent.but in case of online credentials the sql server agent is not appeared.sir plz explain me y this happened
Hello Sir,
I am trying to output the query result into the file.The out file has this error stated
“Msg 208, Level 16, State 1, Server ISD-4QS8HX1, Line 1
Invalid object name ‘WM_GENERAL.dbo.AWS_TRANSMISSION’.”
My query is select * from WM_GENERAL.dbo.AWS_TRANSMISSION;
However,I can see the rows and columns in SQL server management studio.
I can write result of below query intot he file
SELECT “ACKNOWLEDGEMENT REPORT” = CAST(GETDATE() AS DATE),convert(varchar(5), GETDATE(), 108);
Please help me.I have to write all the query results to the output file for my project.
Thank you for your article – it was very helpful to resolve my issue.
Saw my error as soon as I read – ‘I’ve already given a hint in the image.’ I forget to check that sometimes. Always bites me! Thanks!!!!!!
When i run the exe file come this error plz help– could not get database from database ‘invalid object name aa_setup
When i run the exe file come this error plz help– could not get database from database ‘r’invalid object name aa_setup
Great Helped me solved problem