| Groups | Blog | Home |
sql server full text search : Fulltext search always returns no results.
Hello. I am having a problem with fulltext search whereby it always returns no data. I have enabled full text search on the table and successfully created a catalogue, which according to the event log has been populated. I have tested with this query (found in this group) via Query Analyzer: select FulltextCatalogProperty(N'resourceFile', N'PageID') Which returns null (which i believe is correct). The query i am using is: SELECT * FROM tblPages, FREETEXTTABLE(tblPages, *,@searchTerm)searchTable WHERE [Key] = tblPages.PageID ORDER BY RANK DESC Which i also believe is correct. Anyone any ideas? On an unrelated (or possibly related) subject, i also often get this in my error logs - anyone know how to fix? 17052 : This SQL Server has been optimized for 8 concurrent queries. This limit has been exceeded by 1 queries and performance may be adversely affected. Thanks. marc
Is this MSDE? SQL FTS is not supported on MSDE,
-- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Marc" <marc.birkett@gmail.com> wrote in message news:1121073540.588455.25420@z14g2000cwz.googlegroups.com... > > Hello. > > I am having a problem with fulltext search whereby it always returns no > data. I have enabled full text search on the table and successfully > created a catalogue, which according to the event log has been > populated. > > I have tested with this query (found in this group) via Query Analyzer: > > select FulltextCatalogProperty(N'resourceFile', N'PageID') > > Which returns null (which i believe is correct). > > The query i am using is: > > SELECT * FROM tblPages, > FREETEXTTABLE(tblPages, *,@searchTerm)searchTable > WHERE [Key] = tblPages.PageID ORDER BY RANK DESC > > Which i also believe is correct. Anyone any ideas? > > On an unrelated (or possibly related) subject, i also often get this in > my error logs - anyone know how to fix? > > 17052 : This SQL Server has been optimized for 8 concurrent queries. > This limit has been exceeded by 1 queries and performance may be > adversely affected. > > Thanks. > > marc >
Marc,
Could you post the full output of -- SELECT @@version -- as this is most important information when troubleshooting SQL FTS issues! While I suspect that you're using SQL Server 7.0, I also need the service pack level that you have installed. Because of the error message (17052), you may be hitting the issues in the following KB articles relative to SQL Server 7.0 Full-text Search: 230036 BUG: Heavy Full Text Query Activity Results in Unexpected Timeout Errors http://support.microsoft.com/default.aspx?scid=KB;en-us;Q230036 230103 BUG: Cannot Have More than Eight Full Text Joins and Operations http://support.microsoft.com/default.aspx?scid=KB;en-us;Q230103 Unfortunately and again assuming that you're using SQL Server 7.0, this error cannot be fixed as it is by design for SQL Server 7.0, and your only solution is to upgrade to SQL Server 2000. If you are not using SQL Server 7.0, could you provide more details about other SQL Full-text Search queries you may have executing on your server? Thanks, John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "Marc" <marc.birkett@gmail.com> wrote in message news:1121073540.588455.25420@z14g2000cwz.googlegroups.com... > > Hello. > > I am having a problem with fulltext search whereby it always returns no > data. I have enabled full text search on the table and successfully > created a catalogue, which according to the event log has been > populated. > > I have tested with this query (found in this group) via Query Analyzer: > > select FulltextCatalogProperty(N'resourceFile', N'PageID') > > Which returns null (which i believe is correct). > > The query i am using is: > > SELECT * FROM tblPages, > FREETEXTTABLE(tblPages, *,@searchTerm)searchTable > WHERE [Key] = tblPages.PageID ORDER BY RANK DESC > > Which i also believe is correct. Anyone any ideas? > > On an unrelated (or possibly related) subject, i also often get this in > my error logs - anyone know how to fix? > > 17052 : This SQL Server has been optimized for 8 concurrent queries. > This limit has been exceeded by 1 queries and performance may be > adversely affected. > > Thanks. > > marc >
one more point SQL 2005 does interpolation, so this will work
declare @searchTerm varchar(200) set @searchTerm="microsoft" SELECT * FROM tblPages, FREETEXTTABLE(tblPages, *,@searchTerm)searchTable WHERE [Key] = tblPages.PageID ORDER BY RANK DESC In previous versions of SQL Server you would have to do something like this declare @searchTerm varchar(200) set @searchTerm="microsoft" declare @searchphase varchar(2000) select @searchphrase= "SELECT * FROM tblPages,FREETEXTTABLE(tblPages, *," +char(39) +char(34)+ @searchphrase select @searchphrase=@searchphrase+ char(34)+char(39)+")searchTable WHERE [Key] = tblPages.PageID ORDER BY RANK DESC" -- Hilary Cotter Looking for a SQL Server replication book? http://www.nwsu.com/0974973602.html Looking for a FAQ on Indexing Services/SQL FTS http://www.indexserverfaq.com [quoted text, click to view] "Marc" <marc.birkett@gmail.com> wrote in message news:1121073540.588455.25420@z14g2000cwz.googlegroups.com... > > Hello. > > I am having a problem with fulltext search whereby it always returns no > data. I have enabled full text search on the table and successfully > created a catalogue, which according to the event log has been > populated. > > I have tested with this query (found in this group) via Query Analyzer: > > select FulltextCatalogProperty(N'resourceFile', N'PageID') > > Which returns null (which i believe is correct). > > The query i am using is: > > SELECT * FROM tblPages, > FREETEXTTABLE(tblPages, *,@searchTerm)searchTable > WHERE [Key] = tblPages.PageID ORDER BY RANK DESC > > Which i also believe is correct. Anyone any ideas? > > On an unrelated (or possibly related) subject, i also often get this in > my error logs - anyone know how to fix? > > 17052 : This SQL Server has been optimized for 8 concurrent queries. > This limit has been exceeded by 1 queries and performance may be > adversely affected. > > Thanks. > > marc >
Apologies for the lateness of this reply, ive been a bit busy! the
results of select @@ version are: Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Personal Edition on Windows NT 5.2 (Build 3790: Service Pack 1) Also this is the first time ive tried to use full text search so its the only fulltext search query on the server. thanks, marc
Hilary.
Your first query still returns the same, the second returns nothing - not even column names! ta marc
[quoted text, click to view] "Marc" <marc.birkett@gmail.com> wrote in message news:1121782154.811864.158350@f14g2000cwb.googlegroups.com... >I am still not further forward with this, i have reduced my query to: > > CREATE PROCEDURE spGetResults1 > @searchTerm1 varchar > AS > SELECT * FROM tblPages > WHERE FREETEXT(*,@searchTerm1) > GO > > and still it returns nothing. It is almost as though my table is empty > (which it isnt). My catalogue is also definately populated. Anyone have > any ideas? As Hilary pointed out, that syntax only works in SQL Server 2005 - you have SQL Server 2000 so you cannot use a variable in the FREETEXT call. Also you didn't specify the size of @searchTerm1 so it's set to a 1 character string. Try this: CREATE PROCEDURE spGetResults1 @searchTerm1 varchar(20) AS DECLARE @sql nvarchar(100) SET @sql = 'SELECT * FROM STK WHERE FREETEXT(*,' + char(39) + char(34) + @searchTerm1 + char(34) + char(39) + ')' EXEC sp_executesql @sql GO This allows up to 20 characters to be passed in as the search term - obviously you can increase this as needed. If you increase it by a lot make sure you increase the size of @sql too or else you'll have errors caused by truncating the constructed sql. Dan
I am still not further forward with this, i have reduced my query to:
CREATE PROCEDURE spGetResults1 @searchTerm1 varchar AS SELECT * FROM tblPages WHERE FREETEXT(*,@searchTerm1) GO and still it returns nothing. It is almost as though my table is empty (which it isnt). My catalogue is also definately populated. Anyone have any ideas? Thanks, Marc
Ah. The only problem was i had missed out the size of the @searchTerm1
variable on the original query. This works: "CREATE PROCEDURE spGetResults @searchTerm varchar(20) AS SELECT * FROM tblPages, FREETEXTTABLE(tblPages, *,@searchTerm)searchTable WHERE [Key] = tblPages.PageID ORDER BY RANK DESC GO" Thanks for your help people. Marc
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |