7645 Null or empty full-text predicate

by

I found the answer to this today when converting my own database from SQL 2005 to SQL 2008.

Pass "" for your search term and change the @search_term = '' test to be @search_term = '""'
SQL server will ignore the double quotes and not throw an error.

For example, the following would actually returns all records in the Users table:

declare  @SearchTerm nvarchar(250)

SET @SearchTerm = '""'

select UserId, U.Description, U.UserName
from dbo.Users U
WHERE ((@SearchTerm = '""') OR CONTAINS( (U.Description, U.UserName), @SearchTerm))

If you are using .Net, you might grab a copy of E. W. Bachtal’s FullTextSearch class. His site is very informative: http://ewbi.blogs.com/develops/

More Related Contents:

  1. Is it possible to use Full Text Search (FTS) with LINQ?
  2. How do you get leading wildcard full-text searches to work in SQL Server?
  3. Full text search does not work if stop word is included even though stop word list is empty
  4. SQL Server Full Text Search Escape Characters?
  5. Using Full-Text Search in SQL Server 2008 across multiple tables, columns
  6. Full text catalog/index search for %book%
  7. SQL Server – error when creating stored procedure
  8. What do Clustered and Non-Clustered index actually mean?
  9. Regex pattern inside SQL Replace function?
  10. Are GUID collisions possible?
  11. SQL Server Escape an Underscore
  12. Does a foreign key automatically create an index?
  13. best way to convert and validate a date string
  14. Why use the INCLUDE clause when creating an index?
  15. Is there a way to simplify a NULL compare of 2 values
  16. How do I enable MSDTC on SQL Server?
  17. An attempt was made to access a socket in a way forbidden by its access permissions
  18. TSQL Try / Catch within Transaction or vice versa?
  19. What is the maximum number of characters that nvarchar(MAX) will hold?
  20. Is there any difference between DECIMAL and NUMERIC in SQL Server?
  21. SQL Server history table – populate through SP or Trigger?
  22. cross apply xml query performs exponentially worse as xml document grows
  23. How do I flush the PRINT buffer in TSQL?
  24. Removing the remembered login and password list in SQL Server Management Studio
  25. SQL Server: convert ((int)year,(int)month,(int)day) to Datetime [duplicate]
  26. Convert from DateTime to INT
  27. Is it possible to use user defined aggregates (clr) with window functions (over)?
  28. SQL SERVER – Understanding how MIN(text) works
  29. How to convert text column to datetime in SQL
  30. How to calculate difference in hours (decimal) between two dates in SQL Server?

Leave a Comment