| Author |
 Topic  |
|
clj
Junior Member
145 Posts |
 Posted - 09 August 2006 : 13:05:54
|
Hi everyone
I have custom errors switched on in IIS and when the forum errors it sends me an email.
Recently I've been getting a lot of the following error:
Error Number: -2147217887
Source: Microsoft OLE DB Provider for SQL Server
File: /forum/search.asp
Description: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done..
Code: No code available
Line: 446
Time: 09/08/2006 16:53:32
IP info : 82.163.6.14, 82.163.6.14, 192.168.1.65
Script name : /forum/search.asp
Referer : http://cyclingplus.co.uk/forum/search.asp
I'm not sure how this is being generated or what it means. I can't replicate it. Does anyone have any ideas?
Many thanks
Clare |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
clj
Junior Member
145 Posts |
Posted - 09 August 2006 : 14:56:52
|
Sorry, I should have thought to post that. It follows below, the last line is no 446. Please note I'm running the optimised search for SQL Server, thanks
else 'end MySql specific code
set rs = Server.CreateObject("ADODB.Recordset")
rs.cachesize = strPageSize
strFinalSql = strSql & strSql2 & strSqlTopics & strSql31
if Request.Form("SearchMessage") <> 1 then
strFinalSql = strFinalsql & strSqlReplies & strSql32
end if
strFinalSql = StrFinalSql & strSql4
'Response.Write strFinalSql
'Response.End
rs.open strFinalSql, my_Conn, adOpenDynamic |
 |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
