April 26, 2003 at 10:19 am
I am having a problem with running a query on a SQL server 2000 database table. I have a search that uses a zip code and radius to find results. It does the first search to make a list of the zip codes within the radius, and then searches against another table to find the results (matching a records zip code using the IN command). The list of zip codes can become large with a radius of 100 miles. I have encountered the error below when the zip code list becomes large:
SqlDumpExceptionHandler: Process 51 generated fatal exception c0000005 EXCEPTION_ACCESS_VIOLATION. SQL Server is terminating this process.
I have had successful searches that have a zip code list of around 1700 zip codes, but I consistently get the above error when I do a particular search where the list reaches around 2800 zip codes. Being that I only get the error with a large zip code list, I'm assuming I'm running into some kind of buffering problem, that it just can't handle a list that long. I am running into this problem on my development system, and the live system that is run by a hosting company. Any help would be greatly appreciated.
Thank you in advance.
April 26, 2003 at 1:45 pm
I have narrowed it down to 2185 items in the list being the magic number that the system dumps. Any search that uses a zip code list that contains 2184 items or less completes the query and runs fine. As soon as it hits 2185 items or more, it gives the above error. Any thoughts?
April 26, 2003 at 4:34 pm
Try
Select f.Col1,f.Col2
From (Select r.Zipcode
From RadiusTable as r
Where Distance<100) as z(Zipcode)
Inner Join FinalTable as f
On f.Zipcode=z.Zipcode
April 27, 2003 at 1:14 pm
5409045121009, thank you for the suggestion, unfortunately, I am running off of two different databases. The zip codes are under a MS Access datasource, and the member data is under a MS SQL 2000 datasource. Is there a way to do what you suggest under these circumstances? Thank you for your help.
April 27, 2003 at 1:28 pm
You might be able to do it with a linked server to your Access DB. However, I would call into MS SQL Server support. Access Violations are typically the result ofa bug in SQL Server. This definitely sounds like a bug, thus you will not have to pay for your support.
-Chad
Software built for the Common Language Runtime
http://www.clrsoft.com
Software built for the Common Language Runtime
April 27, 2003 at 2:12 pm
Hello chadmat. Thank you for the reply. I think you are correct that it is a bug in SQL Server. I recreated the final search table in MS Access and the search worked fine. So apparently SQL Server doesn't like lists with more than 2184 items used in queries. Thanks.
April 27, 2003 at 3:16 pm
BOL 2000: See OPENROWSET, example towards the end for Access and SQL, alternatively OPENDATASOURCE
April 27, 2003 at 5:51 pm
quote:
I think you are correct that it is a bug in SQL Server. I recreated the final search table in MS Access and the search worked fine. So apparently SQL Server doesn't like lists with more than 2184 items used in queries
sjbrisson,
Can you post your original query? What service pack of SQL Server and Access do you have?
Edited by - Allen_Cui on 04/27/2003 6:54:39 PM
April 28, 2003 at 7:15 am
Allen_Cui, the live server has all the latest service packs running. I have narrowed the problem down to using a CASE statement. Below is the part of my WHERE clause that gives me the error:
<cfif parameterexists(form.ziplist)>
CASE len(OrganizationPostalCode)
WHEN 6 THEN
left(OrganizationPostalCode,3)
WHEN 7 THEN
left(OrganizationPostalCode,3)
ELSE
left(OrganizationPostalCode,5)
END IN (#preservesinglequotes(form.ziplist)#) AND
</cfif>
If I remove the CASE statement and just use this, it works fine:
<cfif parameterexists(form.ziplist)>
left(OrganizationPostalCode,5) IN (#preservesinglequotes(form.ziplist)#) AND
</cfif>
When using Access to test, I replaced this CASE statement with an IFF statement and it worked fine. So for some reason, when using a list with 2185 items or more within a CASE statement and SQL Server, the system dumps out. I don't understand why the 'form.ziplist' length would have any effect on the CASE statement since that is used just to determine what value I need to check against the list. Any ideas would be greatly appreciated.
Thank you.
April 28, 2003 at 8:09 am
A follow-up: the CASE statement does not cause any errors when there is only 1 when clause:
<cfif parameterexists(form.ziplist)>
CASE len(OrganizationPostalCode)
WHEN 6 THEN left(OrganizationPostalCode,3)
ELSE left(OrganizationPostalCode,5)
END IN (#preservesinglequotes(form.ziplist)#) AND
</cfif>
When I add in the second WHEN clause with the long list is when it crashes.
<cfif parameterexists(form.ziplist)>
CASE len(OrganizationPostalCode)
WHEN 6 THEN left(OrganizationPostalCode,3)
WHEN 7 THEN left(OrganizationPostalCode,3)
ELSE left(OrganizationPostalCode,5)
END IN (#preservesinglequotes(form.ziplist)#) AND
</cfif>
Any ideas?
April 28, 2003 at 11:19 pm
Did you try MS Support? They should be able to help you with a fix, either a workaround, or a hotfix etc..
-Chad
Software built for the Common Language Runtime
http://www.clrsoft.com
Software built for the Common Language Runtime
April 29, 2003 at 6:54 am
chadmat, I actually came up with a workaround last night. It's a little more code, but it seems to give me the results I'm looking for. As for going through MS, I've heard that can be a bear and take forever, that's why I love these types of forums. I'll let them know about it, but for now at least my code works. Thanks.
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply