Large query not returning proper values

Results 1 to 2 of 2

Thread: Large query not returning proper values

  1. #1
    Join Date
    Dec 1969

    Default Large query not returning proper values

    I have a SQL query that I am building dynamically through a VB COM Dll that takes three delimited strings as arguments. For the most part, everything is working okay, but I have run into a stumbling block and am hoping someone here will be familiar with the issue and can illuminate me.<BR><BR>The basic gist of the query is this:<BR><BR>select * from table where (status=&#039;active&#039;) AND (lastaccess &#060; today) AND (category like 1 OR category like 2) AND (location like 1 OR location like 2) order by lastaccess desc<BR><BR>I want to get back all records that are active and not too old and have matching location and category fields. The location and category fields are of type nvarchar with delimited strings in them that can have multiple locations, categories, etc., so I have to use LIKE&#039;s.<BR><BR>For each value that is sent in from the string arguments, I add it to the respective field. For example, (category like 1, OR category like 2, OR category like 3, OR category like 4) etc.<BR><BR>If the query only has a couple of arguments sent to it for category and location, it works fine. The issue is when I start adding a lot of locations and categories (a lot being defined as somewhere around 20, I haven&#039;t spent any much trying to find the exact breaking point as I am under a tight deadline).<BR><BR>I have determined that the issue is not coming the dll or anything else. If I echo the SQL query back from the DLL and copy it into query analyzer or enterprise manager, its fails there as well. I am thinking I hit some kind of limitation in SQL 2k for the length of the SQL query, but I can&#039;t believe that they limit it as 15k (query is just a little over 15k). <BR><BR>If anyone is familiar with anything like this, knows what I am doing wrong, or sees a better way to do this then please let me know. <BR><BR>Thanks in advance!

  2. #2
    Join Date
    Dec 1969

    Default Well, look at this page...<BR><BR>It seems to say that the SQL statement length is limited to around 256MB. (64k times 4k)<BR><BR>Maybe the WHERE conditions fall into that 8000 byte max for "Bytes per sort column"???? Or something along those lines?<BR><BR>Sorry, no good ideas.<BR><BR>Is there any way you could avoid using LIKE in the queries??? That is, are you asking the user to choose categories and locations from &#060;SELECT MULTIPLE&#062;s or that ilk? Or are they just typing in arbitrary words?<BR><BR>If the user is getting from &#060;SELECT MULTIPLE&#062; (or checkboxes or anything but free format text), then you could use<BR> WHERE category IN ( ... )<BR>as is discussed in one of the ASPFAQs, in category "Databases, Queries" [one that I wrote].<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts