Hello all. I'm new to this forum and of course I found it because I have a problem I can't find the answer to.
I have full-text indexing on a couple of tables in my database. Periodically, maybe once every couple of weeks, I get an error on a webpage that runs a stored procedure searching these tables. The error is as follows:
"Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Products' because it is not full-text indexed."
I'm running Classic ASP, and the stored procedures run thousands of times a day. As soon as I get the error email from the website containing the url that created the error, I immediately click on it, but by that time, the error has stopped and everything is running properly.
The timing of the error does not coorespond to any other processes. My transactionals run on the hour and the index is re-built at 12:45am, yet I just received the error again at 12:45pm (no, I checked the clocks and they are right).
I do not have replication running and I don't know where to look next.
I don't know whether this is the right place to ask this question. I didn't get the answer from sql forums may be someone here can help. I need to use freetext on two columnssomething likefreetext(column1+column2,@Search) this will give error "Incorrect syntax near '+'."Any One know the correct syntax. Thanks
I have a vb page, which is a simple front end so I can edit text from anywhere.
The person who is going to use it knows no html, and so I am trying to get freetextbox to work.
I am getting the following error: Server Error in '/' Application. --------------------------------------------------------------------------------
ExecuteNonQuery: CommandText property has not been initialized 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.InvalidOperationException: ExecuteNonQuery: CommandText property has not been initialized
Source Error:
Line 81: Line 82: mySelectCmd.Connection.Open() Line 83: mySelectCmd.ExecuteNonQuery() Line 84: MyConnString.Close() Line 85:
The pages code is below:
<%@ Page Language="VB" Debug="True" validateRequest="False"%> <%@ Register TagPrefix="FTB" Namespace="FreeTextBoxControls" Assembly="FreeTextBox" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data" %> <script runat="server"> Dim MyConnString As SqlConnection Dim mySelectCmd As SqlCommand Dim mySelectQuery As String dim myExecuteQuery As String
Sub Page_Load(Sender As Object, E As EventArgs)
If Not Page.IsPostBack Then ReadMyData() end if end sub
Public Sub ReadMyData() MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
Dim dt As DataTable Dim dr As DataRow dt = New DataTable
mySelectQuery = "select * from content_text" mySelectCmd = New SqlCommand( mySelectQuery, MyConnString) MyConnString.Open() Dim myReader As SqlDataReader = mySelectCmd.ExecuteReader() Try While myReader.Read()
End While Finally myReader.Close() MyConnString.Close() End Try
Pubs.DataSource = DT Pubs.Databind()
End Sub
Sub Pubs_Cancel(Sender As Object, E As DataGridCommandEventArgs) Pubs.EditItemIndex = -1 ReadMyData() End Sub
Sub Pubs_Edit(Sender As Object, E As DataGridCommandEventArgs) Pubs.EditItemIndex = CInt(e.Item.ItemIndex) ReadMyData() End Sub
Sub Pubs_Update(Sender As Object, E As DataGridCommandEventArgs)
MyConnString = New SqlConnection( "Server=*;UID=*;PWD=*;Database=content_db")
myExecuteQuery= "Update content_text Set title=@title, text=@text, section_no=@section_no where id=@id" mySelectCmd = New SqlCommand( mySelectQuery, MyConnString)
dim e_id as integer dim e_title, e_text, e_section_no as string
I tried the following select: select * from patientprofile where contains (BillingNotes, '572576-02')
And got the errormessage: Server: Msg 7601, Level 16, State 2, Line 1 Cannot use a CONTAINS or FREETEXT predicate on table 'patientprofile' because it is not full-text indexed.
It shows BillingNotes as a text column
Is there a differnet way I can search this note field for records that contain that text value (572576-02)
Is there a way I could achieve something like this. freetext(FirstName+LastName,@Name) FirstName and LastName are two columns in my database table. What I am trying here is allowing the user to type their first name , last name or both. and write a search which can find there record for all the three cases. Please Advice.
I'm attempting to query the MS Index Server. I allow the users to supply the search text and when doing that it is best to pass the text into the query via a parameter to protect against SQL Injection attacks. However, for some reason I cannot get the FREETEXT predicate to work with a parameter. The FREETEXT predicate works fine when the query is concatenated together. I'd like an example of the FREETEXT predicate working with MS Index Server (Windows 2003). The following line works just fine: cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, '" + searchText + "') order by rank desc " I'd like the following lines to work:cmd.Parameters.AddWithValue("@SearchText", searchText) cmd.CommandText = "select doctitle, filename, vpath, rank, characterization from scope() where FREETEXT(Contents, @SearchText) order by rank desc "
So now I'm wondering if the MS Index Server doesn't support the use of a parameter in this way or if I somehow managed to get the syntax wrong. Thoughts?
Hi, I realised that I am not able to do a FREETEXT search on multiple table, example:SELECT * FROM [tStaffDir], [tStaffDir_ClientExp], [tStaffDir_CoreSpecs], [tStaffDir_GlobalExp], [tStaffDir_Lang], [tStaffDir_PrevEmp], [tStaffDir_TerEdu] WHERE FREETEXT(*, @Name) ORDER BY [Name] Can I use FREETEXTTABLE instead? How do I go about doing it?
I installed SQL server 7.0 beta 3 and I have been experimenting with the full text search capabilities. The catalogs appear to build correctly, but when I issue a query against the full text database, isql/w always returns with `contains` is not a recognized function name. I am confident of my query syntax, since I can execute an identical query against a database without full text search enabled and I get an error stating that the target database does not have full text searching enabled. Has anybody else encountered this? I am hoping that I am missing something obvious/simple.
Hi,I am using MS SQL server 2005 and wondering how to sort my results by rank using FREETEXT on multiple columns. Is there a way to do this? My two colums are:title and description
I am trying to do a freetext filter with mutiple columns using a column list, but I can't get the syntax down for multiple column list. First, am I am going about this the right way...Do I need to be doing both? Second why doesn't mutiple columns work. I can't find any good samples online. What I am trying to accomplish is a refined search stored procedure that uses the freetext to do the search refinement. Any help would be appreciated.
select
b.rank,
a.ProductID,
a.ProductName,
a.Sequence,
a.ProductImage,
a.ItemID,
a.ItemName,
a.ManufacturerItemCode,
a.ItemImage,
a.ItemSourceID,
a.PackageID,
a.BrandID,
a.BrandName,
a.ManufacturerID,
a.ManufacturerName,
a.ProductCategoryID,
a.CategoryID,
a.CategoryName,
d.CustomerGroupName,
isnull(h.PackageDescription,a.ItemPKG) as PKG,
case g.StockStatus
when 1 then 'Yes'
when 0 then 'No'
else ''
end as StockStatus,
isnull(g.StandardUnitPrice,a.ListPrice) as Price,
isnull(j.SupplierAbbreviation,a.ManufacturerAbbreviation) as ItemSource
from
dbo.vw_mcProductItem a
inner join freetexttable(dbo.vw_mcProductItem, (ProductName,ItemName,ManufacturerItemCode,ItemPKG,BrandName,ManufacturerName,ManufacturerAbbreviation,CategoryName), @SearchWord) as b ON a.ItemID = b.[KEY]
inner join [dbo].[mcCustomerGroupItem] c on c.ItemID = a.ItemID
inner join [dbo].[mcCustomerGroup] d on d.CustomerGroupID = c.CustomerGroupID
inner join [dbo].[mcCustomerGroupCustomer] e on e.CustomerGroupID = d.CustomerGroupID
inner join [dbo].[mcCustomerUser] f on f.CustomerID = e.CustomerID
left outer join [dbo].[mcSupplierItem] g on g.ItemID = a.ItemID
left outer join [dbo].[mcPackage] h on h.PackageID = g.SellingPackageID
left outer join [dbo].[mcItemSource] i on i.ItemSourceId = a.ItemSourceId
left outer join [dbo].[mcSupplier] j on j.SupplierID = g.SupplierID
where
d.CustomerGroupID = @CustomerGroupID
and f.UserID = @UserID
and FREETEXT(BrandName,ManufacturerName,CategoryName, @SearchWord)
Please have a look at the following two queries, the purpose of which is to find which ten users (represented by 'Username') have created the most records which contain the term 'foo':
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
WHERE FREETEXT(*, 'foo')
GROUP BY Username
ORDER BY [Count] DESC
SELECT TOP 10 Username, COUNT(*) AS [Count] FROM Options
JOIN FREETEXTTABLE (Options, *, 'foo', 500) ct
ON OptionID = ct.[KEY]
GROUP BY Username
ORDER BY [Count] DESC
They both produce the same result set. However, I am wondering which is more performant. At first glance, it would seem the first one would be. It doesn't involve a JOIN and should, therefore, be more efficient.
But this depends on how the FREETEXT expression is evaluated. My concern is that internally, SQL Server would generate an entire recordset based on 'WHERE FREETEXT(*, 'foo')', which could be thousands of records, and only then restrict this to the TOP 10 by COUNT.
If this does happen, then it would be better to join to a FREETEXTTABLE, where I can at least restrict the result set using the 'top_n_by_rank' parameter (which is set as '500' in this case, as this seems a good balance of performance against the likely number of duplicates I will get in my FREETEXTTABLE results).
So... I am worrying about this unnecessarily? Should I just use the simpler first version?
