Showing posts with label full-text. Show all posts
Showing posts with label full-text. Show all posts

Sunday, February 26, 2012

Full-Text-Search

Hi,
I have a full-text search enabled table and an image data type column that stores the extracts from the documents stored in the files. I have created a full text catalog that indexes few columns in the table and image data type column as well and I assigned the extension column to this image data type column.
Everything works fine except that search on the image data type column called, in my case "body" does not return anything. The documents exist, there is an information in the "body" column.

My code goes like this:
select FT_TBL.resourceID, FT_TBL.lang, FT_TBL.keywords, FT_TBL.description, FT_TBL.extension, b.rank
from full_text_search as FT_TBL inner join containstable(full_text_search, *, 'Microsoft') as b
on b.[key] = FT_TBL.resourceID
order by b.rank desc

any help appreciated,
thanksI figured the solution to the previous post. I was trying to search on the .pdf files, but I do not have filter installed on the server to filter through these files, other files extension work fine.

Another problem:
I have two tables that are full-text search enabled. And I need to search one table first, and than the columns in the second one. This works fine if the search criteria is found in both tables, but if not the query returns nothing.

help much appreciated!
Thanks|||use union all.

FullTexted table

Hi,
I have a database that has about 100 tables. How can I find out which
tables are full-text indexed?
Thanks
Ed
Ed,
You can use the following system stored procedures to determine what tables
are FT Indexed:
sp_help_fulltext_tables
You can also use the following metadata function to gather more FT info:
select
ObjectProperty(object_id(N'<table_name>'),N'TableH asActiveFulltextIndex')
select
objectproperty(object_id(N'<table_name>'),N'TableF ulltextChangeTrackingOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextBackgroundUpdateInd
exOn')
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Regards,
John
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:8E4A8EDC-C5E3-4C75-A200-5412B0818CE3@.microsoft.com...
> Hi,
> I have a database that has about 100 tables. How can I find out which
> tables are full-text indexed?
> Thanks
> Ed

Full-text working sporadically

I have full-text running on one database only. This index seems to have
issues about once every couple of weeks. Normally, a search can be done (via
webpage) on a keyword - and it works fine. But there are times that no
keyword (or other options) searches will work. The only option that is
searchable is "records updated in the last x number of days". So it's like
the full-text searching 1/2 works.
I have ran the stored procedure to ensure FTS was installed properly.
The MSSearch server is running under the local system account (as it should
be per MS).
I have deleted and rebuilt it complete. I have tried to rebuild the index
via the wizard and by right-clicking on the Full-Text Catalog -- neither
fixed the issue.
I've ran the stored procedure to ensure it was enabled.
Anyone have any idea why my FTS will work completely one day and work "a
little" the next?
Thanks!
Key
Can you check the gatherer logs to see if they reveal anything?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"KEY" <key.hammonds@.gmail.com> wrote in message
news:F6E6798B-FA95-4C20-BBE7-104CE05B4B80@.microsoft.com...
>I have full-text running on one database only. This index seems to have
> issues about once every couple of weeks. Normally, a search can be done
> (via
> webpage) on a keyword - and it works fine. But there are times that no
> keyword (or other options) searches will work. The only option that is
> searchable is "records updated in the last x number of days". So it's
> like
> the full-text searching 1/2 works.
> I have ran the stored procedure to ensure FTS was installed properly.
> The MSSearch server is running under the local system account (as it
> should
> be per MS).
> I have deleted and rebuilt it complete. I have tried to rebuild the index
> via the wizard and by right-clicking on the Full-Text Catalog -- neither
> fixed the issue.
> I've ran the stored procedure to ensure it was enabled.
> Anyone have any idea why my FTS will work completely one day and work "a
> little" the next?
> Thanks!
> Key

Full-text usage?

Is it appropriate to use a full-text index and query to search CustomerName and CustomerDBA type fields? I've been told that this type of searching is meant for searching documents, but it seems that Full-text would be useful for fuzzy searching and noise word removal also. Does it make sense to index a varchar field? Does it add performace to searching over native 'Like' queries?

Thanks.

Full-text searching is great when you have multiple words in a column and you'd like to search on those individual words, or if you need to search on different tenses/forms of a word, eliminate noise words, or thesaurus capabilities. So yes, there are benefits to using it on varchar columns. As of SQL Server 2005 your full-text catalogs/indexes are included in your backups and restores so you don't have to rebuild your full-text indexes if you restore the database.

If you are searching on the full value or first few letters of the varchar, then a 'regular' index on the column would likely provide faster performance .. and in SQL Server 2005 we even improved the string searching for LIKEs that start with a wildcard.

Don

Full-text SharePoint

Hi all,
I'm looking for in-depth information on the search functionality of
SharePoint 2003. As it is using the SQL full-text search, I post my question
to this newsgroup.
The main goal is to figure out how I can add the functionality of rating
documents to the SharePoint search, in order to make high-rated documents
show up higher in the search results.
Kind regards,
Jeroendb
Jeroen,
I think what you're looking for in SharePoint 2003 is called "best bets",
see "Planning Your Information Structure Using Microsoft Office SharePoint
Portal Server 2003" at:
http://www.microsoft.com/technet/pro...c0861881x.mspx and
under "Planning Keywords and Keyword Best Bets".
While you can do much the same thing using SQL Server, it's can be a bit
complex to get a scalable solution. How would you determine what is a
"highly-rated document" based upon a keyword or phrase search entered by the
searcher?
Regards,
John
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:OHe$OhCZEHA.2480@.tk2msftngp13.phx.gbl...
> Hi all,
> I'm looking for in-depth information on the search functionality of
> SharePoint 2003. As it is using the SQL full-text search, I post my
question
> to this newsgroup.
> The main goal is to figure out how I can add the functionality of rating
> documents to the SharePoint search, in order to make high-rated documents
> show up higher in the search results.
> Kind regards,
> Jeroendb
>
|||Thanks John, for your quick reply.
By rating I mean that I would like to let the end-user rate the documents he
finds. I.e. documents with an average rating of > 50% should show up higher
in the search hierarchy. Documents that are never rated or searched on
should be discarded after a certain period of time.
This probably is too much for SharePoint ?
Kind regards,
Jeroendb
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OCB1l7JZEHA.3112@.tk2msftngp13.phx.gbl...
> Jeroen,
> I think what you're looking for in SharePoint 2003 is called "best bets",
> see "Planning Your Information Structure Using Microsoft Office SharePoint
> Portal Server 2003" at:
> http://www.microsoft.com/technet/pro...c0861881x.mspx
and
> under "Planning Keywords and Keyword Best Bets".
> While you can do much the same thing using SQL Server, it's can be a bit
> complex to get a scalable solution. How would you determine what is a
> "highly-rated document" based upon a keyword or phrase search entered by
the[vbcol=seagreen]
> searcher?
> Regards,
> John
>
> "Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
> news:OHe$OhCZEHA.2480@.tk2msftngp13.phx.gbl...
> question
documents
>
|||You're welcome, Jeroen,
Yes, this can be done using SQL FTS and adding a new column to your
FT-enabled table called "ExtendedRank" and then adding an value to the RANK
value in the CONTAINSTABLE or FREETEXTTABLE query and then ordering the
results by the new column "Extended Rank". Below is an example of the query
and table structure:
CREATE TABLE FTSTable (
KeyCol int IDENTITY (1,1) NOT NULL
CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
TextCol text NULL,
ImageCol image NULL,
ExtCol sysname NULL,
Keyword varchar(500),
KeywordWeight int,
TimeStampCol timestamp NULL
) ON [PRIMARY]
go
-- Note, the column KeywordWeight may need to be re-define as FLOAT
depending upon the type of calculations you are planning on doing.
-- Insert data...
INSERT FTSTable values('This is random data for row 1', 0xFFFFFFFF, 'zip',
'random,data,row', 1, NULL)
INSERT FTSTable values('The brown fox jumped over the fence. row 2',
0xFFFFFFFF, 'xls', 'brown,fox,fence', 10, NULL)
INSERT FTSTable values('Mary had a little lamb and its fleece was white',
0xFFFFFFFF, 'ppt', 'random,data,row', 0, NULL)
INSERT FTSTable values('Moby Dick is considered on of the greatest books
available.', 0xFFFFFFFF, 'txt', 'moby,dick,book', 50, NULL)
INSERT FTSTable values('This has more to do this short text that with rows',
0xFFFFFFFF, 'htm', 'short,text,row', 100, NULL)
INSERT FTSTable values('Taglines would be helpful here as I could get more
text added', 0xFFFFFFFF, 'pdf', 'tagline,helpful,text', 22, NULL)
INSERT FTSTable values('Winston Churchill was one of the greatest speakers
of all time', 0xFFFFFFFF, 'xml', 'winston,churchill,great,speakers', 1000,
NULL)
go
-- Note, that KeyCol rows 5 and 6 have very high values in the KeyWordWeight
column
-- Test FTS with containstable and freetexttable
SELECT FTS.KeyCol, FTS.TextCol, FTS.Keyword, FTS.KeywordWeight, CT.[RANK]
from FTSTable AS FTS
JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
ORDER by CT.[RANK] DESC
-- as expected two rows returned: KeyCol 6 and 5
-- caculated ExtendedRank = Rank + KeywordWeight
SELECT FTS.KeyCol, FTS.TextCol, CT.[RANK], FTS.KeywordWeight,
FTS.KeywordWeight + CT.[RANK] as ExtendedRank
from FTSTable AS FTS
JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
ORDER by ExtendedRank DESC -- CT.[RANK] DESC
/* -- Returns: as expected two rows returned: KeyCol 6 and 5 with HIGHEST
ExtendedRank first!!
KeyCol TextCol RANK
KeywordWeight ExtendedRank
-- ---- -- -
-- --
5 This has more to do this short text that with rows 48
100 148
6 Taglines would be helpful here as I could get more 48
22 70
(2 row(s) affected)
*/
Additionally, the above method is very scallable as you do not have to join
your FT-enabled table with another FT-enabled table in order to get the
"best bet" to the top of the resultset. This method could also be extended
and use a true "computed column" and support more complex calculations as
well. I don't know if this is "too much for SharePoint", but this is very
doable in SQL Server!
Regards,
John
"Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
news:#Ik6EwOZEHA.3988@.tk2msftngp13.phx.gbl...
> Thanks John, for your quick reply.
> By rating I mean that I would like to let the end-user rate the documents
he
> finds. I.e. documents with an average rating of > 50% should show up
higher[vbcol=seagreen]
> in the search hierarchy. Documents that are never rated or searched on
> should be discarded after a certain period of time.
> This probably is too much for SharePoint ?
> Kind regards,
> Jeroendb
> "John Kane" <jt-kane@.comcast.net> wrote in message
> news:OCB1l7JZEHA.3112@.tk2msftngp13.phx.gbl...
bets",[vbcol=seagreen]
SharePoint[vbcol=seagreen]
> and
> the
rating
> documents
>
|||Jeroen,
Did you find the below SQL FTS solution to provide the "functionality of
rating documents to the SharePoint search, in order to make high-rated
documents show up higher in the search results"? If so, how would you let
the end-user rate the documents he
finds? You programmatically set the column KeywordWeight to some value and
then use this a as multiplier (vs. just adding the value to the CT.[RANK]
value) and then ordering by the computed column: ExtendedRank.
If you're interested I've been able to extend this concept to use the
Keyword column to further extend it to a "keyword specific" solution
without joining to another table and without using cursor processing and
therefore is very scalable... Let me know if you would be interested in this
alternative approach to "best bet", i.e., ranking specific rows higher than
other rows in the CONTAINSTABLE or FREETEXTTABLE resultset.
Regards,
John
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OGnTQvPZEHA.3132@.TK2MSFTNGP10.phx.gbl...
> You're welcome, Jeroen,
> Yes, this can be done using SQL FTS and adding a new column to your
> FT-enabled table called "ExtendedRank" and then adding an value to the
RANK
> value in the CONTAINSTABLE or FREETEXTTABLE query and then ordering the
> results by the new column "Extended Rank". Below is an example of the
query
> and table structure:
> CREATE TABLE FTSTable (
> KeyCol int IDENTITY (1,1) NOT NULL
> CONSTRAINT FTSTable_IDX PRIMARY KEY CLUSTERED,
> TextCol text NULL,
> ImageCol image NULL,
> ExtCol sysname NULL,
> Keyword varchar(500),
> KeywordWeight int,
> TimeStampCol timestamp NULL
> ) ON [PRIMARY]
> go
> -- Note, the column KeywordWeight may need to be re-define as FLOAT
> depending upon the type of calculations you are planning on doing.
> -- Insert data...
> INSERT FTSTable values('This is random data for row 1', 0xFFFFFFFF, 'zip',
> 'random,data,row', 1, NULL)
> INSERT FTSTable values('The brown fox jumped over the fence. row 2',
> 0xFFFFFFFF, 'xls', 'brown,fox,fence', 10, NULL)
> INSERT FTSTable values('Mary had a little lamb and its fleece was white',
> 0xFFFFFFFF, 'ppt', 'random,data,row', 0, NULL)
> INSERT FTSTable values('Moby Dick is considered on of the greatest books
> available.', 0xFFFFFFFF, 'txt', 'moby,dick,book', 50, NULL)
> INSERT FTSTable values('This has more to do this short text that with
rows',
> 0xFFFFFFFF, 'htm', 'short,text,row', 100, NULL)
> INSERT FTSTable values('Taglines would be helpful here as I could get more
> text added', 0xFFFFFFFF, 'pdf', 'tagline,helpful,text', 22, NULL)
> INSERT FTSTable values('Winston Churchill was one of the greatest speakers
> of all time', 0xFFFFFFFF, 'xml', 'winston,churchill,great,speakers', 1000,
> NULL)
> go
> -- Note, that KeyCol rows 5 and 6 have very high values in the
KeyWordWeight
> column
> -- Test FTS with containstable and freetexttable
> SELECT FTS.KeyCol, FTS.TextCol, FTS.Keyword, FTS.KeywordWeight, CT.[RANK]
> from FTSTable AS FTS
> JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
> ORDER by CT.[RANK] DESC
> -- as expected two rows returned: KeyCol 6 and 5
> -- caculated ExtendedRank = Rank + KeywordWeight
> SELECT FTS.KeyCol, FTS.TextCol, CT.[RANK], FTS.KeywordWeight,
> FTS.KeywordWeight + CT.[RANK] as ExtendedRank
> from FTSTable AS FTS
> JOIN containstable(FTSTable,*,'text') as CT ON CT.[KEY] = FTS.KeyCol
> ORDER by ExtendedRank DESC -- CT.[RANK] DESC
> /* -- Returns: as expected two rows returned: KeyCol 6 and 5 with HIGHEST
> ExtendedRank first!!
> KeyCol TextCol RANK
> KeywordWeight ExtendedRank
> -- ---- --
-
> -- --
> 5 This has more to do this short text that with rows 48
> 100 148
> 6 Taglines would be helpful here as I could get more 48
> 22 70
> (2 row(s) affected)
> */
> Additionally, the above method is very scallable as you do not have to
join[vbcol=seagreen]
> your FT-enabled table with another FT-enabled table in order to get the
> "best bet" to the top of the resultset. This method could also be extended
> and use a true "computed column" and support more complex calculations as
> well. I don't know if this is "too much for SharePoint", but this is very
> doable in SQL Server!
> Regards,
> John
>
> "Jeroen De Brabander" <Jeroen.De.Brabander@.edan.be> wrote in message
> news:#Ik6EwOZEHA.3988@.tk2msftngp13.phx.gbl...
documents[vbcol=seagreen]
> he
> higher
> bets",
> SharePoint
http://www.microsoft.com/technet/pro...c0861881x.mspx[vbcol=seagreen]
bit[vbcol=seagreen]
by
> rating
>

Full-text setup error

Hi,
I'm trying to install full-text search service on SQL server using command
ftsetup.exe, but I always get error "FulltextSetup returned error code:
0x80000023".
I've upgraded MSDE with SQL Server 2000 standard . Upgrade process did not
install full-text search service, so I had to do it manually as it is
suggested in KB article 827449
(http://support.microsoft.com/default...b;en-us;827449). I've
successfuly installed Microsoft Search service (service is running), but
when I try to configure the instance of SQL Server to use the Microsoft
Search service with ftsetup command ("ftsetup.exe SQLServer 0 1 0 0 0"), it
fails. I have SQL server 2000 standard with SP3a, and MSSQLSERVER service is
running as local system account.
Can you please give me some advice how to make full-text working?
Thanx (and Happy New Year to all),
Bogdan
Bogdan,
The procedures KB article 827449 are used when it is necessary to "manually
RE-install" the FT Search components on a server where these components were
install installed, but later determined that some problem occurred during
that installation. In your case, the initial FT Search components have not
been installed due to your upgrade path from MSDE to SQL Server 2000
Standard Edition. I'd recommend that you install the Full-text Search
components via your SQL Server 2000 Setup CD and select "custom
installation" and under "server components" you will see "Full-text Search"
and you should checkmark this and then the SQL Server 2000 Setup process
will install these components for you.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Bogdan" <bogdan.skof@.doba.si> wrote in message
news:eTaTnon7EHA.1264@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I'm trying to install full-text search service on SQL server using command
> ftsetup.exe, but I always get error "FulltextSetup returned error code:
> 0x80000023".
> I've upgraded MSDE with SQL Server 2000 standard . Upgrade process did not
> install full-text search service, so I had to do it manually as it is
> suggested in KB article 827449
> (http://support.microsoft.com/default...b;en-us;827449). I've
> successfuly installed Microsoft Search service (service is running), but
> when I try to configure the instance of SQL Server to use the Microsoft
> Search service with ftsetup command ("ftsetup.exe SQLServer 0 1 0 0 0"),
it
> fails. I have SQL server 2000 standard with SP3a, and MSSQLSERVER service
is
> running as local system account.
> Can you please give me some advice how to make full-text working?
> Thanx (and Happy New Year to all),
> Bogdan
>

Full-Text Setup and Search

Hi,

I have a database developed using SQL Server Management Studio Express with Full-Text searching. I can go into the Object Explorer and right click on my database and choose Properties, select File and the 'Use-full-ext indexing' is checked.

I'm not sure how I create an index for a table, can someone help? I want to create an index for table 'tblLogNotes' and have the following columns:

note_id = (PK, int, not null)
note = (varchar(3000), not null)
log_id = (FK, int, not null)
operatorid = (int, not null)
note_created = (datetime, not null)

I know I did something but not sure what I did, but on one of my tables, when I right click on table and choose 'log_note', I can see that Full-text Specification = yes, (is full-text indexed = yes), full-text type column = <none> and language = English (United States).

Table 'tblLog' has the following columns:
log_id - (PK, int - not null)
log_note (varchar(3000), not null)
tag_number(varchar(50), not null)

However, when I try the following query, I get nothing:

SELECT *
FROM tblLog
WHERE CONTAINS(Log_note, '"pump*"')

I've also tried: 'pump'

What am I doing wrong?

Thanks,

Hi Ed,

You need to create a Full Text Catalog and a Full Text Index before you can query against them. See...

CREATE FULLTEXT CATALOG

CREATE FULLTEXT INDEX

and

The Full Text Overview topic in Books Online.

SSMSE does not enable the Full Text commands for technical reasons, but you can use the Template Explorer to get the basic SQL statements and then just fill in the blanks for your database.

Mike

Full-text searching

Are there any plans to add full-text searching capability to SQLCE? I know it's available in SQL Server Express but it would still be useful for an embedded/desktop application -- SQLite recently added this.We have this feature on our radar, but time lines for this have not been fixed yet

Full-text searching

I created a catalog by:
Use Pubs
EXEC sp_fulltext_catalog 'Cat_Test', 'create'
EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
EXEC sp_fulltext_column 'publishers','pub_name','add'
EXEC sp_fulltext_column 'publishers','city','add'
EXEC sp_fulltext_column 'publishers','state','add'
EXEC sp_fulltext_table 'publishers','activate'
EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
EXEC sp_fulltext_table 'publishers','start_change_tracking'
EXEC sp_fulltext_table 'publishers','start_background_updateind
ex'
If I use the SQL
select city
from publishers
where contains (city, '"Paris"')
It return no record.
However, if I use
select *
from publishers
where city = 'Paris'
returned one record.The code you sent is working perfect for me.
Make sure that the Windows account used by the full-text service has
permissions on the SQL Server instance. I have seen that issue a few times
before.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:

> I created a catalog by:
> Use Pubs
> EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
> EXEC sp_fulltext_column 'publishers','pub_name','add'
> EXEC sp_fulltext_column 'publishers','city','add'
> EXEC sp_fulltext_column 'publishers','state','add'
> EXEC sp_fulltext_table 'publishers','activate'
> EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> EXEC sp_fulltext_table 'publishers','start_change_tracking'
> EXEC sp_fulltext_table 'publishers','start_background_updateind
ex'
> If I use the SQL
> select city
> from publishers
> where contains (city, '"Paris"')
> It return no record.
> However, if I use
> select *
> from publishers
> where city = 'Paris'
> returned one record.
>
>|||Are you saying you can use
select city
from publishers
where contains (city, '"Paris"')
returns a record ?
Can you elaborate what do you mean by
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance.
I am using Developer Edition.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...[vbcol=seagreen]
> The code you sent is working perfect for me.
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance. I have seen that issue a few times
> before.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
>
'upkcl_pubind'[vbcol=seagreen]|||You better try this
select city
from publishers
where contains (city, 'Paris')
Go to Services on your computer and see which acount your full-text service
is using. The service name could be something like Microsoft Search or SQL
Server FullText Search. Then verify or give permissions to this account on
SQL Server.
I have seen before this service running as Local System. So, in this case NT
AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:

> Are you saying you can use
> select city
> from publishers
> where contains (city, '"Paris"')
> returns a record ?
> Can you elaborate what do you mean by
> I am using Developer Edition.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> 'upkcl_pubind'
>
>|||Hi Ben,
Yes, the service is running as Local System.
Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please ?
Thanks
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT[vbcol=seagreen]
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
>
times[vbcol=seagreen]|||I checked in EM that the BUILTIN\Administrator has all permissions on all
databases.
And the database roles are public and db_owner.
I tried to add more permission on the role for the databases but got an
error:
"Error 15404: Cannot use the reserved user or role name 'dbo'"
So that I cannot add more permission to the BUILTIN\Administrator.
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uHGuzK7dGHA.3948@.TK2MSFTNGP03.phx.gbl...
> Hi Ben,
> Yes, the service is running as Local System.
> Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please
?
> Thanks
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> service
SQL[vbcol=seagreen]
on[vbcol=seagreen]
case[vbcol=seagreen]
> NT
> times
>|||Hi Ben,
I am still in the trouble of not getting the full-text search working.
Could you direct me how do I , as you said, to give permission to my login
account on the SQL Server ?
My situation is:
1) Windows XP
2) Windows login is 'Alan', password 'Alan'
3) SQL Server 2000 Developer edition use Windows authentication
4) Run EM
5) Open QA inside EM
6) Run the T-SQL to create catalog and include tables from pubs database
7) Checked the catalog status is idle in EM and also got no result from the
query using CONTAINS in QA.
8) I had populate the catalog but the status is still idle
I am really worry about because I really need to get that done very soon but
I get stuck here.
Thank you for your time
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT[vbcol=seagreen]
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
>
times[vbcol=seagreen]

