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