BUG: When You Use a Full Text Query Clause Greater Than 8K an Error Message Occurs That Does Not Indicate Overflow of Size Limit (285114)

SYMPTOMS

For full-text queries that use the CONTAINS or FREETEXT predicates or that use the CONTAINSTABLE or FREETEXTTABLE rowset functions, the query clause is limited to the varchar(8000) or nvarchar(4000) character data type size limit. However, if the size of a query clause is greater than 8K, an error message occurs that may not indicate that the query has exceeded a limit.

For example if the CONTAINSTABLE clause in the following query is greater than 8K:

SELECT FT_TBL.*, KEY_TBL.RANK
FROM pub_info AS FT_TBL INNER JOIN
CONTAINSTABLE(pub_info, *, 'ISABOUT 
( "sql" weight (1.0), "experience" weight (0.1), "asp" weight
(0.1), "vb" weight (0.1),................."a" weight (1.0))', 100)
-- clause is greater than 8k
AS KEY_TBL ON FT_TBL.pub_id = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC)

This error message occurs:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near 'ISABOUT ( "sql" weight (1.0), "experience" weight (0.1), "asp" weight
(0.1), "vb" weight (0.1), "programmer" weight (0.1), "co'.

The error message should indicate that the clause has exceeded a size limit.

WORKAROUND

To work around this issue:

Reduce the size of the clause.

-or-

Break up the query into multiple queries, and then UNION the results. The following example demonstrates the use of the UNION operator:

SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN 
   CONTAINSTABLE(Categories, Description, 
   'ISABOUT (breads weight (.8) )' ) AS KEY_TBL
   ON FT_TBL.CategoryID = KEY_TBL.[KEY]
UNION ALL
SELECT FT_TBL.CategoryName, FT_TBL.Description, KEY_TBL.RANK
FROM Categories AS FT_TBL INNER JOIN 
   CONTAINSTABLE(Categories, Description, 
   'ISABOUT (
   fish weight (.4), beers weight (.2) )' ) AS KEY_TBL
   ON FT_TBL.CategoryID = KEY_TBL.[KEY]
ORDER BY KEY_TBL.RANK DESC

BUG: When You Use a Full Text Query Clause Greater Than 8K an Error Message Occurs That Does Not Indicate Overflow of Size Limit (285114)

SYMPTOMS

WORKAROUND

STATUS