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
Que: select query error in sql server
Answer: is this right?
select * from
(
select dbo.CreditDebit.ID as SLNO,TransactionDate,Particular,Balance,Expenses,Credit_Expenses,Debit_Expenses,Expenses_Name FROM DBO.CreditDebit
INNER JOIN DBO.CreditExpenses
on dbo.CreditDebit.ID=dbo.CreditExpenses.Credit_ID inner join
dbo.Expenses on dbo.Expenses.ID=dbo.CreditExpenses.Expenses_ID where cast(dbo.CreditDebit.TransactionDate as datetime) between ‘2016-07-07’ and ‘2016-09-07′
–and @TransactionDate) and dbo.Expenses.Expenses_Name like @Expenses+’%’)) or dbo.Expenses.Expenses_Name like @Expenses+’%’
–cast(dbo.CreditDebit.TransactionDate as datetime)
)
details
pivot(
sum(Expenses)
for Expenses_Name in([Salary_Expenses],[Electric And Lift Expenses],[Miscellaneous_Expenses],[BuildingMaintainence_Expenses])
)as pvt]
Hi Pinal,
When I try to create any table or stored procedure in a particular sql server database , i get the following error
Msg 208, Level 16, State 1, Procedure capture_events, Line 18
Invalid object name ‘SYSTEMLOGS.dbo.changelog’.
Could not find anything on google. Don’t know the actual reason for this error.
Hi Pinal,
Working on an existing database and I have to create some tables, but these table are not accessible without schema name prefix while older tables are accessible.
Kindly reply on this.
Thanks in advance.
What is the error? can you please more?
He chose the wrong database to look up his columns
Very much possible.
This explanation is Awesome. I run into same problem and this article so helpful
This comment helped me to solve an issue I had. Python was executing an embedded query fine and all of the sudden did not want to do it. Now it reads fine again, Thanks
I know, I am pretty late to the table, but what about using “schema owner” or “synonyms”. I had this problem and added the dbo user as an owner of the other user schema, and this seems to do the trick
I have been reading various articles here since 2011, and I have found more real world fixes to issues I’ve had on your site than anywhere else on the net. In addition, I’ve also learned WHY the error occurred, which meant I wasn’t doomed to repeat it. The only bad part is that the more I learn to avoid errors, the less I read your site :(
Thank you very much. You have saved me countless hours and at least one bypass surgery.
TDS
I ran into the same problem and none of the above worked unfortunately. Once a DBA more familiar with that server returned the answer was quick, case sensitive collation across the server (including MSDB)