clj
Junior Member
145 Posts |
Posted - 10 August 2006 : 07:01:16
|
Ok, done, I got the below:
SELECT DISTINCT C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME, C.CAT_ORDER, F.F_ORDER, F.FORUM_ID, F.F_SUBJECT, F.CAT_ID, F.F_SUBSCRIPTION, F.F_STATUS, T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_STATUS, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT, M.MEMBER_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, F.F_PRIVATEFORUMS, F.F_PASSWORD_NEW FROM ((((FORUM_FORUM F INNER JOIN FORUM_TOPICS T ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID) INNER JOIN FORUM_MEMBERS M ON T.T_AUTHOR = M.MEMBER_ID) INNER JOIN FORUM_CATEGORY C ON T.CAT_ID = C.CAT_ID) LEFT JOIN FORUM_MEMBERS MEMBERS_1 ON T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID WHERE T.TOPIC_ID IN ( SELECT T.TOPIC_ID FROM FORUM_TOPICS T WHERE ( (T.T_SUBJECT LIKE '%test%' OR T.T_MESSAGE LIKE '%test%') ) AND F.F_TYPE = 0 UNION SELECT T.TOPIC_ID FROM FORUM_TOPICS T INNER JOIN FORUM_REPLY R ON R.TOPIC_ID=T.TOPIC_ID WHERE ( (R.R_MESSAGE LIKE '%test%') ) AND F.F_TYPE = 0) ORDER BY C.CAT_ORDER, C.CAT_NAME, F.F_ORDER, F.F_SUBJECT, T.T_LAST_POST DESC
Thanks ruirib
Clare |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
clj
Junior Member
145 Posts |
Posted - 10 August 2006 : 08:07:59
|
Thanks ruirib.
Just to add, I run 2 other forums as well - 1 of which is a similar large size, and I'm not getting these search errors from the other forums. I've checked the code and it's identical. I wonder if this points to a problem with some of the data in the database (this could also explain why I've been unable to replicate the problem - maybe it only happens when the search pulls out a certain topic?) FYI I've had 19 of these errors today
Thanks again
Clare |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
clj
Junior Member
145 Posts |
Posted - 10 August 2006 : 10:31:57
|
OLE DB Version 02.70
Provider Version 08.10.1830 |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 10 August 2006 : 13:52:09
|
I suppose you're using the SQLOLEDB provider, right? Also, your provider is not the latest, so you may ask your host to update the MDAC.
This is a annoying situation. I run your query in SQL Server without any problems whatsoever... |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
clj
Junior Member
145 Posts |
Posted - 11 August 2006 : 07:00:10
|
Hiya
Yes it is rather frustrating isn't it! I can't replicate the error despite trying lots of search variations.
I'm not sure if I'm using the SQLOLEDB provider - how do I check? Also I'm not sure what MDAC is? Although please note that my other forums are on the same server (and have the same settings) and I'm not getting this error from them.
I wonder if the best idea may be to post on the forum asking users if they are having problems with the search and try to pinpoint what's happening that way if we can't figure it out from a tech point of view
Thanks
Clare |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 11 August 2006 : 07:05:24
|
You can check your connection string in config.asp and tell me whether there is SQLOLEB in it. MDAC is the Microsoft API that allows database access from applications, including ASP apps. Your host should know about it.
If you can find a specific search that causes the error, just post the output as before, to allow me to check if it causes a problem with my DB as well. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
clj
Junior Member
145 Posts |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 14 August 2006 : 09:32:08
|
| That is a situation that AFAIK should not occur with the latest MDAC. Can it be a server load issue? Well if a given search completes in a given time of the day and the exact same search fails later, that is weird and it surely does not have to do with the search code, but with something else. Can that be the server? I really can't tell, since I have no previous experience with such a situation. You should discuss that with your host, IMO. |
Snitz 3.4 Readme | Like the support? Support Snitz too |
|
|
|
clj
Junior Member
145 Posts |
Posted - 14 August 2006 : 10:26:43
|
Thanks ruirib, will do
Thanks for all your help
Clare |
|
|
|
bitwise2000
Starting Member
38 Posts |
Posted - 07 April 2008 : 22:17:57
|
I hate to dredge up an old topic, but I wonder if anyone has worked on this at all and gotten the modified search code (the one that uses UNION) to work in situations with large tables.
I use that search code and it works great on the active tables which have on the order of 100K posts. Not so for the archive tables which have about 400K posts.
Here are the two queries for "metal lathe", the only difference being that the second one is searching the archives.
SELECT DISTINCT C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME, C.CAT_ORDER, F.F_ORDER, F.FORUM_ID, F.F_SUBJECT, F.CAT_ID, F.F_SUBSCRIPTION, F.F_STATUS, T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_DATE, T.T_STATUS, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT, M.MEMBER_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, F.F_PRIVATEFORUMS, F.F_PASSWORD_NEW, T.T_STICKY FROM ((((FORUM_FORUM F INNER JOIN FORUM_TOPICS T ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN FORUM_REPLY R ON T.TOPIC_ID = R.TOPIC_ID) INNER JOIN FORUM_MEMBERS M ON T.T_AUTHOR = M.MEMBER_ID) INNER JOIN FORUM_CATEGORY C ON T.CAT_ID = C.CAT_ID) LEFT JOIN FORUM_MEMBERS MEMBERS_1 ON T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID WHERE T.TOPIC_ID IN ( SELECT T.TOPIC_ID FROM FORUM_TOPICS T WHERE (( T.T_SUBJECT LIKE '%metal lathe%' OR T.T_MESSAGE LIKE '%metal lathe%') ) AND F.F_TYPE = 0 UNION SELECT T.TOPIC_ID FROM FORUM_TOPICS T INNER JOIN FORUM_REPLY R ON R.TOPIC_ID=T.TOPIC_ID WHERE ( (R.R_MESSAGE LIKE '%metal lathe%') ) AND F.F_TYPE = 0) ORDER BY C.CAT_ORDER, C.CAT_NAME, F.F_ORDER, F.F_SUBJECT, T.T_LAST_POST DESC
SELECT DISTINCT C.CAT_STATUS, C.CAT_SUBSCRIPTION, C.CAT_NAME, C.CAT_ORDER, F.F_ORDER, F.FORUM_ID, F.F_SUBJECT, F.CAT_ID, F.F_SUBSCRIPTION, F.F_STATUS, T.TOPIC_ID, T.T_AUTHOR, T.T_SUBJECT, T.T_DATE, T.T_STATUS, T.T_LAST_POST, T.T_LAST_POST_AUTHOR, T.T_LAST_POST_REPLY_ID, T.T_REPLIES, T.T_UREPLIES, T.T_VIEW_COUNT, M.MEMBER_ID, M.M_NAME, MEMBERS_1.M_NAME AS LAST_POST_AUTHOR_NAME, F.F_PRIVATEFORUMS, F.F_PASSWORD_NEW, T.T_STICKY FROM ((((FORUM_FORUM F INNER JOIN FORUM_A_TOPICS T ON F.FORUM_ID = T.FORUM_ID) LEFT JOIN FORUM_A_REPLY R ON T.TOPIC_ID = R.TOPIC_ID) INNER JOIN FORUM_MEMBERS M ON T.T_AUTHOR = M.MEMBER_ID) INNER JOIN FORUM_CATEGORY C ON T.CAT_ID = C.CAT_ID) LEFT JOIN FORUM_MEMBERS MEMBERS_1 ON T.T_LAST_POST_AUTHOR = MEMBERS_1.MEMBER_ID WHERE T.TOPIC_ID IN ( SELECT T.TOPIC_ID FROM FORUM_A_TOPICS T WHERE (( T.T_SUBJECT LIKE '%metal lathe%' OR T.T_MESSAGE LIKE '%metal lathe%') ) AND F.F_TYPE = 0 UNION SELECT T.TOPIC_ID FROM FORUM_A_TOPICS T INNER JOIN FORUM_A_REPLY R ON R.TOPIC_ID=T.TOPIC_ID WHERE ( (R.R_MESSAGE LIKE '%metal lathe%') ) AND F.F_TYPE = 0) ORDER BY C.CAT_ORDER, C.CAT_NAME, F.F_ORDER, F.F_SUBJECT, T.T_LAST_POST DESC
If I run these queries directly, outside of Snitz, the first one runs fine, and the second one generates the same error reported by the original poster:
error -2147217887 (Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.)
That doesn't look like a real timeout to me, so what's wrong with that second query?
A related question... can someone point me to a quick and dirty solution using full-text searching? Do I just have that enabled on the server and use the existing code or does the code need to be modified?
Many Thanks! |
|
|
|
Topic |
|
|
|
Topic Locked
