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.