asked on

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?

imran_fast

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 Romere

ASKER

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

in sql server management studio itself you can run this query , new-> query ...

imran_fast

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 Romere

ASKER

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)
FROM RawMatInv INNER JOIN
(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
WHERE (RawMatInv.CostLb IS NULL) AND (RMWC.UNITCOST IS NOT NULL)

imran_fast -
That is exactly how I have the link set up currently.

Carla Romere

ASKER

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

imran_fast

try running
Openquery Command. and see if you are getting result

select * from
OPENQUERY ([sd-sql].,'SELECT *FROM table.dbo.pop10110 ')

Carla Romere

ASKER

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 Romere

ASKER

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.

ASKER CERTIFIED SOLUTION

GranMod

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial