To add server using sp_addlinkedserver

问题:

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.

回答1:

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'

回答2:

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

回答3:

I had same issue to connect an SQL_server 2008 to an SQL_server 2016 hosted in a remote server. @Domnic 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

回答4:

FOR SQL SERVER

EXEC sp_addlinkedserver @server='servername' 

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