Fulltext search always returns no results.

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Marc - 11 Jul 2005 10:17 GMT

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

Reply to this Message

Hilary Cotter - 11 Jul 2005 11:06 GMT

Is this MSDE? SQL FTS is not supported on MSDE,

Signature

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

>
> Hello.
[quoted text clipped - 28 lines]
>
> marc

Reply to this Message

John Kane - 11 Jul 2005 16:15 GMT

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

Signature

SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/

> Hello.
>
[quoted text clipped - 27 lines]
>
> marc

Reply to this Message

Hilary Cotter - 11 Jul 2005 16:38 GMT

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"

Signature

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

>
> Hello.
[quoted text clipped - 28 lines]
>
> marc

Reply to this Message

Marc - 12 Jul 2005 14:05 GMT

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

Reply to this Message

Marc - 12 Jul 2005 14:11 GMT

Hilary.

Your first query still returns the same, the second returns nothing -
not even column names!

ta

marc

Reply to this Message

Marc - 19 Jul 2005 15:09 GMT

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

Reply to this Message

Daniel Crichton - 19 Jul 2005 16:41 GMT

>I am still not further forward with this, i have reduced my query to:
>
[quoted text clipped - 8 lines]
> (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

Reply to this Message

Marc - 21 Jul 2005 09:17 GMT

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

Reply to this Message