Troubleshooting
Problem
Queries against fields using "contains" operator in the IBM Rational ClearQuest (CQ) query wizard may not return expected results when Microsoft SQL Server (SQL Server) FTS (Full Text Search) is enabled database-side.
Symptom
When SQL Server's Full Text Search (a database-side indexing, not to be confused with ClearQuest Full-Text Search) feature is enabled against a table, the ClearQuest query wizard will generate SQL in a different manner when the operator "contains" is used for a SQL Server database with SQL Server FTS vs. SQL with SQL Server FTS unenabled.
Non-SQL Server FTS enabled record types would have a SQL statement based on the SQL "like" operator with a % wildcard:
select T1.dbid,T1.id,T1.description,T1.headline from Defect T1 where T1.dbid <> 0 and ((T1.description like '%this is my search text%'))
When SQL FTS is enabled, the SQL statement is written using the SQL "contains" operator and uses a different wildcard:
select T1.dbid,T1.id,T1.description,T1.headline from Defect T1 where T1.dbid <> 0 and contains(T1.description, '"this is my search text*"')
These two different statements return different results.
When enabling SQL Server FTS, it is best to only search for a single term. When searching for multiple terms such as this example: "this is my search text" you may receive no hits.
Environment
This concern only applies to Microsoft SQL Server when SQL Server FTS is enabled. This does not apply to any other ClearQuest supported database vendor platform.
SQL Server Full Text Search is not enabled by default in a SQL Server database and deliberate SQL Server administration steps would have been taken to enable this feature in a ClearQuest database hosted by SQL Server.
Resolving The Problem
If the SQL Server FTS enabled queries are not returning expected results, it is recommended that SQL Server FTS be disabled against the SQL server table representing that CQ record type.
If text searching is a desired functionality, it is recommended to use ClearQuest's Full-Text Search functionality which can be enabled on the ClearQuest Web Change Management (CM) server. This feature allows faster, more complex search capabilities than using "contains" against a string field within a SQL Server.
Was this topic helpful?
Document Information
Modified date:
01 August 2018
UID
swg21679424