Linked Server Stopped Working

Posted on 2006-04-17
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:Hers2keep
    LVL 28

    Expert Comment

    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

    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
    in sql server management studio itself you can run this query , new-> query ...
    LVL 28

    Expert Comment

    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

    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

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

    Author Comment

    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

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

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

    Author Comment

    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

    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

    Closed, 500 points refunded.
    The Experts Exchange
    Community Support Moderator of all Ages

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Title # Comments Views Activity
    Database restore 8 42
    SQL queries to include Yes or No for the outputresults 3 31
    Help with SQL joins 9 32
    C# primary key 9 46
    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.

    728 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now