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.

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.

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.

Hi…. I was getting the same error. I checked my Database name in top dropdown, it was preselected correctly. Then I used the query USE [myDatabaseName] and it still dint work. Suddenly saw your post and tried ctrl+shift+R and it worked. Thanks alot

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

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.

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.

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)

Community Initiatives

About Pinal Dave

Pinal Dave is a Pluralsight Developer Evangelist. He has authored 11 SQL Server database books, 17 Pluralsight courses and have written over 3200 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 11+ years of hands on experience he holds a Masters of Science degree and a number of certifications, including MCTS, MCDBA and MCAD (.NET). His past work experiences include Technology Evangelist at Microsoft and Sr. Consultant at SolidQ. Follow @pinaldave
Send Author Pinal Dave
an email at pinal@sqlauthority.com

Email Subscription

Enter your email address to subscribe to this blog and receive notifications of new posts by email.