Full-text searching

I created a catalog by:
Use Pubs
EXEC sp_fulltext_catalog 'Cat_Test', 'create'
EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
EXEC sp_fulltext_column 'publishers','pub_name','add'
EXEC sp_fulltext_column 'publishers','city','add'
EXEC sp_fulltext_column 'publishers','state','add'
EXEC sp_fulltext_table 'publishers','activate'
EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
EXEC sp_fulltext_table 'publishers','start_change_tracking'
EXEC sp_fulltext_table 'publishers','start_background_updateindex'
If I use the SQL
select city
from publishers
where contains (city, '"Paris"')
It return no record.
However, if I use
select *
from publishers
where city = 'Paris'
returned one record.The code you sent is working perfect for me.
Make sure that the Windows account used by the full-text service has
permissions on the SQL Server instance. I have seen that issue a few times
before.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> I created a catalog by:
> Use Pubs
> EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test', 'upkcl_pubind'
> EXEC sp_fulltext_column 'publishers','pub_name','add'
> EXEC sp_fulltext_column 'publishers','city','add'
> EXEC sp_fulltext_column 'publishers','state','add'
> EXEC sp_fulltext_table 'publishers','activate'
> EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> EXEC sp_fulltext_table 'publishers','start_change_tracking'
> EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> If I use the SQL
> select city
> from publishers
> where contains (city, '"Paris"')
> It return no record.
> However, if I use
> select *
> from publishers
> where city = 'Paris'
> returned one record.
>
>|||Are you saying you can use
select city
from publishers
where contains (city, '"Paris"')
returns a record ?
Can you elaborate what do you mean by
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance.
I am using Developer Edition.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> The code you sent is working perfect for me.
> Make sure that the Windows account used by the full-text service has
> permissions on the SQL Server instance. I have seen that issue a few times
> before.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > I created a catalog by:
> >
> > Use Pubs
> >
> > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> >
> > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
'upkcl_pubind'
> >
> > EXEC sp_fulltext_column 'publishers','pub_name','add'
> >
> > EXEC sp_fulltext_column 'publishers','city','add'
> >
> > EXEC sp_fulltext_column 'publishers','state','add'
> >
> > EXEC sp_fulltext_table 'publishers','activate'
> >
> > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> >
> > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> >
> > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> >
> > If I use the SQL
> >
> > select city
> > from publishers
> > where contains (city, '"Paris"')
> >
> > It return no record.
> >
> > However, if I use
> >
> > select *
> > from publishers
> > where city = 'Paris'
> >
> > returned one record.
> >
> >
> >
> >|||You better try this
select city
from publishers
where contains (city, 'Paris')
Go to Services on your computer and see which acount your full-text service
is using. The service name could be something like Microsoft Search or SQL
Server FullText Search. Then verify or give permissions to this account on
SQL Server.
I have seen before this service running as Local System. So, in this case NT
AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
Ben Nevarez, MCDBA, OCP
Database Administrator
"Alan" wrote:
> Are you saying you can use
> select city
> from publishers
> where contains (city, '"Paris"')
> returns a record ?
> Can you elaborate what do you mean by
> > Make sure that the Windows account used by the full-text service has
> > permissions on the SQL Server instance.
> I am using Developer Edition.
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> >
> > The code you sent is working perfect for me.
> >
> > Make sure that the Windows account used by the full-text service has
> > permissions on the SQL Server instance. I have seen that issue a few times
> > before.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > I created a catalog by:
> > >
> > > Use Pubs
> > >
> > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > >
> > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> 'upkcl_pubind'
> > >
> > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > >
> > > EXEC sp_fulltext_column 'publishers','city','add'
> > >
> > > EXEC sp_fulltext_column 'publishers','state','add'
> > >
> > > EXEC sp_fulltext_table 'publishers','activate'
> > >
> > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > >
> > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > >
> > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > >
> > > If I use the SQL
> > >
> > > select city
> > > from publishers
> > > where contains (city, '"Paris"')
> > >
> > > It return no record.
> > >
> > > However, if I use
> > >
> > > select *
> > > from publishers
> > > where city = 'Paris'
> > >
> > > returned one record.
> > >
> > >
> > >
> > >
>
>|||Hi Ben,
Yes, the service is running as Local System.
Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please ?
Thanks
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Are you saying you can use
> > select city
> > from publishers
> > where contains (city, '"Paris"')
> >
> > returns a record ?
> >
> > Can you elaborate what do you mean by
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance.
> >
> > I am using Developer Edition.
> >
> >
> > "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> > news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> > >
> > > The code you sent is working perfect for me.
> > >
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance. I have seen that issue a few
times
> > > before.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > I created a catalog by:
> > > >
> > > > Use Pubs
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > > >
> > > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> > 'upkcl_pubind'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','city','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','state','add'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','activate'
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > > >
> > > > If I use the SQL
> > > >
> > > > select city
> > > > from publishers
> > > > where contains (city, '"Paris"')
> > > >
> > > > It return no record.
> > > >
> > > > However, if I use
> > > >
> > > > select *
> > > > from publishers
> > > > where city = 'Paris'
> > > >
> > > > returned one record.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||I checked in EM that the BUILTIN\Administrator has all permissions on all
databases.
And the database roles are public and db_owner.
I tried to add more permission on the role for the databases but got an
error:
"Error 15404: Cannot use the reserved user or role name 'dbo'"
So that I cannot add more permission to the BUILTIN\Administrator.
"Alan" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uHGuzK7dGHA.3948@.TK2MSFTNGP03.phx.gbl...
> Hi Ben,
> Yes, the service is running as Local System.
> Can you tell me how do I give permission to the NTAUTHORITY/SYSTEM please
?
> Thanks
>
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> >
> > You better try this
> > select city
> > from publishers
> > where contains (city, 'Paris')
> >
> > Go to Services on your computer and see which acount your full-text
> service
> > is using. The service name could be something like Microsoft Search or
SQL
> > Server FullText Search. Then verify or give permissions to this account
on
> > SQL Server.
> >
> > I have seen before this service running as Local System. So, in this
case
> NT
> > AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> >
> > Ben Nevarez, MCDBA, OCP
> > Database Administrator
> >
> >
> > "Alan" wrote:
> >
> > > Are you saying you can use
> > > select city
> > > from publishers
> > > where contains (city, '"Paris"')
> > >
> > > returns a record ?
> > >
> > > Can you elaborate what do you mean by
> > > > Make sure that the Windows account used by the full-text service has
> > > > permissions on the SQL Server instance.
> > >
> > > I am using Developer Edition.
> > >
> > >
> > > "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> > > news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> > > >
> > > > The code you sent is working perfect for me.
> > > >
> > > > Make sure that the Windows account used by the full-text service has
> > > > permissions on the SQL Server instance. I have seen that issue a few
> times
> > > > before.
> > > >
> > > > Ben Nevarez, MCDBA, OCP
> > > > Database Administrator
> > > >
> > > >
> > > > "Alan" wrote:
> > > >
> > > > > I created a catalog by:
> > > > >
> > > > > Use Pubs
> > > > >
> > > > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> > > 'upkcl_pubind'
> > > > >
> > > > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > > > >
> > > > > EXEC sp_fulltext_column 'publishers','city','add'
> > > > >
> > > > > EXEC sp_fulltext_column 'publishers','state','add'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers','activate'
> > > > >
> > > > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > > > >
> > > > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > > > >
> > > > > If I use the SQL
> > > > >
> > > > > select city
> > > > > from publishers
> > > > > where contains (city, '"Paris"')
> > > > >
> > > > > It return no record.
> > > > >
> > > > > However, if I use
> > > > >
> > > > > select *
> > > > > from publishers
> > > > > where city = 'Paris'
> > > > >
> > > > > returned one record.
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>|||Hi Ben,
I am still in the trouble of not getting the full-text search working.
Could you direct me how do I , as you said, to give permission to my login
account on the SQL Server ?
My situation is:
1) Windows XP
2) Windows login is 'Alan', password 'Alan'
3) SQL Server 2000 Developer edition use Windows authentication
4) Run EM
5) Open QA inside EM
6) Run the T-SQL to create catalog and include tables from pubs database
7) Checked the catalog status is idle in EM and also got no result from the
query using CONTAINS in QA.
8) I had populate the catalog but the status is still idle
I am really worry about because I really need to get that done very soon but
I get stuck here.
Thank you for your time
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:8868199F-1F62-4528-A783-D54A28875697@.microsoft.com...
> You better try this
> select city
> from publishers
> where contains (city, 'Paris')
> Go to Services on your computer and see which acount your full-text
service
> is using. The service name could be something like Microsoft Search or SQL
> Server FullText Search. Then verify or give permissions to this account on
> SQL Server.
> I have seen before this service running as Local System. So, in this case
NT
> AUTHORITY\SYSTEM must be given permissions on the SQL Server instance.
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Alan" wrote:
> > Are you saying you can use
> > select city
> > from publishers
> > where contains (city, '"Paris"')
> >
> > returns a record ?
> >
> > Can you elaborate what do you mean by
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance.
> >
> > I am using Developer Edition.
> >
> >
> > "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> > news:797F3FC7-2D47-4340-9683-CA11235E74E4@.microsoft.com...
> > >
> > > The code you sent is working perfect for me.
> > >
> > > Make sure that the Windows account used by the full-text service has
> > > permissions on the SQL Server instance. I have seen that issue a few
times
> > > before.
> > >
> > > Ben Nevarez, MCDBA, OCP
> > > Database Administrator
> > >
> > >
> > > "Alan" wrote:
> > >
> > > > I created a catalog by:
> > > >
> > > > Use Pubs
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'create'
> > > >
> > > > EXEC sp_fulltext_table 'publishers', 'create', 'Cat_Test',
> > 'upkcl_pubind'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','pub_name','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','city','add'
> > > >
> > > > EXEC sp_fulltext_column 'publishers','state','add'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','activate'
> > > >
> > > > EXEC sp_fulltext_catalog 'Cat_Test', 'start_full'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_change_tracking'
> > > >
> > > > EXEC sp_fulltext_table 'publishers','start_background_updateindex'
> > > >
> > > > If I use the SQL
> > > >
> > > > select city
> > > > from publishers
> > > > where contains (city, '"Paris"')
> > > >
> > > > It return no record.
> > > >
> > > > However, if I use
> > > >
> > > > select *
> > > > from publishers
> > > > where city = 'Paris'
> > > >
> > > > returned one record.
> > > >
> > > >
> > > >
> > > >
> >
> >
> >

