Linked Server Setup Always Creates a Loopback

Question

Post reply

Linked Server Setup Always Creates a Loopback

dbajunior

SSCrazy

Points: 2307
More actions
April 9, 2013 at 10:38 am

#291939

I want to create a linked server to a remote server (not a loopback to the one I'm on). But for some reason, on the server I am trying to create this on, it ALWAYS creates a loopback, meaning the catalogs it shows after the creation are those that on the server I'm on - not the remote server. Odd thing is, I can created this linked server correctly on a different server, just not this one. Is there a server setting of some kind that causes this? Thanks!

Viewing 7 posts - 1 through 6 (of 6 total)

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

Lowell SSC Guru Points: 323504 More actions · Answer 1

more details are needed to really help, i guess;

maybe there's a custom entry in the hosts file:

from a commandline, notepad.exe %SystemRoot%\system32\drivers\etc\hosts

in that extension-less hosts file, maybe someone has put the servername there and pointed it to 127.0.0.1?

otherwise, i'd think we need to see the scripted create linked server command, and some details on the names/instance names of the local vs remote servers.

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!

dbajunior SSCrazy Points: 2307 More actions · Answer 2

Here's the code:

EXEC master.dbo.sp_addlinkedserver @server = N'LinkSvrName', @srvproduct=N'SQL Server'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkSvrName',@useself=N'False',@locallogin=NULL,@rmtuser=N'ReportsUser',@rmtpassword='########'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'collation compatible', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'data access', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'dist', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'pub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'rpc', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'rpc out', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'sub', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'connect timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'collation name', @optvalue=null

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'lazy schema validation', @optvalue=N'false'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'query timeout', @optvalue=N'0'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'use remote collation', @optvalue=N'true'

GO

EXEC master.dbo.sp_serveroption @server=N'LinkSvrName', @optname=N'remote proc transaction promotion', @optvalue=N'true'

GO

Lowell SSC Guru Points: 323504 More actions · Answer 3

LinkSvrName?

I know you are obfuscating this, but you didn't provide the other information I mentioned at all.

What the name of the server you are running this code on?

Is the "remote" server LinkSvrName, or LinkSvrName\SQLExpress, or actually an IP address like 192.168.1.55, or what?

could you be using SSMS and THINK you are pointing to a remote, but actually running it against local? otr vice-versa?

did you peek at the hosts file?

a linked server for me, for example is created on the server DEV223 and points to DBSQL2K5

--Executed on DEV223

EXEC master.dbo.sp_addlinkedserver @server = N'DBSQL2K5', @srvproduct=N'SQL Server'

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!

dbajunior SSCrazy Points: 2307 More actions · Answer 4

Thanks for your reply. I have been in a class most of the day.

I didn't find anything unusual in the host file. The server I am trying to create the linked server on is 'DevSql01'. The server I want to link to is 'SQLProdB'. I was actually able to create a linked server on another dev server called 'DevSql2' <-> SQLProdB....and all catalogs were available. It's just not happening on DevSql. Also, this was working fine about a week ago...so something has changed.

Mansfield SSCrazy Points: 2396 More actions · Answer 5

Also try the Sql Server Configuration Manager and see if any Aliases were created.

dbajunior SSCrazy Points: 2307 More actions · Answer 6

Wow, you made my day...that's exactly what it was! Thank you very, very much! Someone created an alias on our Dev SQL server so that whenever we reference the Prod server, it points back to Dev...grrrrr! :w00t: