January 27th, 2004, 05:35 AM
SQL Connectivity- has MS bodged it up?
Hi, I've recently started having a problem with a large(ish) SQL database I run. It's been running fine up to now - albeit a bit slow, but never-the-less fine. I've been doing some optimisation on it, as one of the tables has around 3 million rows, and I have to search it pretty quick (ideally inside 3 seconds). Anways - that's just background.
I installed the latest Microsoft auto-update on the SQL server a couple of days ago (and also on the webserver, which is a seperate machine), which was a security patch for MDAC. Since then, I've been getting problems saying SQL Server can't be found - but not all the time. I can't see a pattern to when it occurs, but it's causing some real problems! I'm not an SQL Server expert, although I do know roughly what I'm doing. The connection string is as follows:
sDSN = "Provider=SQLOLEDB;Network Library=DBMSSOCN;Initial Catalog=myDatabaseName;Data Source=xxx.xxx.xxx.xxx;User Id=myUserId;Password=myPassword;"
I'm connecting with ASP, and also with a VBS script, and the error occurs on both. I've also tried changing to using a DSN, but that seems to make no difference - sometimes it works, sometimes it doesn't.
Could it be a processor usage thing? If the SQL Server is doing a lot of work, could it ignore requests?
January 27th, 2004, 02:23 PM
Is your connection trying to reconnect again after a specified retry time?
I would look at running a sql/ODBC trace and see if you can `see` what is happening. Since this issue is random, it may take some time and logging to just capture the error.