Full-text searching

Are there any plans to add full-text searching capability to SQLCE? I know it's available in SQL Server Express but it would still be useful for an embedded/desktop application -- SQLite recently added this.We have this feature on our radar, but time lines for this have not been fixed yet

Full-text searches

BASIC QUESTION here:

Can you do JOINS with tables in a catalog?What catalog? I'm not sure I understand your question. Do you mean the SQL Server System tables? In that case, yes you can.|||I have 'enabled' full text search capability for 3 tables in my SQL Server database and defined which columns I want indexed.

Of those tables I have enabled, can I JOIN them in queries?|||You can join them in queries regardless of whether they are indexed.|||Ok, thanks for the info.

Full-text search: Stemming

In a column that I'm searching for, I have 'rod' and 'rods' for fishing gear.
I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
What am I missing?
I would think what you have should work...but try
contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
quoted
or
contains (p.*, "rod*")
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> In a column that I'm searching for, I have 'rod' and 'rods' for fishing
gear.
> I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> What am I missing?
>
|||Good suggestion. I tried that, too.
Same result, only rows with 'rod'.
If I query using LIKE '%rod%', then I get 20 rows so that's what I'm
expecting with full-text indexing.
What else should I be doing? Is stemming supposed to "turned on" or
something? The full-text language for all the columns is English.
"Wayne Snyder" wrote:

