Can CONTAINSTABLE get its search terms from a table?  
Author Message
CathyBoehm





PostPosted: Wed Nov 05 22:23:14 CST 2003 Top

SQL Server >> Can CONTAINSTABLE get its search terms from a table?

(SQL Server 2000, SP3)

Hello all!

We've got an issue with a Full Text Search query that is failing because of too many
search terms in the CONTAINSTABLE clause (upwards of 3000 elements separated with " OR ",
I think).

I was wondering if the CONTAINSTABLE clause could somehow be refactored to obtain its
search terms from a temporary table? I certainly don't see any evidence of this in BOL --
but I thought I'd check here.

Thanks for any help you can provide! :-)

John Peterson

SQL Server132  
 
 
John





PostPosted: Wed Nov 05 22:23:14 CST 2003 Top

SQL Server >> Can CONTAINSTABLE get its search terms from a table? John,
Are you getting a syntax error on the FTS query? If so, what is the syntax
error? If you reduce the number of terms, i.e., reduce the total length of
the search string, does the error go away? There was a bug in the past
builds of SQL Server in regards to the length of the search string, but it
was fixed in an early SP.

Is the containstable query in a stored procedure or is it an ad hoc query?
You could "refactor" the query to use a temp table of terms, but that would
most likely require cursors or a while loop, both could be detrimental to
the SQL FTS query performance... Instead, try something like this and change
contains to containstable and be careful and use the correct number of
single quotes...

use pubs
go
DROP PROCEDURE usp_FTSearchPubsInfo
go

AS




go

-- Small / Simple example of mutiple parameters....
EXEC usp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or
"wastepaper") or
("recycle* paper") or (("paper slurry") and ("paper sludge")) or
("biodegrad* paper") or
("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
"chemical*"))'
go

With usp_FTSearchPubsInfo you can add to the length of the search string up
to 8000 bytes (the max varchar size), but I've not tested this on SQL 2000
SP3 to 8000 bytes, so, your 3000 elements *might* exceed this limit... Give
it a try and let us know...

Regards,
John






> (SQL Server 2000, SP3)
>
> Hello all!
>
> We've got an issue with a Full Text Search query that is failing because
of too many
> search terms in the CONTAINSTABLE clause (upwards of 3000 elements
separated with " OR ",
> I think).
>
> I was wondering if the CONTAINSTABLE clause could somehow be refactored to
obtain its
> search terms from a temporary table? I certainly don't see any evidence
of this in BOL --
> but I thought I'd check here.
>
> Thanks for any help you can provide! :-)
>
> John Peterson
>
>


 
 
John





PostPosted: Wed Nov 05 22:48:51 CST 2003 Top

SQL Server >> Can CONTAINSTABLE get its search terms from a table? Hello John!

I *was* getting a syntax error in the FTS query -- and it would go away when I removed a
bunch of the search terms. This was the error:

Server: Msg 170, Level 15, State 1, Line 5
Line 5: Incorrect syntax near ' "Aberdeen" OR "Acton" OR "Acworth" OR "Addison" OR
"Advance" OR "Ajax" OR "Alameda" OR "Albuquerque" OR "Algonquin" OR "Aliquip'.

The query is an ad-hoc query that gets built by an .ASP application and sent to SQL Server
via an ADOConnection/ADORecordset.

Alas, the string that's being sent is like 20K -- so I think that it far exceeds that 8000
character limit. :-(

Fundamentally, I think we need to change our searching component to ensure that people
can't enter in a billion search terms. But, by the same token, I felt that if we could
fix this relatively quickly/easily, that would be good too.

Any other ideas that we might be able to try?

As always, thank you so much for your help! :-)

John Peterson




> John,
> Are you getting a syntax error on the FTS query? If so, what is the syntax
> error? If you reduce the number of terms, i.e., reduce the total length of
> the search string, does the error go away? There was a bug in the past
> builds of SQL Server in regards to the length of the search string, but it
> was fixed in an early SP.
>
> Is the containstable query in a stored procedure or is it an ad hoc query?
> You could "refactor" the query to use a temp table of terms, but that would
> most likely require cursors or a while loop, both could be detrimental to
> the SQL FTS query performance... Instead, try something like this and change
> contains to containstable and be careful and use the correct number of
> single quotes...
>
> use pubs
> go
> DROP PROCEDURE usp_FTSearchPubsInfo
> go

> AS




> go
>
> -- Small / Simple example of mutiple parameters....
> EXEC usp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or
> "wastepaper") or
> ("recycle* paper") or (("paper slurry") and ("paper sludge")) or
> ("biodegrad* paper") or
> ("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
> "chemical*"))'
> go
>
> With usp_FTSearchPubsInfo you can add to the length of the search string up
> to 8000 bytes (the max varchar size), but I've not tested this on SQL 2000
> SP3 to 8000 bytes, so, your 3000 elements *might* exceed this limit... Give
> it a try and let us know...
>
> Regards,
> John
>
>
>
>


