mkellen


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.




Re: Connecting to another server

Madhu K Nair


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







Re: Connecting to another server

mkellen

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!






Re: Connecting to another server

vivi369

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!





Re: Connecting to another server

mkellen

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.



Re: Connecting to another server

Madhu K Nair

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






Re: Connecting to another server

mkellen

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



Re: Connecting to another server

Madhu K Nair

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

Madhu






Re: Connecting to another server

mkellen

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