> I would think what you have should work...but try
> contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
> quoted
> or
> contains (p.*, "rod*")
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
> news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> gear.
>
>

Full-text search: Stemming

In a column that I'm searching for, I have 'rod' and 'rods' for fishing gear
.
I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
What am I missing?I would think what you have should work...but try
contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
quoted
or
contains (p.*, "rod*")
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> In a column that I'm searching for, I have 'rod' and 'rods' for fishing
gear.
> I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> What am I missing?
>|||Good suggestion. I tried that, too.
Same result, only rows with 'rod'.
If I query using LIKE '%rod%', then I get 20 rows so that's what I'm
expecting with full-text indexing.
What else should I be doing? Is stemming supposed to "turned on" or
something? The full-text language for all the columns is English.
"Wayne Snyder" wrote:

> I would think what you have should work...but try
> contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
> quoted
> or
> contains (p.*, "rod*")
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
> news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> gear.
>
>

Full-text search: Stemming

In a column that I'm searching for, I have 'rod' and 'rods' for fishing gear.
I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
What am I missing?I would think what you have should work...but try
contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
quoted
or
contains (p.*, "rod*")
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> In a column that I'm searching for, I have 'rod' and 'rods' for fishing
gear.
> I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> What am I missing?
>|||Good suggestion. I tried that, too.
Same result, only rows with 'rod'.
If I query using LIKE '%rod%', then I get 20 rows so that's what I'm
expecting with full-text indexing.
What else should I be doing? Is stemming supposed to "turned on" or
something? The full-text language for all the columns is English.
"Wayne Snyder" wrote:
> I would think what you have should work...but try
> contains(p.*, 'Formsof(Inflectional, rod)' ) -- note the keyword is not
> quoted
> or
> contains (p.*, "rod*")
>
> --
> Wayne Snyder, MCDBA, SQL Server MVP
> Mariner, Charlotte, NC
> www.mariner-usa.com
> (Please respond only to the newsgroups.)
> I support the Professional Association of SQL Server (PASS) and it's
> community of SQL Server professionals.
> www.sqlpass.org
> "MGBloomfield" <MGBloomfield@.discussions.microsoft.com> wrote in message
> news:24EC5637-8762-4A4C-9354-A7C5BE414864@.microsoft.com...
> > In a column that I'm searching for, I have 'rod' and 'rods' for fishing
> gear.
> >
> > I'm using WHERE CONTAINS ( p.*, 'FORMSOF(INFLECTIONAL, 'rod')')
> >
> > It only returns rows with 'rod'. I'm expecting rows with 'rod' and 'rods'.
> >
> > What am I missing?
> >
>
>

