SQL 2008R2 Linked Server - Can access one database but not the other?

Hi
Two servers, each with an instance of sql 2008r2.
Server 1:  HOUSQL2\INSTANCE
Server 2: HOUSQL2TEST\INSTANCE

Different SA passwords, but I created a link from server 2 to server 1, using SQL authentication.

I can run the following from server 2:

select top 100 * from [housql2\instance].database1.dbo.coss_workorder

and I get records returned.  But if I run:

select top 100 * from [housql2\instance].database2.dbo.coss_workorder

I get the error: Msg 208, Level 16, State , Line 1  Invalid object name 'database2.dbo.coss_workorder'.

The database does exist, and I can see it on server 1, and navigate to it.

Another thing that's weird, is on server 2, even if I delete the link to server 1, and can verify by running "sp_linkedservers" and confirming server 1 doesn't show up in the list, I can still run the first above command and get records.  Although there's no linked server in the list (both in the GUI and by running sp_linkedservers), I can still pull up records from the first database on the server.

Any ideas?
Mystical_IceAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

arnoldCommented:
The credentials you used to establish tge link does not have rights on the other db.  Check the server security, logins that you use for the link to make sure it has rights on the sevo do db as it does on the first.
Mystical_IceAuthor Commented:
The linked server is setup to link using sa, which has rights on all databases
arnoldCommented:
Double check that it is not simply your query is using a different server than you expected.

I.e. you have ssms with connection to both servers, you opened a new query thinking it was to server2 but it is actuallly server1.master.
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Mystical_IceAuthor Commented:
Tried all of those type potential problems.
what I ended up doing was ran both of the following commands:

select * from sys.servers

sp_LinkedServers

Then deleted all linked servers (not through the SSMS interface, but through the stored procedure for deleting linked servers).
Re-added the server, and restarted the SQL service, and all works

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL Senior EngineerCommented:
Mystical_Ice, do you still need help with this question?
Mystical_IceAuthor Commented:
No thank you. The solution I posted was the final result
Mystical_IceAuthor Commented:
This was the final resolution
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.