CODEWITHSUNDEEP
sql-serversql-server-2008
TishyMouse
TishyMouse

Reputation: 21

sp_addlinkedserver is linking to local server not remote

I am trying to link a remote server using the following commands:

EXEC sp_addlinkedserver
@server='REMOTESERVER', @srvproduct='',@provider='SQLNCLI'

EXEC sp_addlinkedsrvlogin
@useself='FALSE', @rmtsrvname='REMOTESERVER',
@rmtuser='user', @rmtpassword='pwd'

sp_testlinkedserver REMOTESERVER;

Where REMOTESERVER is the value returned on the remote server when I execute SELECT @@Servername

All command execute successfully. However when I view the catalog for my 'linked' server (in SSMS) it is listing the tables in the local server, not the remote. Equally if I run a query

SELECT * FROM [REMOTESERVER].localdb.dbo.localtablename;

It will return a results set from the local server.

Note I have confirmed that remote access is enabled as recommended in https://blogs.msdn.microsoft.com/walzenbach/2010/04/14/how-to-enable-remote-connections-in-sql-server-2008/

Any ideas? Thanks in advance.

Upvotes: 1

Views: 759

Answers (1)

TishyMouse
TishyMouse

Reputation: 21

Solved this by adding the remote IP address to the sp_addlinkedserver command:

sp_addlinkedserver 
@server = N'REMOTESERVER', 
@srvproduct=N'SQLNCLI', 
@provider=N'SQLNCLI', 
@datasrc=N'99.99.99.99'

Upvotes: 1

Related Questions