Full text search microsoft index server on .NET using C#

Hi,
I used the following code for an index search on specific folder.
{
//create a connection object and command object, to connect the Index
Server
System.Data.OleDb.OleDbConnection odbSearch = new
System.Data.OleDb.OleDbConnection( "Provider=\"MSIDXS\";Data
Source=\"SearchFolder\";");
System.Data.OleDb.OleDbCommand cmdSearch = new
System.Data.OleDb.OleDbCommand();
//assign connection to command object cmdSearch
cmdSearch.Connection = odbSearch;
//Query to search a free text string in the catalog in the contents of
the indexed documents in the catalog
string searchText = txtSearch.Text.Replace("","");
cmdSearch.CommandText = "select doctitle, filename, vpath, rank,
characterization from scope() where FREETEXT(Contents, "+ searchText
+") order by rank desc ";
odbSearch.Open();
I m able to get the TEXT search results. But full text search results
are reqiuired... like suppose if i search for experts release then i
need to get those documents where both the words exists..not only one
word.
Please let me know how to do this on .NET using C#.
Awaiting for the response.
Regards,
Rojasri.
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/Full-Text-se...ict228634.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=790451
use a contains based search for this.
ie
//Query to search a free text string in the catalog in the contents of
the indexed documents in the catalog
string searchText = txtSearch.Text.Replace("'","''");
cmdSearch.CommandText = "select doctitle, filename, vpath, rank,
characterization from scope() where CONTAINS(Contents, '"+ searchText
+"') order by rank desc ";
BTW - this is an indexing services question. It should be posted in
Microsoft.public.inetserver.indexserver
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
"rojasree" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_790451_42f97e43956785fa721f58da989a05d8@.dbf orumz.com...
> Hi,
> I used the following code for an index search on specific folder.
> {
> //create a connection object and command object, to connect the Index
> Server
> System.Data.OleDb.OleDbConnection odbSearch = new
> System.Data.OleDb.OleDbConnection( "Provider=\"MSIDXS\";Data
> Source=\"SearchFolder\";");
> System.Data.OleDb.OleDbCommand cmdSearch = new
> System.Data.OleDb.OleDbCommand();
> //assign connection to command object cmdSearch
> cmdSearch.Connection = odbSearch;
> //Query to search a free text string in the catalog in the contents of
> the indexed documents in the catalog
> string searchText = txtSearch.Text.Replace("'","''");
> cmdSearch.CommandText = "select doctitle, filename, vpath, rank,
> characterization from scope() where FREETEXT(Contents, '"+ searchText
> +"') order by rank desc ";
> odbSearch.Open();
>
> I m able to get the TEXT search results. But full text search results
> are reqiuired... like suppose if i search for 'experts release' then i
> need to get those documents where both the words exists..not only one
> word.
> Please let me know how to do this on .NET using C#.
> Awaiting for the response.
> Regards,
> Rojasri.
> --
> Posted using the http://www.dbforumz.com interface, at author's request
> Articles individually checked for conformance to usenet standards
> Topic URL:
http://www.dbforumz.com/Full-Text-se...ict228634.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=790451
sql

Full text Search doesnt find a specific string

Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.Hi all,
We have a table that is full text enabled and it is working fine, but the full text search doesn't returns any record for the following case

select * from let_catalog_search where contains(search_field,'"Bulk Process 1*"')
even though there exist records that satisfy the condition in the table,
the record that i am talking abt is "bulk process 1 with price bp100-ilt1-00200136 bp100-ilt1"

If I remove the last 1 from the search string i get lot of records, Can anybody help me out.

Im guessing here, but I think it is looking for records having one or more "words" that start with 1 (e.g. 1hs, 1tv, 1rquut). I'm not certain that the process would find just "1").

That's my story and I'm sticking with it.

Regards,

hmscott|||Thanks for your suggestion. Could you do me one more favor and modify the SQL query that I had put up in the previuos post to return the record that I am talking about.sql

FULL TEXT SEARCH

Microsoft Says:
This example uses a variable instead of a specific search term.
USE pubs
GO
DECLARE @.SearchWord varchar(30)
SET @.SearchWord ='Moon'
SELECT pr_info FROM pub_info WHERE FREETEXT(pr_info, @.SearchWord)
at http://msdn.microsoft.com/library/de...fa-fz_2juc.asp
but what I want is to assign a value to the variable @.SearchWord from a query like
SET @.SearchWord = (SELECT top 1 Fields1 from TableName)
which seems to be not working and given a error.
Server: Msg 7631, Level 15, State 1, Line 15
Syntax error occurred near '''. Expected '_STRING' in search condition 'Some Value
'.
where 'Some value' will be the value selected by the query 'SELECT top 1 Fields1 from TableName', so can any one help me out that how to use a variable which has been asigned a value from a query not explicitly assigned like SET @.SearchWord ='Moon' ?, I
have tried every combination of wraping the variable with single and double qoutes but its not working
many thanks
Ahsan,
Since this is the fulltext newsgroup, I thought I'd provide an example of a successful variable assignment for your question:
declare @.keyword varchar(100), @.count int
set @.keyword = 'MyActivityList'
set @.count = (select count(*) from jtkane_ASP_Procs where contains(*,@.keyword))
print @.count -- returns: 1
A non-FTS solution using TOP 1 <valid_column_name> example:
declare @.SearchWord varchar(100)
SET @.SearchWord = (select top 1 ASPFile from jtkane_ASP_Procs)
print @.SearchWord
-- returns: MyActivityList.cs
In your example below your variable @.SearchWord should be declared as the same datatype and lenght as the column named: Fields1 from your table named: TableName.
Hope this helps.
Regards,
John
"Ahsan" wrote:

> Microsoft Says:
> This example uses a variable instead of a specific search term.
> USE pubs
> GO
> DECLARE @.SearchWord varchar(30)
> SET @.SearchWord ='Moon'
> SELECT pr_info FROM pub_info WHERE FREETEXT(pr_info, @.SearchWord)
>
> at http://msdn.microsoft.com/library/de...fa-fz_2juc.asp
> but what I want is to assign a value to the variable @.SearchWord from a query like
> SET @.SearchWord = (SELECT top 1 Fields1 from TableName)
> which seems to be not working and given a error.
> Server: Msg 7631, Level 15, State 1, Line 15
> Syntax error occurred near '''. Expected '_STRING' in search condition 'Some Value
> '.
> where 'Some value' will be the value selected by the query 'SELECT top 1 Fields1 from TableName', so can any one help me out that how to use a variable which has been asigned a value from a query not explicitly assigned like SET @.SearchWord ='Moon' ?,
I have tried every combination of wraping the variable with single and double qoutes but its not working
> many thanks
>
>
|||John,
data type and length are same but I am still getting this error, I am using Sql server 2000 standard edition on windows 2000
ERROR IS:
Server: Msg 7631, Level 15, State 1, Line 19
Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson
'.
SELECT * From temp
QUERY:
SET QUOTED_IDENTIFIER OFF
declare @.vTemp as varchar(50)
SET @.vTemp = (SELECT ltrim(rtrim(FULLNAME)) FROM TEMP where incontact=46)
--Set @.vTemp = '"' + @.vTemp + '"'
--print @.vTemp
SELECT distinct * From temp
where
freetext(fullname,@.vTemp)
and freetext(address,'14 Marchmont Rd') AND contains(zip,'"SM6 9NU"')
TABLE SCRIPT:
CREATE TABLE [dbo].[Temp] (
[ID] [int] NOT NULL ,
[Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[FullName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[InContact] [numeric](18, 0) NULL ,
[Gender] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RecentID] [int] NULL
) ON [PRIMARY]
GO
SAMPLE DATA:
(this is exported from sql server export facility)
1,14 Marchmont Rd
,SM6 9NU
,Kevin Durban- Jackson
,46,Male
,
2,14 Marchmont Road
,SM6 9NU
,Kevin Durban - Jackson
,42,Male
,
3,14 Marchmont Road
,SM6 9NU
,Kevin Durban- Jackson
,1,Male ,
4,14 Marchmont Rd
,SM6 9NU,Kevin Durban-jackson
,3,Male ,
5,14 Maplin Park
,SL3 8XY
,Jo Rowden
,1,Female
,
6,14 Maplin Park
,SL3 8XY
,Nige Bacon
,1,Male ,
7,14 Maplin Drive
,SK2 5XJ
,Viv Nash
,2,Male ,
8,14 Maple Wood
,NN10 0UN
,Kevin Durban-jackson
,1,Female ,
Could you give it a try by creating the sample table in your sql server john?
many thanks in advance
"John Kane" wrote:
[vbcol=seagreen]
> Ahsan,
> Since this is the fulltext newsgroup, I thought I'd provide an example of a successful variable assignment for your question:
> declare @.keyword varchar(100), @.count int
> set @.keyword = 'MyActivityList'
> set @.count = (select count(*) from jtkane_ASP_Procs where contains(*,@.keyword))
> print @.count -- returns: 1
> A non-FTS solution using TOP 1 <valid_column_name> example:
> declare @.SearchWord varchar(100)
> SET @.SearchWord = (select top 1 ASPFile from jtkane_ASP_Procs)
> print @.SearchWord
> -- returns: MyActivityList.cs
> In your example below your variable @.SearchWord should be declared as the same datatype and lenght as the column named: Fields1 from your table named: TableName.
> Hope this helps.
> Regards,
> John
>
> "Ahsan" wrote:
?, I have tried every combination of wraping the variable with single and double qoutes but its not working[vbcol=seagreen]
|||Ah!!!
Interesting thing I have found
Maximum length I have found in the data for fullname colum is 24
Max(len(fullname))
returns: 26
when I changed the length of the variable @.vtemp to 23, it works (amazed why so), well I have posted the whole code with sample table script and data.
just change the @.vtemp length
NOT WORKING: declare @.vTemp as varchar(50)
WORKING: declare @.vTemp as varchar(23)
error was:
Server: Msg 7631, Level 15, State 1, Line 22
Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson
'.
now the lenght of this 'Kevin Durban- Jackson
' is 26
I am wondering whats goin on, you ask me to check the data type and length and i checked it, it was right, but still I am wondering whats going on!!!, is there a limitation on the length of the variable one can use in freetext or contain ?
"Ahsan" wrote:
[vbcol=seagreen]
> John,
> data type and length are same but I am still getting this error, I am using Sql server 2000 standard edition on windows 2000
> ERROR IS:
> Server: Msg 7631, Level 15, State 1, Line 19
> Syntax error occurred near '''. Expected '_STRING' in search condition 'Kevin Durban- Jackson
> '.
> SELECT * From temp
> QUERY:
> SET QUOTED_IDENTIFIER OFF
> declare @.vTemp as varchar(50)
> SET @.vTemp = (SELECT ltrim(rtrim(FULLNAME)) FROM TEMP where incontact=46)
> --Set @.vTemp = '"' + @.vTemp + '"'
> --print @.vTemp
> SELECT distinct * From temp
> where
> freetext(fullname,@.vTemp)
> and freetext(address,'14 Marchmont Rd') AND contains(zip,'"SM6 9NU"')
> TABLE SCRIPT:
> CREATE TABLE [dbo].[Temp] (
> [ID] [int] NOT NULL ,
> [Address] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ZIP] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [FullName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [InContact] [numeric](18, 0) NULL ,
> [Gender] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [RecentID] [int] NULL
> ) ON [PRIMARY]
> GO
>
> SAMPLE DATA:
> (this is exported from sql server export facility)
> 1,14 Marchmont Rd
> ,SM6 9NU
> ,Kevin Durban- Jackson
> ,46,Male
> ,
> 2,14 Marchmont Road
> ,SM6 9NU
> ,Kevin Durban - Jackson
> ,42,Male
> ,
> 3,14 Marchmont Road
> ,SM6 9NU
> ,Kevin Durban- Jackson
> ,1,Male ,
> 4,14 Marchmont Rd
> ,SM6 9NU,Kevin Durban-jackson
> ,3,Male ,
> 5,14 Maplin Park
> ,SL3 8XY
> ,Jo Rowden
> ,1,Female
> ,
> 6,14 Maplin Park
> ,SL3 8XY
> ,Nige Bacon
> ,1,Male ,
> 7,14 Maplin Drive
> ,SK2 5XJ
> ,Viv Nash
> ,2,Male ,
> 8,14 Maple Wood
> ,NN10 0UN
> ,Kevin Durban-jackson
> ,1,Female ,
> Could you give it a try by creating the sample table in your sql server john?
> many thanks in advance
> "John Kane" wrote:
?, I have tried every combination of wraping the variable with single and double qoutes but its not working[vbcol=seagreen]

full text body search not finding all results

I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
Archiver. When I run queries against the database for a specific word i.e
"testing", only 2 results show up when there should be more results.
I know the data is there because I can see it, it's just that when I do a
search it doesn't find it. I have full text-catalog enabled on the database.
I've tried deleting and re-creating the catalog but still the same thing.
The question is are they really there or are they being indexed.
Use a like to verify that it is really there. If it is then it is a problem
with it being indexed. Review the gatherer logs to see if it indexes the
rows correctly.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>I am running SQL 2005 SP1 and I have a database I'm using for GFI Mail
> Archiver. When I run queries against the database for a specific word i.e
> "testing", only 2 results show up when there should be more results.
> I know the data is there because I can see it, it's just that when I do a
> search it doesn't find it. I have full text-catalog enabled on the
> database.
>
> I've tried deleting and re-creating the catalog but still the same thing.
|||That's what my problem is, it's not being indexed correctly.
How do I fix that?
"Hilary Cotter" wrote:

> The question is are they really there or are they being indexed.
> Use a like to verify that it is really there. If it is then it is a problem
> with it being indexed. Review the gatherer logs to see if it indexes the
> rows correctly.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1813FDF8-E095-4784-B9B8-707C8309B1C9@.microsoft.com...
>
>
|||Can you see if there are any errors in the gatherer logs? If not can you
send some of the problem docs to me offline or post them here? Send me the
originals before they went into the database.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...[vbcol=seagreen]
> That's what my problem is, it's not being indexed correctly.
> How do I fix that?
> "Hilary Cotter" wrote:
|||I'm new to SQL so I'll need some help here.
1. How do I get gatherer logs?
2. When you say problem docs, what do you mean exactly? (these are test
emails).
"Hilary Cotter" wrote:

> Can you see if there are any errors in the gatherer logs? If not can you
> send some of the problem docs to me offline or post them here? Send me the
> originals before they went into the database.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:5FEDEB29-BE51-4733-8224-58C87AEAED5C@.microsoft.com...
>
>
|||You can find the gatherer logs on a SQL 2005 server in C:\Program
Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
Where X is your instance name.
The gatherer logs themselves will look like this:
SQLFT0001000015.LOG
test email should be indexable in a char or varchar column. Attachments may
not be depending on how you store them. Are you storing them in varbinary or
image columns? Do you have a document type column associated with the image
or varbinary columns?
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...[vbcol=seagreen]
> I'm new to SQL so I'll need some help here.
> 1. How do I get gatherer logs?
> 2. When you say problem docs, what do you mean exactly? (these are test
> emails).
> "Hilary Cotter" wrote:
|||Ok, I'll look at them. I assume I just open them with Notepad. What am I
looking for?
As far as your questions:
1. Not sure if they're being stored in verbinary or image columns. However
this GFI Mail Archive stores it.
2. Don't know if I have a document type column associated with the
verbinary or image columns.
"Hilary Cotter" wrote:

> You can find the gatherer logs on a SQL 2005 server in C:\Program
> Files\Microsoft SQL Server\MSSQL.X\MSSQL\LOG>
> Where X is your instance name.
> The gatherer logs themselves will look like this:
> SQLFT0001000015.LOG
> test email should be indexable in a char or varchar column. Attachments may
> not be depending on how you store them. Are you storing them in varbinary or
> image columns? Do you have a document type column associated with the image
> or varbinary columns?
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:1F6F0D54-F9FB-4CC3-A8D3-E0445883AE00@.microsoft.com...
>
>
|||Can you script out your table, indexes, and full text indexes and post them
here.
Key to solving your problem is discovering how the GFI archive stores it. If
it is text, or msg you should be fine. If it is something proprietary you
will be unable to index them.
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
"Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...[vbcol=seagreen]
> Ok, I'll look at them. I assume I just open them with Notepad. What am I
> looking for?
> As far as your questions:
> 1. Not sure if they're being stored in verbinary or image columns.
> However
> this GFI Mail Archive stores it.
> 2. Don't know if I have a document type column associated with the
> verbinary or image columns.
> "Hilary Cotter" wrote:
|||I went to the GFI web site and notice that their product does work with SQL
FTS. Their technical support group should be able to help you through this
problem.
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
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23lkK28IEHHA.3520@.TK2MSFTNGP04.phx.gbl...
> Can you script out your table, indexes, and full text indexes and post
> them here.
> Key to solving your problem is discovering how the GFI archive stores it.
> If it is text, or msg you should be fine. If it is something proprietary
> you will be unable to index them.
> --
> 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
>
> "Gilbert" <Gilbert@.discussions.microsoft.com> wrote in message
> news:BD072397-C5D4-4B19-A8E6-80C03AF1784E@.microsoft.com...
>

FTS Cluster Setup

I have an existing SQL 2000 Cluster setup and would like to add Full
Text search to it. Is there any specific doc or best practices for
this configuration? I have started the install once but the only
option was to uninstall SQL Server. Do I run ftsetup.exe instead?
From what I recall, SQL FTS is part of a cluster install. There is no option
to deselect it.
Are you having a problem with it?
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
"AF" <af.at.work@.gmail.com> wrote in message
news:1170700421.768499.282270@.h3g2000cwc.googlegro ups.com...
>I have an existing SQL 2000 Cluster setup and would like to add Full
> Text search to it. Is there any specific doc or best practices for
> this configuration? I have started the install once but the only
> option was to uninstall SQL Server. Do I run ftsetup.exe instead?
>
|||On Feb 6, 11:51 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> From what I recall, SQL FTS is part of a cluster install. There is no option
> to deselect it.
> Are you having a problem with it?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "AF" <af.at.w...@.gmail.com> wrote in message
> news:1170700421.768499.282270@.h3g2000cwc.googlegro ups.com...
It wasn't showing up in EM - after restarting the service it did
appear. Thanks.