Linked Server Anomoly

Question

Linked Server Anomoly

DBANewbie

Hall of Fame

Points: 3095
More actions
April 6, 2011 at 6:02 am

#237454

Hi,
I want to set up a linked server connection between two internal SQL server instances located on seperate servers (instance A and instance B).
I am able to set up the linked server on instance A to instance B but if i expand the catalog tree for the linked server (instance B) i get a list of all local databases (instance A).
The same thing occurs if i set up the linked server in the opposite direction; the local databases are listed rather then the databases on the linked server.
I have other linked server connections set up on both instances and they work fine; the problem seems restircted to connections between these two servers.
Any advice on this matter would be greatfully received; please let me know if you would like me to post any code to clarify the problem.
Kind Regards.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

Sounds pretty wierd; can you script the linked server command out so we can see it? that woudl be most helpful in the diagnosis; you are of course sure you specificed the instance name when creating the linked server right?

so on my [MyDevMachine] server for example, if i wanted a simple linked server to any of two instances on that machine, i might do this:

EXEC master.dbo.sp_addlinkedserver @server = N'MYDEVMACHINE\SQLEXPRESS', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYDEVMACHINE\SQLEXPRESS', @locallogin = NULL , @useself = N'False'

EXEC master.dbo.sp_addlinkedserver @server = N'MYDEVMACHINE\SQL2005', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname = N'MYDEVMACHINE\SQL2005', @locallogin = NULL , @useself = N'False'

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

DBANewbie Hall of Fame Points: 3095 More actions · Answer 2

Interestingly, i was adding the linked server via SSMS but i have just added it via TSQL script and it has worked correctly.

I have no idea what the problem was.

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 3

DBANewbie (4/6/2011)
Interestingly, i was adding the linked server via SSMS but i have just added it via TSQL script and it has worked correctly.
I have no idea what the problem was.

The problem was likely a flaw in SSMS. There are MANY of them. I try to do pretty much every metadata operation using direct TSQL. Can be a PITA, but there are some things (like editing a table for example) that you REALLY don't want to touch the SSMS GUI for!

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

DBANewbie Hall of Fame Points: 3095 More actions · Answer 4

Thanks for your advice; i am starting to understand why many DBA's seem to favour direct TSQL coding rather then using the GUI.