Full-text search: Problem with single/double quotes

Hi,
I am developing Knowledge Database(intranet) for my compnay.
I am using SQL Server 2000 as DB and Active Server Pages as Scripting
language.
I have a table, tb_kdb with 3 fields(title,keyword,description) as text
type.
I am insering the form data into DB after encoding.for e.g
rs("title") = Server.HTMLEncode(Request.Form("txtTitle"))
I have enabled Full-text search for these 3
fields(title,keyword,description).
Now whenever i enter data in the HTML form as - Error code: "H2SCX" in the
configuration file- for tile field.
This will be stored in the DB field (title) as - Error code:
"H2SCX" in the configuration file.
After this i do "start Full Population"
But when i try to search string - H2SCX from title field i will get 0
records found.Actually i should get 1 record found.
If i remove quotes around string - H2SCX and do again Full population and
seach, i get 1 record found.
I feel the problem is due to te quotes around string. I am facing this
problem for single & double quotes.
There are number of FAQs on our intranet site which has single or double
quotes...when searched those records will not be diaplayed.
I tried the below queriy on Sql Query Analyser
SELECT title FROM tb_kdb WHERE CONTAINS(title,'H2SCX')
SELECT @.@.Version output:
Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Any help or suggestion on this is higly appreciated.
Thanks & Best Rgds,
Surjit
replace your single quotes with two single quotes, and your double quotes
with two double quotes.
"Surjit Madiwalar" <sursatraj@.hotmail.com> wrote in message
news:ua3qw78FEHA.684@.tk2msftngp13.phx.gbl...
> Hi,
> I am developing Knowledge Database(intranet) for my compnay.
> I am using SQL Server 2000 as DB and Active Server Pages as Scripting
> language.
> I have a table, tb_kdb with 3 fields(title,keyword,description) as text
> type.
> I am insering the form data into DB after encoding.for e.g
> rs("title") = Server.HTMLEncode(Request.Form("txtTitle"))
> I have enabled Full-text search for these 3
> fields(title,keyword,description).
> Now whenever i enter data in the HTML form as - Error code: "H2SCX" in the
> configuration file- for tile field.
> This will be stored in the DB field (title) as - Error code:
> "H2SCX" in the configuration file.
> After this i do "start Full Population"
> But when i try to search string - H2SCX from title field i will get 0
> records found.Actually i should get 1 record found.
> If i remove quotes around string - H2SCX and do again Full population and
> seach, i get 1 record found.
> I feel the problem is due to te quotes around string. I am facing this
> problem for single & double quotes.
> There are number of FAQs on our intranet site which has single or double
> quotes...when searched those records will not be diaplayed.
> I tried the below queriy on Sql Query Analyser
> SELECT title FROM tb_kdb WHERE CONTAINS(title,'H2SCX')
> SELECT @.@.Version output:
> Microsoft SQL Server 2000 - 8.00.818 (Intel X86) May 31 2003 16:08:15
> Copyright (c) 1988-2003 Microsoft Corporation Standard Edition on Windows
> NT 5.0 (Build 2195: Service Pack 4)
>
> Any help or suggestion on this is higly appreciated.
> Thanks & Best Rgds,
> Surjit
>
>
>

