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 :o
Thanks.No takers on fixing this? :(|||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? :confused:|||When you are saying "different networks", do you mean different subnets or different domains or both?|||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." :D
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?|||This may be a prob. I've never used SQL in workgroup environment. How did you set up login mapping?|||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?|||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? :(|||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.|||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.|||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?|||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.|||All out of suggestions? :(
Perhaps, its just time to FDISK and reformat the production SQL Server. :D|||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.|||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.|||have you tried using the IP address in the link server setup?
just a thought...it sounds like you've aliased it already in your client network config, but it's something i would try just for giggles, were i in this position.
cheers!|||have you tried using the IP address in the link server setup?
just a thought...it sounds like you've aliased it already in your client network config, but it's something i would try just for giggles, were i in this position.
cheers!Ye fish and little cat-gods! That sounds like something that I'd write. Are you sure that you aren't me?
-PatP|||Just to check, the network guys are not blocking port 1433 or port 1434, by chance, are they? If they have had problems with SQL Slammer, some network groups have done this in the past.|||given the nature of time and space i'm not entirely sure we are aren't all a cruel reflection of some divine dick-n-fart joke.
actually,come to think, the vedic (vastly ancient spiritual perspective from india) says there are only 49 souls and because what we call 'time' is actually an illusion that the vast majority of us are actually the same person.
so...could be.
:eek:
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment