Big IN clause cause access violation 
Author Message
 Big IN clause cause access violation

I have a simple, but large, query that causes an access violation when run
(I have tried it in Database Explorer and Database Desktop and it causes an
access violation there too).

Does anyone know why? The database being used contains Paradox tables.

Here is the query:

SELECT
  BOOK_ID
FROM
  BOOK
WHERE
  LOCATION_ID = 1 AND
  AUTHOR_ID IN (8719, 3929, 5186, 6858, 6845, 9969, 9678, 10771, 10564,
< SNIP > < big list of numbers removed >
7453, 168, 11858, 387, 7446, 7291)

The IN clause is huge, but when I take away the line:
      LOCATION_ID = 1 AND
it works fine.

When I make the list of numbers smaller then it works fine too. Is it just a
limit on the number of values you can put in the IN clause? But why then
does it work with the location line removed?

If you're wondering why I'm doing it like this it is because I found that
using ids, and then a lookup field to find the author names (in this case)
is faster than joining the tables.

Steve



Wed, 18 Jun 1902 08:00:00 GMT
 
 [ 1 post ] 

 Relevant Pages 

1. Triggers seem to cause PK violations/changed records in Access

2. Scroll_Lock cursor and Update causes ACCESS Violation

3. why does this query cause an access violation?

4. ADO Stream object cause access violation

5. Stored procedure causes ACCESS VIOLATION.

6. BUG: Concurrent Xp_makewebtask May Cause an Access Violation

7. SS 6.5: Cause of an access violation

8. VB5 app using DAO causes access violation when shut down on NT4.0

9. SELECT < CAUSING AN ACCESS VIOLATION

10. Stored proc causes access violation

11. Manually created recordset causes Access Violation in CoUninitialize()

12. CDatabase causes Access Violation


 
Powered by phpBB® Forum Software