Efficiency Of A 'SELECT TOP' Style GROUP BY Query: FREETEXT Vs. FREETEXTTABLE
Aug 11, 2007
Hi,
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?
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 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)
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the resluts I want using UNION, but this is not practical when there is more than a few groups. Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
I am trying to return a result set that gives me the top 20 results for EACH group (PERILNAME in this case), not the top 20 results of the whole result set.
I have been able to create the results I want using UNION, but this is not practical when there is more than a few groups. I think it should be possible using a derived table, but I am not sure how to do it! Below is the code the returns the results I am after, any ideas how to achieve this another way?
SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT) AS [Total Value], COUNT(loc.LOCID) AS [Num Locs],loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Earthquake' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Windstorm' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Tornado/Hail' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR UNION SELECT TOP 20 LookupPeril.PERILNAME, accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, SUM(loccvg.VALUEAMT), COUNT(loc.LOCID) ,loccvg.VALUECUR FROM loc INNER JOIN loccvg ON loc.LOCID = loccvg.LOCID INNER JOIN accgrp ON loc.ACCGRPID = accgrp.ACCGRPID INNER JOIN RMS_Reports.dbo.LookupPeril LookupPeril ON loccvg.PERIL = LookupPeril.PERILCODE WHERE LookupPeril.PERILNAME = 'Flood' GROUP BY accgrp.ACCGRPNUM, accgrp.ACCGRPNAME, LookupPeril.PERILNAME, loccvg.VALUECUR ORDER BY LookupPeril.PERILNAME, [Total Value]
I need some help in returning the top 3 records in each group ie I have a table containing a list of stores with the address details and I need to return the top 3 records in each postcode area. The Postcode field contains the postcode area ie BN1, BN2 etc. Many thanks.
We have a query that uses the Full-text index on a view that's returning duplicate rows. We thought maybe it was the way we were joining, but we were able to simplify the query as much as possible and it still happens. Here's the query:
Code Snippet SELECT * FROM FREETEXTTABLE(vwSubtable, TitleSearch, 'Across five Aprils') AS KEY_TBL ORDER BY RANK DESC
vwSubtable is an indexed view that contains some of the columns in our original table, and is also filtering out some rows from the main table in a where clause. There are no joins in the view.
This seems like it's about as simple a query as we could get. It will return some rows twice (ie. the same primary key row is returned back as two separate rows in the resultset). This is a problem since we're filling a datagrid, which is throwing a ConcurrencyException because the primary key is already in there.
I made sure we have SP2 installed on my SQL Server. Any ideas on what might be happening?
I have a table T (a1, ..., an, time, id). I need to select those rows that have different id (GROUP BY id), and from each "id group" the row that has the latest field 'time'. Something like SELECT a1, ..., an, time, id ORDER BY time DESC GROUP BY id. This is the wrong syntax and I don't know how to handle this.
I have the following 2 tables:location:placelftrgt-------------------Europe099England110France1120Italy2130Asia100199London1212staff:namelocLft--------------Edwards0Smith1Leveil11Rossi21Lee12Chan100location uses the Celko hierarchy model.I wish to retrieve for a location the names of all staff within it andthe hierarchy of place associated with that member of staff, eg aquery for Europe should return all staff in Europe, and for Lee I wishto return Lee-London, Lee-England, Lee-Europe etc.I can achieve this using a subquery, ieSELECT name, placeFROM staff, locationWHERE name IN (SELECT nameFROM staff, locationWHERE place='Europe' And locLft>=location.lft AndlocLft<=location.rgt)AND locLft>=lft AND locLft<=rgtBut is this the most efficient way of doing so?Thanks
Suppose I have two tables(Customer and Order) which are as follows:
Code: Customer customer_id first_name
[Code]....
Another thing I am concerned about is that in the line INNER JOIN Order ON Customer.customer_id = Order.customer_id , I have written Customer.customer_id on the left hand side. Is that correct or I should write it on the right hand side of the equal sign?
I want to make a CSS Style solution for my reports. I have few ideas related to this, changing the report properties on the fly, but I am not sure for which I should go for.
- We know that we can put expression on reports almost everywhere, and can also call custom code for setting up those expressions. i.e.
Public Shared Function GetValue(ByVal Key As String) As String
Dim myDataReader As SqlDataReader Dim mySqlConnection As SqlConnection Dim mySqlCommand As SqlCommand
mySqlConnection = New SqlConnection("server=localhost;Trusted_Connection=yes;database=tempMIQB") mySqlCommand = New SqlCommand("SELECT * FROM [Properties] Where PropertyName='" & Key & "'", mySqlConnection) mySqlConnection.Open() myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection)
If myDataReader.Read() Then GetValue = myDataReader.Item("PropertyValue").ToString Else GetValue = "None" End If
End Function
and in textbox or any where I can say =Code.GetValue("BGColor") or from .NET dll.
For going further on with this idea I can create an .NET dll and get the list from database or XML file. (Please give suggestions for performance/scalability issue.)
- second idea is to write a custom application (script) which can go through all the reports and change the color and fonts and every thing.
- third is to use parameters and use Array.IndexOf method to search for the value, but in this case i need to add dataset to all reports and, I think we cannot access report properties i.e. Parameters from custom code, just to make a generic function to access parameter value.
It seems when I run the query with the set staticts IO on then statistic reports back with the 'work table', and the query takes 30+ sec. if the worktable is ommited(whatever the reason?) the query take less 1 sec.
Here is my take, I believe work table is created in tempdb...and if not then whole query is using the cached page, am I right?
if I am right then the theory is, if I increase the (via sp_configure) server min memory setting and min query memory, the query ought use the cached page and return in less 1 sec. (specially there is absolutely no one but me on the server), so far I can't make it go faster...what setting am I missing to make it run faster?
Another question is if the query can not avoid but use the tempdb, is it going to always be 30 sec+ time? why is tempdb involvement make it go so much slower?
I have two tables that are pretty standard I think. Table a has product descriptions and one of those fields is a price. I have a second table that contains fees based on the price. so table B looks like this.
min max fee 0 19.99 2.50 20.00 49.99 3.50 50.00 1000.00 5.50
the max ends up around a million just to be sure we cover all prices. my problem is this I need a very efficient query to poll all the values from A and the correct value from B. All the attempts I have made are not working. I also have to make sure this query is extremely efficient as it is executed several times a minute. If there is a better way in general to structure this I am all ears. I wanted to avoid placing the fees in the product table as the fees are updated often, but if its the only way to get this to work, then that is where I will go.
Hey guys i have a stock table and a stock type table and what i would like to do is say for every different piece of stock find out how many are available The two tables are like thisstockIDconsumableIDstockAvailableconsumableIDconsumableName So i want to,Select every consumableName in my table and then group all the stock by the consumable ID with some form of total where stockavailable = 1I should then end up with a table like thisEpson T001 - Available 6Epson T002 - Available 0Epson T003 - Available 4If anyone can help me i would be very appreciative. If you want excact table names etc then i can put that here but for now i thought i would ask how you would do it and then give it a go myself.ThanksMatt
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
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 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 trying to build a search function using FREETEXTTABLE to provide rankings of the results. I've got it working on one of the tables using a query that looks something like this: SELECT DISTINCT title, recordid, rank FROM mn_records INNER JOIN FREETEXTTABLE(mn_records,*, 'sony') AS ft ON mn_records.recordid = ft.[key] ORDER BY rank DESC
The problem is that I want to search two tables mn_records and mn_items. A record has multiple items linked to it and some of the fields in the items table are different for the same record. I have the functionality working using FREETEXT: SELECT DISTINCT title, recordid FROM mn_records, mn_items WHERE mn_records.recordid = mn_items.parentrecord AND (FREETEXT (mn_records.*, 'sony') OR FREETEXT (mn_items.*, 'sony'))
How can I properly join these two tables and the FREETEXTTABLE rank so that the search results will only contain distinct record_ids yet still search the information contained in mn_items?
I have used Full-Text Search in a stored procedure with English words whithout any problems..But when i use it with Arabic words it gives me THE Error:
Server: Msg 7619, Level 16, State 1, Procedure SearchProject, Line 19 Execution of a full-text operation failed. A clause of the query contained only ignored words.
The stored procedure :
CREATE PROCEDURE SearchProject ( @SearchString nvarchar(500), @CultureName nvarchar(50), @HowManyResults int OUTPUT ) AS CREATE TABLE #SearchTable ( FieldNO int, ProjectNO int, ProjectName nvarchar(200), ProjectDescription nvarchar(1000), ProjectImage nvarchar(1000), CultureID int )
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID) SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM FREETEXTTABLE(Project_Locale,*,@SearchString) AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID JOIN Project AS P ON P.ProjectNO=PL.ProjectNO WHERE PL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID) SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM Project_Locale AS PL ,Project AS P, ProjectField_Locale AS FL, FREETEXTTABLE(ProjectField_Locale,*,@SearchString) AS FT2 WHERE FL.FieldNO=P.FieldNO AND FL.FieldCultureID=FT2.[KEY] AND PL.ProjectNO=P.ProjectNO AND PL.CultureID=dbo.GetCultureID(@CultureName) AND FL.CultureID=dbo.GetCultureID(@CultureName)
INSERT INTO #SearchTable (FieldNO,ProjectNO,ProjectName,ProjectDescription,ProjectImage,CultureID) SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM Project_Locale AS PL ,Project AS P, Feature_Locale AS PFEA, ProjectFeature AS FP, Feature AS F, FREETEXTTABLE(Feature_Locale,*,@SearchString) AS FT3 WHERE FT3.[KEY]=PFEA.FeatureCultureID AND FP.ProjectNO=P.ProjectNO AND FP.FeatureNO=PFEA.FeatureNO AND PL.ProjectNO=P.ProjectNO AND F.FeatureNO=PFEA.FeatureNO AND PFEA.CultureID=dbo.GetCultureID(@CultureName) AND PL.CultureID=dbo.GetCultureID(@CultureName)
SELECT @HowManyResults=COUNT(DISTINCT ProjectNO) FROM #SearchTable SELECT DISTINCT * FROM #SearchTable RETURN GO
I called the stored procedure using the following(in the Query Analayzer) :
USE nabeel1eagle DECLARE @HowManyResults int EXEC SearchProject 'ملÙ?ات','ar-SA',@HowManyResults OUTPUT
but when I use the previous code directly(without calling the stored procedure using EXEC) in the Query Analyzer it works fine.Like the following (part of the code)code:
SELECT P.FieldNO, PL.ProjectNO,PL.ProjectName,PL.ProjectDescription,P.ProjectImage,PL.CultureID FROM FREETEXTTABLE(Project_Locale,*,'ملÙ?ات') AS FT JOIN Project_Locale AS PL ON FT.[KEY]=PL.ProjectCultureID JOIN Project AS P ON P.ProjectNO=PL.ProjectNO WHERE PL.CultureID=dbo.GetCultureID('ar-SA')
Could any one help and tell me how to solve this problem?
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?
I have defined full-text indexing on a table a couple of months ago and the catalog was fully populated, it was working fine until now. But suddenly has stopped working, I have been scheduling incremental population on a regular basis and also have tried to remove and redefined full-text indexing but can't get it to work.
We are using freetexttable as our search function in our application and it seems to be partially working. I search for a word from a column of a table I included in the search catalog and it sometimes pickup the record and sometimes it doesn't. I got 2 records having 'business' as the keyword. And when I search that keyword, it only returned 1 record.
here is the codesnippet: select * from freetexttable(<tablename>, *, 'business')
i use two containstable (one of them with 'formsof') and one freetexttable attributes in a select command and i want to combine them with the logical 'or'.can i do this in the same command? what is the syntax?my code :select table1.field1,a.rank,b.rank,c.rankfrom table1containstable(table1,field,'"word"') as aor containstable(table1,field,'formsof(inflectional(word)') as b)or freetexttable(table1,field,'word') as cwhere table1.id=a.[key] and table1.id=b.[key] and table1.id=c.[key]the above syntax is wrong. i tried ',' instead of 'or' but the results were not right. thanks
I have the following code on a database that works perfectly (i.e. searching for 'print' will return results that have 'printer' in the field):
***************************************
INSERT INTO searchResults (articleId, articleTitle, articleSummary, articleType, reviewedBy, reviewedDate, submitDate, modifiedDate, appTitle, appVersion, rank, hasAccess) SELECT id, articleTitle, articleSummary, articleType, reviewedBy, reviewedDate, submitDate, modifiedDate, appTitle, appVersion, k.rank, hasAccess FROM articles As a INNER JOIN FREETEXTTABLE(articles, articlesummary, 'print') AS K ON a.id = K.[KEY]
***************************************
I am working on a new DB trying to accomplish the same thing, so I have the following code:
***************************************
SELECT id, firstName, lastName, k.rank FROM Pagers_Users As a INNER JOIN FREETEXTTABLE(Pagers_Users, lastName, 'smith') AS K ON a.id = K.[KEY] ORDER BY k.rank DESC
***************************************
However, this second block does not completely work (as I see it). When I enter the word 'smith' to serch for, it only brings back exact matches, not close calls, like 'smithe'.
I have also tried searching for other words (or partial words) and it only returns exact matches.
Is there something in setting up the Text Index that I forgot to turn on for "close" matches?
