ft search error in win2k3 sp1 with sql 2k sp4?

I just setup my server with win2k3 sp1 and ALL the latest patches, plus
sql 2004 standard sp4.
I keep getting this error on a table with 39000 records where I am
running a basic wildcard CONTAINS fulltext query for "cer*"
Server: Msg 7643, Level 16, State 1, Line 1
Your search generated too many results. Please perform a more specific
search.
There should be about 30000 records.
I noticed on technet that this was a problem pre sp2 in sql, but was
since patched. Has the bug recently been re-introduced in some combo
of win2k3 and sql patches?
Phadke,
Yes, there was some additional logic added in SP3 that generates the below
error message. SQL FTS is optimized for returning 2000 results per query,
primarily for performance reasons. You may want to re-write your query using
CONTAINSTABLE and use the Top_N_by_Rank parameter to limit the results to
2000 or use a similar TOP restriction in the select list.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129632193.545575.218860@.g47g2000cwa.googlegr oups.com...
>I just setup my server with win2k3 sp1 and ALL the latest patches, plus
> sql 2004 standard sp4.
> I keep getting this error on a table with 39000 records where I am
> running a basic wildcard CONTAINS fulltext query for "cer*"
> Server: Msg 7643, Level 16, State 1, Line 1
> Your search generated too many results. Please perform a more specific
> search.
> There should be about 30000 records.
> I noticed on technet that this was a problem pre sp2 in sql, but was
> since patched. Has the bug recently been re-introduced in some combo
> of win2k3 and sql patches?
>
|||John,
Just to clarify, is this a hard limit of 2000, because I am able to see
the result set for queries that return a con query with 17-18k records
with no problem.
I saw another technote that the problem limit was at approx 20k
records:
http://support.microsoft.com/default...;en-us;Q296456
Thanks for the info,
Aditya
|||Also, is this limitation common to sql 2005?
|||Ok, now I am really confused...I ran a fulltext query for "c*" on a
database on another server. This database has 2.4 Million Records.
This server is Win2k3 (no SP) and SQL 2k SP3.
When I run the query, I get 900,000 records back without an error
message.
|||Aditya,
Yes, this is the KB article that I couldn't find early this morning when in
a rush to get off to work...
Still for performance reasons, it might be best to use Top_N_Rank with
CONTAINSTABLE or is there a specific reason you want to return this many
rows?
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
<phadke@.gmail.com> wrote in message
news:1129660216.247521.205300@.g47g2000cwa.googlegr oups.com...
> John,
> Just to clarify, is this a hard limit of 2000, because I am able to see
> the result set for queries that return a con query with 17-18k records
> with no problem.
> I saw another technote that the problem limit was at approx 20k
> records:
> http://support.microsoft.com/default...;en-us;Q296456
> Thanks for the info,
> Aditya
>
|||Aditya,
I need to confirm this with a large table in order to generate the large
results. However, I would suspect that it is still in SQL 2005 until proved
otherwise...
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129660966.529955.176010@.g44g2000cwa.googlegr oups.com...
> Also, is this limitation common to sql 2005?
>
|||Aditya,
Different servers can behavior differently for FTS results based upon the OS
platform (different wordbreakers) and due to different noise word file
contents. In this case, and on this server is the single letter "C" removed
from the language-specific noise word file (noise.enu = US English) for the
specific FT-enable column's "Language for Word Breaker"?
As they say the devil is in the details... ;-)
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Aditya Phadke" <phadke@.gmail.com> wrote in message
news:1129672239.794380.257680@.o13g2000cwo.googlegr oups.com...
> Ok, now I am really confused...I ran a fulltext query for "c*" on a
> database on another server. This database has 2.4 Million Records.
> This server is Win2k3 (no SP) and SQL 2k SP3.
> When I run the query, I get 900,000 records back without an error
> message.
>