[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Linked Server Stopped Working

Posted on 2006-04-17
Medium Priority
Last Modified: 2012-08-14
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?
Question by:Carla Romere
LVL 28

Expert Comment

ID: 16470412
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.


Author Comment

by:Carla Romere
ID: 16470463
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?
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 16470586
in sql server management studio itself you can run this query , new-> query ...
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

LVL 28

Expert Comment

ID: 16470594
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

Author Comment

by:Carla Romere
ID: 16470833
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.

Author Comment

by:Carla Romere
ID: 16472657
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...
LVL 28

Expert Comment

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

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

Author Comment

by:Carla Romere
ID: 16477273
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...

Author Comment

by:Carla Romere
ID: 16478875
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.

Accepted Solution

GranMod earned 0 total points
ID: 16516791
Closed, 500 points refunded.
The Experts Exchange
Community Support Moderator of all Ages

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question