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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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?
Aneesh RetnakaranDatabase AdministratorCommented:
in sql server management studio itself you can run this query , new-> query ...
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

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.
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.