To add server using sp addlinkedserver

Question

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

User · Answer

-- 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

User · Answer

I had  the 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

User · Answer

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

User · Answer

FOR SQL SERVER  EXEC sp addlinkedserver  server  servername     No need to specify other parameters  You can go through this article

User · Answer

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

[sql-server] To add server using sp_addlinkedserver

Examples related to sql-server

Examples related to tsql