Full-Text Search: Prefix / Suffix Search

Please help me to create an SQL Server 2000 Stored Procedure for using prefix and suffix terms.

Example:

Say I want to find "Terminator" (1984).

I want to be able to use "Term" or "ator" as search results and still return the proper record.

Here is my Stored Procedure creation sql:


CREATE PROCEDURE sps_searchTitles(@.searchTerm varchar(255)) AS
SELECT * FROM Video
WHERE FREETEXT (Video.*, '"*@.searchTerm*"')
GO

-- The above does not appear to properly check both prefix ("Term--") and suffix ("--ator") terms.

I am trying to accomplish what is similarly done with LIKE '%term%'.

thanks, YMYou want to use CONTAINS, not FREETEXT. For example:

USE Northwind
GO
SELECT ProductName
FROM Products
WHERE CONTAINS(ProductName, ' "choc*" ')
GO

Full-text Search, Query returns empty

Hello,
I'm working on a project using SQL server 2000 full text search. My os is
windows XP professional. (And I also tried to remote
connect to a windows 2000 server computer to do the same thing.) The problem
is same: query returns empty rows.
I followed all the steps from msdn website:Administering Full-Text
Features Using SQL Enterprise
Manager(http://msdn.microsoft.com/library/de...ullad_6g1f.asp).
It goes on well. But after running full population, I checked the properties
of the full-text catalog. It shows item count: 6. Unique Key count: 12.
I think something wrong here. Because I have a table with a data column
name File_data is image datatype. And I wrote a C#.net program to insert
several word doc, pdf, jpg file into the table SearchFile. The number of
unique key count should much bigger than 12.
And when I do the query, e.g.
SELECT File_title, File_data
FROM SearchFile
WHERE CONTAINS (File_data, 'cookies');
It returns empty rows.
This is my design table:
Column Name Data Type length
File_id(PK) int 4
file_type nvarchar 50
file_size nvarchar 50
file_title nvarchar 50
File_data image 16
File-time timestamp 8
This is part of the content of the table:
File_id file_type File_size File_title File_data File_time
8 image/pjpeg 4216 image1.jpg 0xFFD8FFE00010... 0x00000000000000D7
9 application/msword 29696 Introduction to ASP.doc 0xD0CF11E0A1B1...
0x00000000000000D9
10 application/pdf 449473 asp_net_whitepaper.pdf 0x255044462D31...
0x00000000000000DF
11 application/msword 26112 How do I use cookies in ASP.doc ...........
I can insert word,pdf,jpg file into SQL server 2000 database, and
retreive it in my asp.net web application successfully.
I also checked the services list for "Microsoft Search". It already
started.
I checked the log file SQL0001800005.1.gthr under Program
Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs.
It shows some error, but I don't know how to handle it.
3/17/2006 4:46:14 PM Add The gatherer has started
3/17/2006 4:46:14 PM Add The initialization has completed
3/17/2006 4:46:26 PM Add Started Full crawl
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000013 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000015 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000014 Add
Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
errors. Check each OLE DB status value, if available. No work was done. )
Multiple-step OLE DB
operation generated errors. Check each OLE DB status value, if available.
No work was done.
3/17/2006 4:46:30 PM Add Completed Full crawl
My query for full text search always returns empty. And I'm
sure the string I searched is inside the document.
Can anyone please give me some idea what is wrong here? I really
appreciate any help.
Thanks in advance!
Sincerely,
Sherry
Actually I found out the problem. I should save the file_type just use the
file extention e.g. .doc instead of application/msword.
Thanks,
Sherry
"Sherry" wrote:

