53

I tried to insert values from one server to another server and I got the error:

Msg 7202, Level 11, State 2, Line 1 Could not find server 'SNRJDI\SLAMANAGEMENT' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

Jonathan Porter
  • 1,365
  • 7
  • 34
  • 62
Domnic
  • 5,227
  • 8
  • 26
  • 27

5 Answers5

116

I got it. It worked fine

Thank you for your help:

EXEC sp_addlinkedserver @server='Servername'

EXEC sp_addlinkedsrvlogin 'Servername', 'false', NULL, 'username', 'password@123'
SteveC
  • 15,808
  • 23
  • 102
  • 173
Domnic
  • 5,227
  • 8
  • 26
  • 27
  • 3
    For the benefit of other users, you may want to mark your response as an `Answer`. – nam Aug 07 '17 at 21:44
16

Add the linked server first with

exec sp_addlinkedserver
@server = 'SNRJDI\SLAMANAGEMENT',
@srvproduct=N'',
@provider=N'SQLNCLI'

See http://msdn.microsoft.com/en-us/library/ms190479.aspx

hkf
  • 4,440
  • 1
  • 30
  • 44
  • When I tried to execute above I got error like 'Msg 15028, Level 16, State 1, Procedure sp_MSaddserver_internal, Line 89 The server 'SNRJDI\SLAMANAGEMENT' already exists.' – Domnic Apr 19 '12 at 11:12
  • Then When I execute the Query I got another error like'Msg 18452, Level 14, State 1, Line 1 Login failed for user ''. The user is not associated with a trusted SQL Server connection.' – Domnic Apr 19 '12 at 11:13
  • How are you connecting to the server you are running from? You should be providing the server credentials as opposed to using a network service or windows logon. – hkf Apr 19 '12 at 11:17
10

I had the same issue connecting an SQL_server 2008 to an SQL_server 2016 hosted in a remote server. @Domnic 's answer didn't worked for me straightforward. I write my tweaked solution here as I think it may be useful for someone else.

An extended answer for remote IP db connections:

Step 1: Link servers

EXEC sp_addlinkedserver @server='SRV_NAME',
   @srvproduct=N'',
   @provider=N'SQLNCLI',   
   @datasrc=N'aaa.bbb.ccc.ddd';
   
EXEC sp_addlinkedsrvlogin 'SRV_NAME', 'false', NULL, 'your_remote_db_login_user', 'your_remote_db_login_password'

...where SRV_NAME is an invented name. We will use it to refer to the remote server from our queries. aaa.bbb.ccc.ddd is the ip address of the remote server hosting your SQLserver DB.

Step 2: Run your queries For instance:

SELECT * FROM [SRV_NAME].your_remote_db_name.dbo.your_table

...and that's it!

Syntax details: sp_addlinkedserver and sp_addlinkedsrvlogin

MarcM
  • 2,173
  • 22
  • 32
7

-- check if server exists in table sys.server

select * from sys.servers

-- set database security 

    EXEC sp_configure 'show advanced options', 1
    RECONFIGURE
    GO

    EXEC sp_configure 'ad hoc distributed queries', 1
    RECONFIGURE
    GO

-- add the external dbserver

EXEC sp_addlinkedserver @server='#servername#'

-- add login on external server 

EXEC sp_addlinkedsrvlogin '#Servername#', 'false', NULL, '#username#', '#password@123"'

-- control query on remote table

select top (1000) * from [#server#].[#database#].[#schema#].[#table#]
sirandy
  • 1,834
  • 5
  • 27
  • 32
wike
  • 71
  • 1
  • 1
5

FOR SQL SERVER

EXEC sp_addlinkedserver @server='servername'

No need to specify other parameters. You can go through this article.

4b0
  • 21,981
  • 30
  • 95
  • 142
Rohan Bhutani
  • 79
  • 2
  • 4
  • Apologies for the -1, looks like I clicked it by accident but noticed too late to undo. Apparently if you do any edit I'll be able to change it. – 8forty May 15 '18 at 00:39