We help IT Professionals succeed at work.

SQL 2000 FTI - CONTAINS clause not working when we moved from WIN2003 Server to WIN 2008 Server

Chuck_aa
Chuck_aa asked
on
We have been using FTI Full Text Indexing on our SQL2000 / Win 2003 environment and it was working perfectly.

We migrated our SQL2000 over to a Win 2008 environment and now all of the CONTAINS clauses are throwing errors, however the LIKE clauses work fine.  (Of course we re indexed after moving to the Win 2008 environment).

Can you tell me if the CONTAINS clauses are deprecated when moving to a Win 2008 server, using SQL 2000 ?

Thanks,
Chuck.
Comment
Watch Question

>>Can you tell me if the CONTAINS clauses are deprecated when moving to a Win 2008 server, using SQL 2000 ?
<<
No, they are not
Certainly LIKE will work because it does not depend on FTI.
1) Can you please post the error message you're getting?
2) Did you check that the FT search service is working? Try running this command:

exec sp_fulltext_database 'enable'
3) Did you recreated the catalog? this link will help you with all the steps you need to have FT working
http://www.databasejournal.com/features/mssql/article.php/3441981/Full-Text-Search-on-SQL-2000-Part-1.htm 
 

Author

Commented:
Hi ralmada,
Thanks for the reply.
1. Ahh, ok, didn't know that LIKE was not dependent on FTI, thanks for that.
2. Here is the error I'm getting
Search on full-text catalog 'Survey_Index' for database ID 7, table ID 686013575 with search condition 'attention' failed with unknown result (8004110a).
Ran the command, and appears FT is running.
3. Yes that is a great article, I've followed prior, and yes I've created, defined, populated catalog, and started full population.
 
Thanks for your help!
 
Chuck.
 
hmmm, Error 8004110a seems to be "The specified alias for the given entity in the condition does not exist." check that the column that you're trying to do the FT search is the correct one or is spelled correctly.
If not please try deleting and recreating the catalog again.

Author

Commented:
1. OK, I deleted and recreated with the same error.
2. I created a test table with same parameters, moved the data over, indexed it, and same error !
3. I looked to see where the index is being created, and this is the path
C:\Program Files (x86)\Microsoft SQL Server\MSSQL$SQL2000\FTData
When I look there, I don't really see anything specific to my particular table?... (not really sure what I shoudl be looking for)
4. I might add too, that this is a 'NAMED INSTANCE'  of SQL2000 and the main install is SQL2008.  I wonder if perhaps the NAMED INSTANCE is causing the catalog not to be created/found ?
 
Thanks,
Chuck.
 

Author

Commented:
I just ran this too
 sp_help_fulltext_catalogs
and results look good....
23 tblSurvey C:\Program Files (x86)\Microsoft SQL Server\MSSQL$SQL2000\FTData 0 1
So it seems indeed it is creating the catalog at this browse path.

 
>>
4. I might add too, that this is a 'NAMED INSTANCE'  of SQL2000 and the main install is SQL2008.  I wonder if perhaps the NAMED INSTANCE is causing the catalog not to be created/found ?<<
Yeah it could be specially if you call it SQL 2008. Remember that there's a 2008 version of SQL.
 

Author

Commented:
Yes, I bet the named instance has something to do with it.  I will pursue that.  Yes I know there's 2008.... just that Enterprise Manager and Query Analyzer are no longer used. Must learn to navigate with SQL Management Studio and VS2008.... just too much to learn at the moment.  Thanks for your help!

Author

Commented:
Just wanted to add that yes there are two install's of SQL on our box, SQL2000 and SQL2008.
I'll eventually learn 2008...... but for now I'm mainly using the NAMED INSTANCE, or secondary load of SQL2000 on this box.
Take Care,
Chuck.