Linked server query pulls from same-named database on main server 3

[foxdev]

I seem to be unique with this problem, because my Google-fu isn't showing this having come up before. The gist of the problem: linked server query actually pulls data from the primary server, assumedly because db/table name is same.

The setup: SQL Server 2005 on both server "DW" and server "RPT". Both are using named instances.

The query is run from DW, from within database DW. A linked server RPT\INSTANCE has been set up, and works.

SELECT COUNT(*) FROM MYTABLE
returns 450, which is correctly the number of rows in database DW on server DW for MyTable.

SELECT COUNT(*) FROM OTHERDB.DBO.MYTABLE
returns 420, which is correctly the number of rows in database OTHERDB on the same server.

SELECT COUNT(*) FROM [RPT\INSTANCE].OTHERDB.DBO.MYTABLE
returns 420, which is incorrect.

After much trial-and-error (and with real data pulls, not just counts), I've found that even though I've specified [RPT\INSTANCE], and the query runs without error, I am getting results from the same database name, same table name on the primary server.

I've dropped and rebuilt the linked server, tried synonyms; no joy.

Renaming one of the databases is not desirable, as it will break quite a few things. Any suggestions on what else to check for/try would be appreciated.


--------------

http://www.liquidsql.com

SQLS metasearch

 

[gmmastros]

This is a long shot, but it's quick to check.

From the server....

Click Start -> Run
Type: CLICONFG
Click OK

Click the Alias tab. Is there anything in there?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

when you created the linked server did you perhaps by mistake used the local server in the connectionstring?

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[ESquared]

select * from master.dbo.sysservers

 

[foxdev]

gmmastros: no aliases configured, but 10 points for something I wouldn't have thought of.

SQLDenis: there is no connection string specified; I simply chose the SQL Server option, which disables all connection-related boxes.

Esquared: that and sp_linkedservers shows what I would expect. When I deleted the linked server (before recreating), I ran sp_linkedservers to make sure it wasn't showing. This is important, I think, because at one time there MAY have been a remote server configured with the same name (been there, done that on a different server).

--------------

http://www.liquidsql.com

SQLS metasearch

 

[SQLDenis]

>>there is no connection string specified; I simply chose the SQL Server option, which disables all connection-related boxes.

What i meant was the @datasrc = ] 'data_source' ]
this has to be the network name of the server, is it possible you will have to use IP can the name be resolved?


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[foxdev]

Hi, Denis; thanks for hanging in with this.

I used the UI to create the linked server. SP_linkedservers shows:
SRV_PROVIDERNAME: SQLNCLI
SRV_DATASOURCE: RPT\INSTANCE
SRV_PROVIDERSTRING: (null)

SOME POSSIBLY IMPORTANT ADDITIONAL INFO

* the same query against yet another linkedserver with the same database and table name correctly returns rows from that server/database/table.

* there is a linkedserver "RPT" (default instance) also on DW. However, I tried deleting that linkedserver and re-running the queries, and no difference, so I don't think that is interfering in any way.

Thanks to all who have responded thus far. These are production systems, so we don't have a lot of flexibility of choice for workarounds. We use linked servers a LOT, but for some odd reason, only this server pair/direction has this issue.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[ESquared]

Does this work?

select *
from openquery ([RPT\INSTANCE], 'SELECT COUNT(*) FROM OTHERDB.DBO.MYTABLE') x

 

[foxdev]

ESquared, openquery works!
That can be a fall-back work-around.

One more bit of bizarre: using a linkedserver query (without openquery) on the same-named database and table on a third, different server...works fine!

So, it seems to be something specific about DW not seeing RPT properly. If I try to linkedserver-query a database on RPT that does not exist on DW, I get an "object not found" error.

Denis, I will try using IP to see if that works.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[foxdev]

Congratulations to ESQuared and SQLDenis for two effective and practical work-arounds: openquery and using IP address\instance name rather than servername\instance name. Both work, although the IP address is easier to implement (the queries involved are moderately complex).

Unfortunately, the core problem remains: why isn't DW properly seeing RPT under some circumstances. IOW, it works with OpenQuery, and it pings the correct server, but with a regular linkedserver query it does not.

There are no entries in the server's HOSTS file. I'll follow-up with the network guy to see if we can find the root cause, but for now...we're operational.

Thanks again to EVERYONE, even if your guess wasn't the answer.

--------------

http://www.liquidsql.com

SQLS metasearch

 

[gmmastros]

I wonder if it's not a DNS problem.

From a command prompt...

[tt][blue]ipconfig /displaydns[/blue][/tt]

Does the correct IP Address show for the server?

You could also try pinging the server name. Does it return the correct IP Address?

[tt][blue]ping ServerName[/blue][/tt]

You may want to try:

[tt][blue]ipconfig /flushdns[/blue][/tt]

Truth is, I don't know alot about DNS, but I suspect that is your problem.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[SQLDenis]

some SQL boxes can't resolve DSN because all stuff has been turned off

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[LNBruno]

Not sure this is going to add any value, but when I recently started working from home, I discovered that our company's set up allows me to execute against servername\instance name ONLY when I am on the local network. When working remotely (VPN), I HAVE to use the IP address.

< M!ke >
[small]Where are we going and why am I in this handbasket?[/small]