Linked Server Stopped Working

I have a linked server set up going from one SQL Server 2000 to another SQL Server 2000. The connection has worked fine for months, but has suddenly quit working and posting this error:

Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.
[OLE/DB provider returned message: Invalid connection string attribute]

Here is my connection string:

Data Source=sd-sql;Initial Catalog=AERT;Persist Security Info=True;User ID=sa;Password=*****

As far as I know, nothing has been changed on either server. What would cause this connection to stop working?
Carla RomereDirector of Information TechnologyAsked:
Who is Participating?
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages
Try connecting the linked server from the query analyzer then

try recreating the linked server and make sure that the sa password is correct.
and one more thins did you install any software on any of the server.

Carla RomereDirector of Information TechnologyAuthor Commented:
I've tried recreating the linked server and get the same error. I can connect directly to both servers with no problems, so I know I've got the sa passwords correct.

I'm using Sql Server Management Studio 2005 on my laptop to connect to both of the sql servers. I don't see anywhere in there to connect to Query Analyzer. Where would I find that?
Cloud Class® Course: Amazon Web Services - Basic

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

Aneesh RetnakaranDatabase AdministratorCommented:
in sql server management studio itself you can run this query , new-> query ...
in the security tab of the linked server select don't select any user  mapping
check this radio button
be made using this security context and put your sa password and userid there
Carla RomereDirector of Information TechnologyAuthor Commented:
aneeshattingal -

I ran this query:

SELECT     po.PONUMBER, po.VENDORID, po.VNDITDSC, po.UNITCOST, po.Landed_Cost_Group_ID, rm.vendname
FROM       rawmatinv rm INNER JOIN
           [sd-sql].table.dbo.pop10110 po ON po.PONUMBER = rm.ponbr
WHERE     (po.PONUMBER = 'po065983')

and got this error:

Msg 17, Level 16, State 1, Line 1
[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

I can run each of these queries and get correct results:

Query #1 on sdp_nts_001
SELECT     rm.ponbr, rm.vendname, rm.costlb
FROM       rawmatinv rm
WHERE     (rm.PONBR = 'po065983')

Query #2 on sd-sql
SELECT     rm.ponumber, rm.vendorid, rm.unitcost
FROM       pop10110 rm
WHERE     (rm.ponumber = 'po065983')

Here is the combined query that used to work:
UPDATE    RawMatInv
SET       RawMatInv.CostLb = (RMWC.UNITCOST)
                          (SELECT     PONbr, SUM(NetWeight) SumWeight
                           FROM          RawMatInv
                           GROUP BY PONbr) tb ON RawMatInv.PONbr = tb.PONbr INNER JOIN
                                       [SD-SQL-LINK].AERT.dbo.pop10110 RMWC ON RawMatInv.PONbr = RMWC.PONumber COLLATE SQL_Latin1_General_CP1_CI_AS

imran_fast -
That is exactly how I have the link set up currently.
Carla RomereDirector of Information TechnologyAuthor Commented:
Well, after exhaustive research inhouse and online, it seems there is a problem with the RPC service on one of the sql servers. IT will reboot that server tomorrow morning and we'll see if that resolves the problem...
try running
Openquery Command. and see if you are getting result

select * from
OPENQUERY ([sd-sql].,'SELECT *FROM table.dbo.pop10110 ')
Carla RomereDirector of Information TechnologyAuthor Commented:
Going directly to that server I'm not having any problems at all. The only problem is trying to access data on that server via a Linked Server on another sql server. I hope when IT reboots it this morning that it will resolve the problem...
Carla RomereDirector of Information TechnologyAuthor Commented:
Rebooting the server fixed the problem. The RPC service was hung on that server and IT rebooted it this morning and the linked server is working just as it should be.

Thanks for everyone's suggestions.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.