Microsoft KB Archive/285114

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

Article ID: 285114

Article Last Modified on 10/17/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q285114



BUG #: 351616 (SHILOH)

BUG #: 101223 (SQLBUG_70)



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 ( &quot;sql&quot; weight (1.0), &quot;experience&quot; weight (0.1), &quot;asp&quot; weight (0.1), &quot;vb&quot; weight (0.1),.................&quot;a&quot; 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 ( &quot;sql&quot; weight (1.0), &quot;experience&quot; weight (0.1), &quot;asp&quot; weight

(0.1), &quot;vb&quot; weight (0.1), &quot;programmer&quot; weight (0.1), &quot;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 



STATUS
Microsoft has confirmed this to be a problem in SQL Server SQL Server 7.0.

Microsoft has confirmed this to be a problem in SQL Server SQL Server 2000.

Additional query words: FT MSSEARCH full-text

Keywords: kbbug kbpending KB285114

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.