SQL Server 2000 Errors

[psimth]

I'm getting SQL Server errors using CONTAINS with a search term like horse (but no error with horses). Other words causing errors are cat, dog, seismic, safety, accountants, car, cars, hair, washing, attorneys, carpet, family

Same ColdFusion application worked fine on SQL7 with Website web server. Now on IIS6 on Windows 2003 web server.

Any ideas?

best, paul

[Fernis]

What's the SQL error? You can't expect people to be able to help you, without being more specific.

Are the databases identical (the contents too) with the old and the new web server? Should your query return rows with both "horse" and "horses"? Do you have nested conditions / selects / etc?

[psimth]

The errors are so off the wall that IF anyone had seen them before I figured the info I provided was enough. In any event here's the error message:

=========================
cfcatch.detail [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2: Incorrect syntax near ','.

cfcatch.message ODBC Error Code = 37000 (Syntax error or access violation)
==============================

I confirm that the search term exists right before the query:

===============================================
<cfquery name="REQUEST.GetHeadings" datasource="XXXXX" username="XXX" password="XXX" cachedwithin="#CreateTimeSpan(0,0,0,0)#">
SELECT ID,Class_ID,Class_Name,Pages
FROM YellowPages (nolock)
WHERE CONTAINS(Class_Name,'"#VARIABLES_SEARCHTERM#"')
ORDER BY Class_Name
</cfquery>
========================================

Results are the same when I replace '"#VARIABLES_SEARCHTERM#"' with the CFQUERYPARAM equivalent.

Now the big surprise. Moving back to SQL7 gives the same result (MANY strange errors).

Searching for "hors*" does not cause an error.

But searching for "hors*" does not find instances of "horse" it should have.

Query should have returned rows with ALL the search terms I listed in my original posting If a term cannot be found that result used to be returned to the user.

No nested conditions.

Pretty strange that the SQL7 database should all of a sudden (after literally years of serving the same application) start throwing so many errors.

The common feature is IIS6 & Window2003, plus the database. The database is the same on SQL2000 & SQL7. SQL2000 created its own index.

best, paul

[MikerRoo]

I suspect you have stopped or misconfigured the Microsoft Full-text and search "services".

Try the generated SQL (which you should have posted here with the error message) in Query analyzer.
If it doesn't work, then go to a SQL forum and ask for help creating or repairing the text index and the text search system.

In the off chance that it works in QA but not in CF, attach the FULL error message (which includes generated SQL) and the unedited code of the errored template to this thread.

[Newsgroup_User]

> cfcatch.detail [Microsoft][ODBC SQL Server Driver][SQL Server]Line 2:

Which version of CF are you using? If it's anything after CF5, why are you
using an ODBC driver? As opposed to a JDBC driver, I mean.

Sounds to me like the upgrade to MS-SQL2k might have updated your ODBC
drivers too, and there's something "bung" (technical term, that one ;-)
with them.

Try the JDBC driver instead.

And as someone else said... post your FULL SQL, including the resolved
VARIABLES_SEARCHTERM.

--
Adam

[BKBK]

The trouble could be with how you define VARIABLES_SEARCHTERM. First of all, remove the ambiguous cachedwithin="#createtimespan(0,0,0,0)#". We can now test whether SQL really cannot stand seismic cats and dogs, attorneys washing the family car for safety or accountants whose horses drop hair on the carpet. Run the following

<cfquery name="REQUEST.GetHeadings" datasource="XXXXX">
SELECT ID,Class_ID,Class_Name,Pages
FROM YellowPages (nolock)
WHERE CONTAINS(Class_Name,'"horses" or "cat" or "dog" or "seismic" or "safety" or "accountants" or "car" or "cars" or "hair" or "washing" or "attorneys" or "carpet" or "family"')
ORDER BY Class_Name
</cfquery>

[DaveyS]

you might also be looking at a preserveSingleQuotes() problem