run-time error 80004005 odbc sql server driver tcp/ip sockets sql server does not exist or access denied
Friday, January 18, 2013 6:49 PM
I've got an odd issue. I have a custom VB project that runs daily to email me a list of users. It has ran fine for years and then suddenly I started getting the following error:
Run-time error '-2147467259 (800004005)':
[Microsoft][ODBC SQL Server Driver][TCP/IP Sockets]SQL Server does not exist of access is denied
Nothing has changed on the server at all. The odd thing is that once this error occurs you can reboot the server and it will run fine again anywhere from 2-5 days and then spew out the error again. Sometimes I can get away with simply restarting the MSSQLSERVER service and then it will run. There is nothing in the error logs other than the normal starting processes on blah, blah, blah port, nothing out of the ordinary. Does anyone has any suggestions on this please?
Tuesday, January 22, 2013 2:33 PM
Without more details I am gathering that this is a VB client\server application with no middle tier.
I would look in two areas right off the bat. I would check any timeout settings. I am not sure if you are using OLEDB or ADO but I know that sometimes the settings may have been changed. Next I would verify that DNS is not causing an issue.
Hopefully this will give you some areas to look at until an MVP or MSFT Eng replies.
Tuesday, January 22, 2013 3:08 PMThis application was already setup prior to me comin onboard so my details of it all are vague as I've been digging through the code. It uses OLEDB connected to an SQL database. I've verified its not DNS related, I can connect to server via Telnet from the source machine by both IP and FQDN to the SQL port I'm using. As for timeouts, the error happens about 10 seconds after launching. I did go into the SQL server and enabled named pipings and TCP/IP and its worked longer than it has been so I will see if that resolves the issue fully. It is just odd how it worked fine for years and then suddenly starting throwing out the error when nothing network related was changed.
Tuesday, January 22, 2013 3:44 PMThat is strange... with TCP/IP disabled; this shouldn't have been working at all.
Tuesday, January 22, 2013 4:24 PMTCP was never disabled, it was set for TCP/IP connections only. So I changed it to TCP/IP and Named piping connections and its working so far. Not sure what that made a difference, but I guess time will tell.
Tuesday, January 22, 2013 7:49 PM
Whats the status of SQL server it time of issue in terms of no. of sessions, blocking, CPU & memory ?
Please check & share.
This posting is provided with no warranties and confers no rights.
Please remember to click Mark as Answer and Vote as Helpful on posts that help you. This can be beneficial to other community members reading the thread.
Tuesday, January 22, 2013 8:00 PMI've checked after the job fails and I don't see anything concrete. CPU level is extremely low, memory is always around 70-75% usage but thats really it. Nothing too concerning.
Wednesday, January 23, 2013 2:23 PMThought I'd share with everyone the solution that worked for me in case it helps anyone else out (its worked for over a week which is longer than it has so hopefully this is a long term solution). I enabled named piping on the SQL server. To do this I went to Start > All Programs > Microsoft SQL Server 2005 > Configuration Tools > SQL Server Surface Area Configuration. Once that comes up at the bottom of the box select Surface Area Configuration for Services and Connections. On the next screen under Database Engine go to the Remote Connections section. There you can select Local and remote connections "Using both TCP/IP and named pipes." Again this solved my issue so far after a week so not sure if its a long term solution, but so far so good on mine.
- Marked As Answer by Billy_c Wednesday, January 23, 2013 2:23 PM
Wednesday, January 23, 2013 2:30 PM
That's good to hear. Doesn't make sense to me as to why that configuration issue was so intermittent, but we continue learning...