I have four SQL Servers that are named in the following way:
dbs
dbs2
dbs3
dbs4
I have a table that is on dbs3 called table1 in database1. This table does not exist on the other servers. However when I run the query:
select *
from dbs.database1.dbo.table1 (or any of the database servers)
it returns the results as if I queried the existing table on dbs3. It is like the DBMS is ignoring the 4 part nameing in the query and returning the results from table on dbs3 no matter which server I try to designate in the 4 part naming convention. Any ideas what could be going on here. The servers appear in the linked servers list.
If you can make changes without breaking stuff (or if it's already broken enough in your opinion), I recommend recreating your linked servers. If your linked server is another SQL Server, you can do
exec sp_dropserver 'dbs';
exec sp_addlinkedserver 'dbs';
This creates a linked server definition with the default configuration, which is appropriate for most applications (and can still be tweaked afterwards).