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
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.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...;en-us;Q230036
230103 BUG: Cannot Have More than Eight Full Text Joins and Operations
http://support.microsoft.com/default...;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/
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.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
"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121073540.588455.25420@.z14g2000cwz.googlegro ups.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
|||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
|||"Marc" <marc.birkett@.gmail.com> wrote in message
news:1121782154.811864.158350@.f14g2000cwb.googlegr oups.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
|||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