> > (SQL Server 2000, SP3)
> >
> > Hello all!
> >
> > We've got an issue with a Full Text Search query that is failing because
> of too many
> > search terms in the CONTAINSTABLE clause (upwards of 3000 elements
> separated with " OR ",
> > I think).
> >
> > I was wondering if the CONTAINSTABLE clause could somehow be refactored to
> obtain its
> > search terms from a temporary table? I certainly don't see any evidence
> of this in BOL --
> > but I thought I'd check here.
> >
> > Thanks for any help you can provide! :-)
> >
> > John Peterson
> >
> >
>
>


 
 
John





PostPosted: Thu Nov 06 10:01:41 CST 2003 Top

SQL Server >> Can CONTAINSTABLE get its search terms from a table? You're welcome, John
That's the syntax error I suspected you were getting... Yes, I think it
would be a good idea to change your "searching component to ensure that
people can't enter in a billion search terms" as even Google limits the
number of *effective* search terms to 10, even though you can enter as many
search terms as you want. Perhaps adding a "tips" or "help" statement to
your ASP application page might be helpful as well, stating a limit of 10
(or whatever number) of search words are allowed...

Relative to the below stored procedure, and a "quick fix" for this would be
allowing your users to enter a trailing * (asterisk), for example book* - to
find book, books, booking, booked, etc. so that they will not have to enter
all word variations. Note, also add a tip that only a trailing * (asterisk)
is allowed as SQL FTS only supports this syntax...

Regards,
John






> Hello John!
>
> I *was* getting a syntax error in the FTS query -- and it would go away
when I removed a
> bunch of the search terms. This was the error:
>
> Server: Msg 170, Level 15, State 1, Line 5
> Line 5: Incorrect syntax near ' "Aberdeen" OR "Acton" OR "Acworth" OR
"Addison" OR
> "Advance" OR "Ajax" OR "Alameda" OR "Albuquerque" OR "Algonquin" OR
"Aliquip'.
>
> The query is an ad-hoc query that gets built by an .ASP application and
sent to SQL Server
> via an ADOConnection/ADORecordset.
>
> Alas, the string that's being sent is like 20K -- so I think that it far
exceeds that 8000
> character limit. :-(
>
> Fundamentally, I think we need to change our searching component to ensure
that people
> can't enter in a billion search terms. But, by the same token, I felt
that if we could
> fix this relatively quickly/easily, that would be good too.
>
> Any other ideas that we might be able to try?
>
> As always, thank you so much for your help! :-)
>
> John Peterson
>
>


> > John,
> > Are you getting a syntax error on the FTS query? If so, what is the
syntax
> > error? If you reduce the number of terms, i.e., reduce the total length
of
> > the search string, does the error go away? There was a bug in the past
> > builds of SQL Server in regards to the length of the search string, but
it
> > was fixed in an early SP.
> >
> > Is the containstable query in a stored procedure or is it an ad hoc
query?
> > You could "refactor" the query to use a temp table of terms, but that
would
> > most likely require cursors or a while loop, both could be detrimental
to
> > the SQL FTS query performance... Instead, try something like this and
change
> > contains to containstable and be careful and use the correct number of
> > single quotes...
> >
> > use pubs
> > go
> > DROP PROCEDURE usp_FTSearchPubsInfo
> > go

> > AS




> > go
> >
> > -- Small / Simple example of mutiple parameters....
> > EXEC usp_FTSearchPubsInfo '("pulp*") or ("waste" and "paper" or
> > "wastepaper") or
> > ("recycle* paper") or (("paper slurry") and ("paper sludge")) or
> > ("biodegrad* paper") or
> > ("paper" and "dispos*") or (("paper" near "bleach*") or ("paper" near
> > "chemical*"))'
> > go
> >
> > With usp_FTSearchPubsInfo you can add to the length of the search
string up
> > to 8000 bytes (the max varchar size), but I've not tested this on SQL
2000
> > SP3 to 8000 bytes, so, your 3000 elements *might* exceed this limit...
Give
> > it a try and let us know...
> >
> > Regards,
> > John
> >
> >
> >
> >


> > > (SQL Server 2000, SP3)
> > >
> > > Hello all!
> > >
> > > We've got an issue with a Full Text Search query that is failing
because
> > of too many
> > > search terms in the CONTAINSTABLE clause (upwards of 3000 elements
> > separated with " OR ",
> > > I think).
> > >
> > > I was wondering if the CONTAINSTABLE clause could somehow be
refactored to
> > obtain its
> > > search terms from a temporary table? I certainly don't see any
evidence
> > of this in BOL --
> > > but I thought I'd check here.
> > >
> > > Thanks for any help you can provide! :-)
> > >
> > > John Peterson
> > >
> > >
> >
> >
>
>