Connecting to another server

Hi,

I have an adp that hits a databse on an 2005SQL server (databseserver). This server needs to pull information from another table in a SQL2000 server database (DBServer). When I click on the button on a form in the adp, I get this error:

"OLE DB provider "SQLNCLI" linked server communications error"

Now the server shows up in the SQL2005 Management studio, and I can open the tables in this database on this server from the studio. Additionally, it did work until recently as we changed the sa password.

I also was able to move some databases off of the SQL2000 server and then this database failed to move; it errors out.

I do not know what I am doing and am looking for help from anyone who can help.

the error looks like the linked server between sql 2005 and 2000 is not able to communicate... just check the Linkedserver configuration and makesure it is configured properly. post thecomplete error with error no

Madhu

|||

Madhu please forgive me but I am not proficient in SQL server. I do not know how to check what you suggested. Could you give me some direction. The error message box that comes up in the ADP application say the following:

OLE DB Provider "SQLNCLI" for linked server "DBServer" returned message "Communication link failure"

Could you please specify what seerver I am to do it on also?

Thank you for your help!

|||

click on Server Objects, right click on Linked Servers and create one if you dot' have one; or right click on the one select property to edit it.

hope this can help!

|||Ok! That did in fact help. I deleted the old link and created a new one but I still get the error I mentioned in my previous post about the communication link failure.|||

OK... delete the existing linked server configuration and start afresh

Scenario

Servers : Server1 ,Server2

you want to configure Server2 as Linkedserver in SERVER1

All the below statement are to be run on Server1

(a) EXEC master.dbo.sp_addlinkedserver @.server = N'Server2', @.srvproduct=N'SQL Server'

(b) EXEC master.dbo.sp_addlinkedsrvlogin @.rmtsrvname=N'Server2',@.useself=N'False',@.locallogin=NULL,@.rmtuser=N'SomeLoginInServer2',@.rmtpassword='Password'

read more about Linked server in BOL

Madhu

|||Well this kind of worked! I can get into the server now. Problem is, that one of the tables doesn't seem to want to show up. I removed it from the ADP and now I can't get it back. All of the other linked tables show up however. Any ideas on this?|||

check the permission on this table... remotelogin should have permission on this table....

Madhu

|||I just recreated the view and it appears to be working! Thanks for the help, I really do appreciate it!