> Hello,
> I'm working on a project using SQL server 2000 full text search. My os is
> windows XP professional. (And I also tried to remote
> connect to a windows 2000 server computer to do the same thing.) The problem
> is same: query returns empty rows.
> I followed all the steps from msdn website:Administering Full-Text
> Features Using SQL Enterprise
> Manager(http://msdn.microsoft.com/library/de...ullad_6g1f.asp).
> It goes on well. But after running full population, I checked the properties
> of the full-text catalog. It shows item count: 6. Unique Key count: 12.
> I think something wrong here. Because I have a table with a data column
> name File_data is image datatype. And I wrote a C#.net program to insert
> several word doc, pdf, jpg file into the table SearchFile. The number of
> unique key count should much bigger than 12.
> And when I do the query, e.g.
> SELECT File_title, File_data
> FROM SearchFile
> WHERE CONTAINS (File_data, 'cookies');
> It returns empty rows.
> This is my design table:
> Column Name Data Type length
> File_id(PK) int 4
> file_type nvarchar 50
> file_size nvarchar 50
> file_title nvarchar 50
> File_data image 16
> File-time timestamp 8
> This is part of the content of the table:
> File_id file_type File_size File_title File_data File_time
> 8 image/pjpeg 4216 image1.jpg 0xFFD8FFE00010... 0x00000000000000D7
> 9 application/msword 29696 Introduction to ASP.doc 0xD0CF11E0A1B1...
> 0x00000000000000D9
> 10 application/pdf 449473 asp_net_whitepaper.pdf 0x255044462D31...
> 0x00000000000000DF
> 11 application/msword 26112 How do I use cookies in ASP.doc ...........
> I can insert word,pdf,jpg file into SQL server 2000 database, and
> retreive it in my asp.net web application successfully.
> I also checked the services list for "Microsoft Search". It already
> started.
> I checked the log file SQL0001800005.1.gthr under Program
> Files\Microsoft SQL Server\MSSQL\FTDATA\SQLServer\GatherLogs.
> It shows some error, but I don't know how to handle it.
> 3/17/2006 4:46:14 PM Add The gatherer has started
> 3/17/2006 4:46:14 PM Add The initialization has completed
> 3/17/2006 4:46:26 PM Add Started Full crawl
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000013 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000015 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:28 PM MSSQL75://SQLServer/2c3393d0/00000014 Add
> Error fetching URL, (80040e21 - Multiple-step OLE DB operation generated
> errors. Check each OLE DB status value, if available. No work was done. )
> Multiple-step OLE DB
> operation generated errors. Check each OLE DB status value, if available.
> No work was done.
> 3/17/2006 4:46:30 PM Add Completed Full crawl
> My query for full text search always returns empty. And I'm
> sure the string I searched is inside the document.
> Can anyone please give me some idea what is wrong here? I really
> appreciate any help.
> Thanks in advance!
> Sincerely,
> Sherry

Full-text search with German umlaut

Hello everyone!
Iâ'm using the full text search with the German language and it works fine in
mapping the German umlaut "ü" into "ue", but unfortunately the other
direction doesnâ't work (the search for words including "ue" does not match
words including "ü").
I have tried to solve it by editing the thesaurus file but I didnâ't find a
possibility to specify only word sequences. It works only for words.
This works for "Mueller"
<expansion>
<sub>Mueller</sub>
<sub>Müller</sub>
</expansion>
But this doesâ'nt work:
<expansion>
<sub>ue</sub>
<sub>ü</sub>
</expansion>
How can I get the mapping of "ue" to "ü" within the full text search?
Thanks
RobIt works in SQL Server 2005 if you select the accent insensitive catalog
option.
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:41D47CF7-3BCB-45D7-A68F-1ED1DA4C629C@.microsoft.com...
> Hello everyone!
> I'm using the full text search with the German language and it works fine
> in
> mapping the German umlaut "ü" into "ue", but unfortunately the other
> direction doesn't work (the search for words including "ue" does not match
> words including "ü").
> I have tried to solve it by editing the thesaurus file but I didn't find a
> possibility to specify only word sequences. It works only for words.
> This works for "Mueller"
> <expansion>
> <sub>Mueller</sub>
> <sub>Müller</sub>
> </expansion>
> But this does'nt work:
> <expansion>
> <sub>ue</sub>
> <sub>ü</sub>
> </expansion>
> How can I get the mapping of "ue" to "ü" within the full text search?
> Thanks
> Rob
>|||Hi Hilary!
Thanks for your answer but unfortunately there is no change of the search
behaviour: Searching for "mueller" does not match "müller".
I tried those configurations (all possible combinations) using SQL Server
Express 9.00.2047:
I used an empty thesaurus file.
Database collations:
German_PhoneBook_CS_AI
German_PhoneBook_CS_AS
Latin1_General_CS_AI
Latin1_General_CS_AS
Fulltext Catalog ACCENT_SENSITIVITY:
ALTER FULLTEXT CATALOG TestFTCatalog REBUILD WITH ACCENT_SENSITIVITY = ON /
OFF
Did I misundertand your advice?
Rob
"Hilary Cotter" wrote:
> It works in SQL Server 2005 if you select the accent insensitive catalog
> option.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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

Full-text search with German umlaut

Hello everyone!
I’m using the full text search with the German language and it works fine
in
mapping the German umlaut "ü" into "ue", but unfortunately the other
direction doesn’t work (the search for words including "ue" does not match
words including "ü").
I have tried to solve it by editing the thesaurus file but I didn’t find a
possibility to specify only word sequences. It works only for words.
This works for "Mueller"
<expansion>
<sub>Mueller</sub>
<sub>Müller</sub>
</expansion>
But this does’nt work:
<expansion>
<sub>ue</sub>
<sub>ü</sub>
</expansion>
How can I get the mapping of "ue" to "ü" within the full text search?
Thanks
RobIt works in SQL Server 2005 if you select the accent insensitive catalog
option.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"RobRoma" <RobRoma@.discussions.microsoft.com> wrote in message
news:41D47CF7-3BCB-45D7-A68F-1ED1DA4C629C@.microsoft.com...
> Hello everyone!
> I'm using the full text search with the German language and it works fine
> in
> mapping the German umlaut "" into "ue", but unfortunately the other
> direction doesn't work (the search for words including "ue" does not match
> words including "").
> I have tried to solve it by editing the thesaurus file but I didn't find a
> possibility to specify only word sequences. It works only for words.
> This works for "Mueller"
> <expansion>
> <sub>Mueller</sub>
> <sub>Mller</sub>
> </expansion>
> But this does'nt work:
> <expansion>
> <sub>ue</sub>
> <sub></sub>
> </expansion>
> How can I get the mapping of "ue" to "" within the full text search?
> Thanks
> Rob
>|||Hi Hilary!
Thanks for your answer but unfortunately there is no change of the search
behaviour: Searching for "mueller" does not match "müller".
I tried those configurations (all possible combinations) using SQL Server
Express 9.00.2047:
I used an empty thesaurus file.
Database collations:
German_PhoneBook_CS_AI
German_PhoneBook_CS_AS
Latin1_General_CS_AI
Latin1_General_CS_AS
Fulltext Catalog ACCENT_SENSITIVITY:
ALTER FULLTEXT CATALOG TestFTCatalog REBUILD WITH ACCENT_SENSITIVITY = ON /
OFF
Did I misundertand your advice?
Rob
"Hilary Cotter" wrote:

> It works in SQL Server 2005 if you select the accent insensitive catalog
> option.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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