asked on

Full-text search problem

I am using VS.NET 2005 and SQL Server 2005
I am getting an error running the following query.

SELECT [notes] FROM [Info] WHERE (CONTAINS([notes], @notes)) ORDER BY [info_date] DESC

The notes field is a varchar field with a length of 7000.
I have never created an index before. Maybe this is what I need to do? When I try to create a Full-Text Index it tells me 'Creation of the full-text index is not available. Database is not enabled to allow for full-text indexing.'
How do I enable it?
Any ideas?

Here is the error.

Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Info' because it is not full-text indexed.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.Data.SqlClient.SqlException: Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Info' because it is not full-text indexed.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[SqlException (0x80131904): Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Info' because it is not full-text indexed.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +95
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +83
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +345
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +3152
System.Data.SqlClient.SqlDataReader.ConsumeMetaData() +58
System.Data.SqlClient.SqlDataReader.get_MetaData() +127
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +295
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1083
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +337
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) +45
System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) +163
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior behavior) +35
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(CommandBehavior behavior) +32
System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +183
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior) +308
System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable) +153
System.Web.UI.WebControls.SqlDataSourceView.ExecuteSelect(DataSourceSelectArguments arguments) +3060
System.Web.UI.WebControls.BaseDataList.GetData() +54
System.Web.UI.WebControls.DataList.CreateControlHierarchy(Boolean useDataSource) +381
System.Web.UI.WebControls.BaseDataList.OnDataBinding(EventArgs e) +76
System.Web.UI.WebControls.BaseDataList.DataBind() +85
System.Web.UI.WebControls.BaseDataList.EnsureDataBound() +82
System.Web.UI.WebControls.BaseDataList.OnPreRender(EventArgs e) +26
System.Web.UI.Control.PreRenderRecursiveInternal() +148
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Control.PreRenderRecursiveInternal() +233
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5036

Anthony Perkins

You need to define the Full-Text Search and do a Full Population before you can use it. I suggest you do some reading on the subject, as it is somewhat more complex than you may have anticipated.

ASKER CERTIFIED SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jettman26

ASKER

Thanks, that worked!

jettman26

ASKER

acperkins,
Please visit https://www.experts-exchange.com/questions/21501438/Getting-error-trying-to-do-a-search-of-a-large-field-in-database.html
and post this same answer there so that I can give you more points.
:)

Anthony Perkins

>>post this same answer there so that I can give you more points.<<
I appreciate it. But since that is a duplicate question and the total exceeds the total of 500 points allowed, I suggest you post a message in Community Support to have the question deleted and the points refunded. Make sure to include a reference to that question.

jettman26

ASKER

ok.
I didn't know where to post it, so I posted it in both places.
Sorry.

Anthony Perkins

>>I didn't know where to post it, so I posted it in both places.<<
No problem and I appreciate the gesture. Usually the way it is done is as follows:

What if my question fits more than one technical area?
https://www.experts-exchange.com/Databases/Microsoft_SQL_Server/help.jsp#hi262

Hillsman

The accepted solution works a treat. Had to manually add each string column to the FTC. Trying to create this via Management Studio or Enterprise Manager simply wasn't working.

Many thanks,

Hillsman