0

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
smsranger
  • 3
  • 1
  • 1
    What does `select name, data_source from sys.servers` produce? – Jeroen Mostert Oct 22 '14 at 14:28
  • 1
    Did you verify that the 4 linked servers actually link to different servers? – Dan Bracuk Oct 22 '14 at 14:29
  • It returned: DBS NULL DBS2 NULL DBS3 DBS3 DBS4 NULL – smsranger Oct 22 '14 at 14:30
  • Recheck your linked server definitions, recreate them if possible. Apparently only DBS3 is configured correctly. I have never seen a linked server with an empty `data_source`, so that's quite interesting. – Jeroen Mostert Oct 22 '14 at 14:34
  • DBS3 is the one I am logged onto and using SSMS. Its in the linked server definition. The linked server appears to be created but all the tables listed are the same ones as on DBS3 – smsranger Oct 22 '14 at 15:01

1 Answers1

0

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).

Jeroen Mostert
  • 27,176
  • 2
  • 52
  • 85