Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jun 2004
    Posts
    13

    Question Unanswered: I cant solve this linked server problem, can you?

    We rebooted our main SQL Server (SQL2k). It had 5 linked servers setup, 2 on the same network and 3 on another network. After the reboot the linked sql servers on the other network only work some of the time when trying to access them via a linked server. The 2 linked servers on the same network have no problems at all. All the routes are setup properly and are persistant.

    Linked servers
    SameNetwork1 -- No Problems
    SameNetwork2 -- No Problems
    ServerOtherNetwork1 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
    ServerOtherNetwork2 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
    ServerOtherNetwork3 -- "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."

    All SQL Server 2k Standard with 2 processor licenses and the latest service packs.
    All have been setup as TCPIP and the port has been defined in the client network utility using the IP address not the name.
    All servers can be "pinged" and you can pull up the physical drive folders from across the networks with no problems.
    All linked servers are running windows 2000. The main server is running NT.

    After the reboot only server1 was working with the linked server. The other 2 servers were getting either a timeout or "sqlserver does not exist or access is denied. [SQLSTATE 42000] (Error 17)."
    I tried to fiddle with the linked servers by changing the passwords and got Server1 and Server2 working over the linked server connection but could not get server3 to work. After about 30 minutes or so I noticed that server 2 was working on and off. It was not consistant about failing or working. I decided to go home for the night. When I came in this morning, only Server2 works. Server1 and 3 do not at all.

    I have a job setup to pull transactions from a table on server2 to our main sqlserver. This job was running every 5 minutes and it was failing here and there. I have now setup the job to run every 2 minutes and it has not failed for the past hour. When it does fail it gives the error above (server does not exist).

    I really hope this makes sence to someone... lol


    Thanks.

  2. #2
    Join Date
    Jun 2004
    Posts
    13
    No takers on fixing this?

  3. #3
    Join Date
    Jun 2004
    Posts
    13
    Since the reboot, I have noticed that we are getting quite a bit of DEMO 19 errors for locking. We never really had this before. Could the locking issues be related to the linked server connection errors?

  4. #4
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    When you are saying "different networks", do you mean different subnets or different domains or both?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  5. #5
    Join Date
    Jun 2004
    Posts
    13
    Quote Originally Posted by rdjabarov
    When you are saying "different networks", do you mean different subnets or different domains or both?
    When I asked if they were on different subnets our admin said yes then replied with this: "Physically the networks are connected but logically they are not."

    I know that our main SQL Server is on a network that has a PDC. The other 3 servers that I need the linked server setup with are on a network that does not have a domain. I know that I have a persistent route setup between my dev box and they network that those 3 are on and there is also a route setup between the main sql server and the network with those 3 servers.

    Does all that help?

  6. #6
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    This may be a prob. I've never used SQL in workgroup environment. How did you set up login mapping?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    I am sure you have already tried this, but what happens when you try to connect to the remote servers from the main server using Query Analyzer? Be sure to use the same address/credentials as the linked server would be tying to use. Do you get any errors, or connection problems there?

  8. #8
    Join Date
    Jun 2004
    Posts
    13
    Here is another thing I just noticed. The job that runs every 2 minutes that works fine with one linked server starts failing when I attempt to setup a linked server to one of the other 2 servers on that network. Then to make it work again I do a select from of the tables, otherwise the job continues to fail.

    to explain a little differently:
    - The Server1 linked server is working and has a job that runs against it.
    - I attempt to setup the Server2 linked server and it will wait for 60 seconds or so to time out with the error "sqlserver does not exist or access is denied"
    - While waiting for the timeout on the setup of Server2 the Server1 linked server looses connectivity with the error "sqlserver does not exist or access is denied".
    - Once the error occurs then I can use the Server1 linked server again if I do a select from of the tables, otherwise the job continues to fail.

    The net admin assures me that its not his problem. He also said there there is no way that the switches or routers or whatever are overloaded.

    I can connect to all these boxes with no problem from my dev box and have all the linked servers working with no problem all at the same time. So more and more I think this problem is specific to our main sql server and not to anything outside of it, but who knows?

  9. #9
    Join Date
    Jun 2004
    Posts
    13
    Quote Originally Posted by MCrowley
    I am sure you have already tried this, but what happens when you try to connect to the remote servers from the main server using Query Analyzer? Be sure to use the same address/credentials as the linked server would be tying to use. Do you get any errors, or connection problems there?
    Well I have no problem from my dev box. But trying to connect in query analyzer from the main sql server to the other servers on the other network does not work, to my knowledge; but it can ping the servers with no problems.

  10. #10
    Join Date
    Jun 2004
    Posts
    13
    Quote Originally Posted by rdjabarov
    This may be a prob. I've never used SQL in workgroup environment. How did you set up login mapping?
    Mapping was setup with the bottom setting: "Be made using this security context"

    Then I typed the SQL authenticated login and password.

  11. #11
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    We may be on to something here. I assume it is giving you the same error "SQL Server does not exist or access is denied"? If so, then something about the client setup on the main server is not quite right. Check over the aliases in the Client Network Utility. Are they still valid?

  12. #12
    Join Date
    Jun 2004
    Posts
    13
    Quote Originally Posted by MCrowley
    We may be on to something here. I assume it is giving you the same error "SQL Server does not exist or access is denied"? If so, then something about the client setup on the main server is not quite right. Check over the aliases in the Client Network Utility. Are they still valid?
    Yup. Its all standard stuff. port 1433. TCP/IP and Named Pipes. I am using the IP address in the client config because you cant ping the server names.

  13. #13
    Join Date
    Jun 2004
    Posts
    13
    All out of suggestions?

    Perhaps, its just time to FDISK and reformat the production SQL Server.

  14. #14
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Sorry, man. I am not as good with cross-domain problems. I am used to single domain stuff, which is a heck of a lot easier.

    That brings up a question, which may have been answered before. How are you authenticating to the remote servers? By SQL Authentication, or by Windows Authentication?

    In it simplest form, if you can use Query Analyzer from your main SQL server to get to the remote servers, you should be able to use the linked servers. Not sure if that helps, but it may give you an extra tool to generate errors with.

  15. #15
    Join Date
    Jun 2004
    Posts
    13
    Quote Originally Posted by MCrowley
    Sorry, man. I am not as good with cross-domain problems. I am used to single domain stuff, which is a heck of a lot easier.

    That brings up a question, which may have been answered before. How are you authenticating to the remote servers? By SQL Authentication, or by Windows Authentication?

    In it simplest form, if you can use Query Analyzer from your main SQL server to get to the remote servers, you should be able to use the linked servers. Not sure if that helps, but it may give you an extra tool to generate errors with.
    SQL Authentication. I have problems in SQL QA too. I just cant seem to get the connectivity from that specific box to those boxes. I can get it to work on any other boxes so I think the problem is with our main sql server itself.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •