We have a system that has been running for years and some of the .asp pages haven't been touched in years, but about 10 days ago the same SQL stmts that have always been there have been returning
Microsoft OLE DB Provider for ODBC Drivers error '80004005'
This is on SQL Server 2000
Windows 2000 SP4
The problem is this occurs at random. The statement will execute 5x, then fail once, then run 2 more times and fail again. There isn't a problem with the SQL itself as the same Select stmt will fail or pass randomly. I can also take and execute the stmt manually and it will also pass. There are about 5 places it occurs in our pages. Here is on particular example. Note that DataRS("acc_num") is a valid value and will return just fine if I manually execute it and will pass probably 75% of the time as it is, but 25% of the time will fail with above error.
CMD = "SELECT TOP 1 id, acc_num, businessType, mdCountry, cust_lec, First_Name, Last_Name FROM accounts WHERE (Acc_Num = '" & DataRS("acc_num") & "')"
AccRS.Open CMD, DBConn, adOpenForwardOnly
Not sure at all what is causing this. I've haven't been able to find anything conclusive in the SQL Profiler either.
Is this the correct version of MDAC? I tried to get the latest, but it was an earlier version .1117.
I would check to see if any of the DataRS("acc_num")'s have suddenly sprouted single quotes. Also, you may want to monitor the statement via Profiler with the SQL Statement Starting and RPC starting events, to see if you can see an obviously munged statement (likely user entered data, if the code has not changed in years). Just as a dumb check, I would also make sure the .asp file dates are on the order of years old. It would not be the first time someone deployed a change without telling anyone.
I checked the stmt as I had it in the profiler already and it looks fine. I even took the stmt as is and did it manually in the SQL Pane in Enterprise Manager and it works fine. Also, if you were to click this link from the website, it would work some of the time and fail others, but no consistency on that.
I've got the profiler capturing just about every event right now. I can't make much sense though of the results.
I can see it executing 2 Select stmts and those complete just fine, then it has another Select to execute, but before it even shows the 3rd select stmt starting, I get a whole series of these:
7 pairs in total, then I get the error and I never see the 3rd select at all in the log. Like the cmd never made it or it got tossed or something. I don't see the login failing either and it shouldn't as the login is the same everywhere.
When it works fine, I don't have all those Audit Login/Logout pairs, but what I have is the 3rd Select stmt is bracketed around those login/logout pairs like this:
Like I said, I can click the link 3 times and it works fine, then the 4th time it fails and its exactly the same data each time. When it fails, I see those series of login/logout and never see the 3rd select stmt at all.