Results 1 to 3 of 3

Thread: linked servers

  1. #1
    Join Date
    Feb 2005
    Posts
    76

    Unanswered: linked servers

    Hello (again!)

    We have a new 2005 server, and I am trying to link our old 2000 server so I can use the old databases in new server.

    This is what I done:
    In 2005:
    EXEC sp_addlinkedserver oldServer
    --completed fine
    then:
    EXEC sp_addlinkedsrvlogin oldServer, 'false', NULL, 'Administrator', password
    --completed fine
    But whenI run the query I get the error:

    OLE DB provider "SQLNCLI" for linked server "oldServer" returned message "Communication link failure".
    Msg 10054, Level 16, State 1, Line 0
    TCP Provider: An existing connection was forcibly closed by the remote host.
    Msg 18456, Level 14, State 1, Line 0
    Login failed for user 'Administrator'.

    Any ideas????

  2. #2
    Join Date
    Mar 2003
    Posts
    144

    Lightbulb

    I would recommend to use the Management Studio in order to create the Linked Server.

    -Press Right-Click on the node \YourServer\Server Objects\Linked Servers and choose "New Linked Server"
    -On the dialog, write the OldServer name in the "Linked server" textbox.
    -On the "Server type" option button, choose "SQL Server".
    -In then "Security" page, you have to configure how the local server is going to login to the remote server. If you use Windows Authentication, and your user has permissions in the remote server, you can choose "Be made using the login's current security context". But if you want to specify a remote user and password (SQL Server Authentication), you can write that in "Be made using this security context".
    -In the "Server options" page, make the RPC properties to TRUE if you want to invoke remote stored procedures.

  3. #3
    Join Date
    Dec 2002
    Posts
    1,245
    I just created a linked server on SQL 2005 point to a SQL 2000 box.

    Then I scripted it off; here are the results. Note that I accepted all default options and used a specific user on the remote server.

    YMMV

    [code]

    /****** Object: LinkedServer [REMOTESERVER] Script Date: 04/12/2007 16:58:55 ******/
    EXEC master.dbo.sp_addlinkedserver @server = N'REMOTESERVER', @srvproduct=N'REMOTESERVER', @provider=N'SQLNCLI', @datasrc=N'REMOTESERVER'

    /* For security reasons the linked server remote logins password is changed with ######## */
    EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'REMOTESERVER',@useself=N'False',@loc allogin=NULL,@rmtuser=N'remote_user',@rmtpassword= '########'

    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'collation compatible', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'data access', @optvalue=N'true'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'dist', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'pub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'rpc', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'rpc out', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'sub', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'connect timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'collation name', @optvalue=null
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'lazy schema validation', @optvalue=N'false'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'query timeout', @optvalue=N'0'
    GO
    EXEC master.dbo.sp_serveroption @server=N'REMOTESERVER', @optname=N'use remote collation', @optvalue=N'true'

    [code]


    Regards,

    hmscott
    Last edited by hmscott; 04-12-07 at 18:05.
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •