Showing posts with label searching. Show all posts
Showing posts with label searching. Show all posts

Wednesday, March 21, 2012

Funny search error

I have two fields I am searching on ... address and zipCode

When I have both pieces of data, everything is fine, i get back the expected results, but i am unable able to search on the individual pieces. Here is the SQL I am using. It's placed within a store procedure in Sqlserver that I amreferencing from my VB.net code... any help??


IF ((NOT @.address IS NULL) AND( NOT @.zipCode IS NULL))
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @.address AND ShpAddr_ZipCode = @.zipCode
END

ELSE IF NOT @.address IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = @.address
END

ELSE IF NOT @.zipCode IS NULL
BEGIN
SELECT OrderNumber FROM Orders WHERE ShpAddr_ZipCode = @.zipCode
END

any help would be great. thanksMy guess is that your parameters (@.address and @.zipCode) contain empty strings, not NULLs.

You VB.NET code should be setting the parameter value to SQLString.Null if the user did not choose to use the parameter.

I would then also consider changing your stored procedure to simply this:


SELECT OrderNumber FROM Orders WHERE ShpAddr_Address1 = ISNULL(@.address,ShpAddr_Address1) AND ShpAddr_ZipCode = ISNULL(@.zipCode,ShpAddr_ZipCode)

I make this suggestion for ease of code maintenance. I myself have gotten burned several times over the past month by not changing all relevant similar blocks of code in a stored procedure when the logic needed to change.

Terri|||I'm trying this right now as we speak, but my program refuses to reconize what a "SqlString" is. Is there anything special i have to import?|||It's in the System.Data.SqlTypes namespace, so you can either import the namespace or reference it with the fully qualified name: System.Data.SqlTypes.SqlString.Null.

Terri

Sunday, February 26, 2012

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 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 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?
> >
>
>

Friday, February 24, 2012

Full-Text Search is not enabled for the current database

We are having problems with Full-Text searching...
Recently we installed a SQL application that needs to access full-text
searching.
It gave the following error:
"The crawl seed <MSSQL75://SQLServer/0539c240> in project <SQLServer
SQL0001400006> cannot be accessed. Error: 8007052e - Logon failure: unknown
user name or bad password."
Upgrading from SQL2kSP3a to SQL2kSP4 didn't change our situation.
Changing to system account following instructions in KB277549 also didn't
fix the problem.
We decided to reinstall the MSSearch service following commands in KB827449.
We received the following error:
"Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database."
Then we ran EXEC sp_fulltext_database 'enable'.
This didn't resolve the problem as we received error message:
"Microsoft search service cannot be administered under present user account."
Current Status recap:
* MSSearch service is started
* In EM > Support Services > Full Text Search is running
* In EM > R-click on Full-Text Catalogs > Rebuild All Catalogs produces
error:
"Microsoft search service cannot be administered under present user account."
* In EM > R-click on catalog within Full-Text Catalog > Rebuild Catalog
produces error:
"Full-Text Search is not enabled for the current database. Use
sp_fulltext_database to enable full-text search for the database."
Not sure if this is user account related or bad install of MSSearch.
Before reloading SQL or contacting MS Support, any advice is appreciated.
Thanks!
D Krage,
As you have reviewed KB 277549 (Q277549) PRB: Unable to Build Full-Text
Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
Panel [or Win2K Component Services] at
http://support.microsoft.com/default...;EN-US;277549, did you do
all of the "Steps to Reproduce Behavior", including stopping & re-starting
the MSSearch & MSSQLServer?
The error you're getting (Logon failure: unknown user name or bad password)
is typical of the above. Additionally, have you or anyone else removed or
altered the BUILTIN\Administrators login? If so, can you re-add it or alter
it back to its original values, including the master database and with
sysadmin rights?
You may also want to review KB article 317746 "PRB: SQL Server Full-Text
Search Does Not Populate Catalogs" at
http://support.microsoft.com/default...b;en-us;317746
However, since you've moved ahead with re-installing the MSSearch components
via the procedures in KB 827449, the above most likely no longer applies. At
this time, the following is the primary error: "Microsoft search service
cannot be administered under present user account." and usually, this error
indicates a missing or altered BUILTIN\Administrators login, but you might
also want to checkout the steps at
http://www.doughughes.net/index.cfm/...nk/entryId-126 as others have
had success with this method in correcting this error.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"d krage" <d krage@.discussions.microsoft.com> wrote in message
news:4C845DC5-B0D7-4F45-A0FC-FB3DCF5F8427@.microsoft.com...
> We are having problems with Full-Text searching...
> Recently we installed a SQL application that needs to access full-text
> searching.
> It gave the following error:
> "The crawl seed <MSSQL75://SQLServer/0539c240> in project <SQLServer
> SQL0001400006> cannot be accessed. Error: 8007052e - Logon failure:
> unknown
> user name or bad password."
> Upgrading from SQL2kSP3a to SQL2kSP4 didn't change our situation.
> Changing to system account following instructions in KB277549 also didn't
> fix the problem.
> We decided to reinstall the MSSearch service following commands in
> KB827449.
> We received the following error:
> "Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable full-text search for the database."
> Then we ran EXEC sp_fulltext_database 'enable'.
> This didn't resolve the problem as we received error message:
> "Microsoft search service cannot be administered under present user
> account."
> Current Status recap:
> * MSSearch service is started
> * In EM > Support Services > Full Text Search is running
> * In EM > R-click on Full-Text Catalogs > Rebuild All Catalogs produces
> error:
> "Microsoft search service cannot be administered under present user
> account."
> * In EM > R-click on catalog within Full-Text Catalog > Rebuild Catalog
> produces error:
> "Full-Text Search is not enabled for the current database. Use
> sp_fulltext_database to enable full-text search for the database."
> Not sure if this is user account related or bad install of MSSearch.
> Before reloading SQL or contacting MS Support, any advice is appreciated.
> Thanks!
|||Thanks for your reply John. As it turns out the error indicated a problem
with user rights, however, the problem turns out to be a bad install of
MSSearch.
Here is what corrected the problem...from Google groups.
http://groups.google.com/group/micro...b7c7692a0e2ff1
According to Google search results, there should be a
HKLM\Software\Microsoft\Search\1.0\Applications\SQ L Server key. This did not
exist, but existed on two of our other servers. The article said to go to
regedit under HKLM\Software\Microsoft\MSSQLServer\Tracking and rename the key
{E07FDDA7-5A21-11d2-9DAD-00C04F79D434}. I renamed the key by putting an X in
front of the name. This makes the SQL Server think that the Search service is
not installed with SQL Server.
After this, shut down all affected services and ran the SQL 2000 install
from CD and did a Custom to modify the installation. All components were
already checked except the Search, so checked the search. Did not uncheck
other components or this would uninstall the other components.
After doing this and rebooting, catalog creation appeared to work. Added
item to database and number of items in catalog increased. Reapplied Service
pack 4 to update the SQL search files. Rebooted. Our SQL app search function
is now working.
Hope this can help someone else out there.
d Krage
"John Kane" wrote:

> D Krage,
> As you have reviewed KB 277549 (Q277549) PRB: Unable to Build Full-Text
> Catalog After You Modify MSSQLServer Logon Account Through [NT4.0) Control
> Panel [or Win2K Component Services] at
> http://support.microsoft.com/default...;EN-US;277549, did you do
> all of the "Steps to Reproduce Behavior", including stopping & re-starting
> the MSSearch & MSSQLServer?
> The error you're getting (Logon failure: unknown user name or bad password)
> is typical of the above. Additionally, have you or anyone else removed or
> altered the BUILTIN\Administrators login? If so, can you re-add it or alter
> it back to its original values, including the master database and with
> sysadmin rights?
> You may also want to review KB article 317746 "PRB: SQL Server Full-Text
> Search Does Not Populate Catalogs" at
> http://support.microsoft.com/default...b;en-us;317746
> However, since you've moved ahead with re-installing the MSSearch components
> via the procedures in KB 827449, the above most likely no longer applies. At
> this time, the following is the primary error: "Microsoft search service
> cannot be administered under present user account." and usually, this error
> indicates a missing or altered BUILTIN\Administrators login, but you might
> also want to checkout the steps at
> http://www.doughughes.net/index.cfm/...nk/entryId-126 as others have
> had success with this method in correcting this error.
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "d krage" <d krage@.discussions.microsoft.com> wrote in message
> news:4C845DC5-B0D7-4F45-A0FC-FB3DCF5F8427@.microsoft.com...
>
>
|||You're welcome, d Krage,
After reviewing the Google groups link & thread as that was an old one from
back in 2001 as I was the person who helped Chris with "The solution that
John found to my problem was this:" back when I was fulltime at Microsoft...
I'm now a bit older and wiser, but it still is good to be remembered!
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"d krage" <dkrage@.discussions.microsoft.com> wrote in message
news:8F7D817F-B35C-4689-97E4-C79B711842B8@.microsoft.com...[vbcol=seagreen]
> Thanks for your reply John. As it turns out the error indicated a problem
> with user rights, however, the problem turns out to be a bad install of
> MSSearch.
> Here is what corrected the problem...from Google groups.
> http://groups.google.com/group/micro...b7c7692a0e2ff1
> According to Google search results, there should be a
> HKLM\Software\Microsoft\Search\1.0\Applications\SQ L Server key. This did
> not
> exist, but existed on two of our other servers. The article said to go to
> regedit under HKLM\Software\Microsoft\MSSQLServer\Tracking and rename the
> key
> {E07FDDA7-5A21-11d2-9DAD-00C04F79D434}. I renamed the key by putting an X
> in
> front of the name. This makes the SQL Server think that the Search service
> is
> not installed with SQL Server.
> After this, shut down all affected services and ran the SQL 2000 install
> from CD and did a Custom to modify the installation. All components were
> already checked except the Search, so checked the search. Did not uncheck
> other components or this would uninstall the other components.
> After doing this and rebooting, catalog creation appeared to work. Added
> item to database and number of items in catalog increased. Reapplied
> Service
> pack 4 to update the SQL search files. Rebooted. Our SQL app search
> function
> is now working.
> Hope this can help someone else out there.
> d Krage
> "John Kane" wrote:

Full-Text Search in SQL Server Express?

Hi, all,

I heard a rumor that the next CTP of SQL Server Express would allow installation of full-text searching (MSFTE). Has anyone else heard this? Is it true?

Thanks,
Mike

Hi, all,

Looks like it's true: http://news.com.com/2100-1012_3-6062696.html?part=rss&tag=6062696&subj=news.

Thanks,
Mike

Full-Text Search in SQL Server Express?

Hi, all,

I heard a rumor that the next CTP of SQL Server Express would allow installation of full-text searching (MSFTE). Has anyone else heard this? Is it true?

Thanks,
Mike

Hi, all,

Looks like it's true: http://news.com.com/2100-1012_3-6062696.html?part=rss&tag=6062696&subj=news.

Thanks,
Mike

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.com
You might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Sunday, February 19, 2012

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
--
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegroups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegroups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,
We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.
As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.
Thanks.
John Fleming
jfleming@.misicompany.com
You might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.
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
"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com
>

Full-text search across multiple tables

Hello,

We are developing an application against an MS SQL Server 2000 database
which requires that we implement full-text searching across columns in
multiple tables. The research that we have conducted seems to indicate
that this is not directly possible within SQL Server 2000. And we can
find no way to implement this as the catalogs that are generated are
table specific.

As a work-around, we are planning to create a secondary table used
strictly for searching which is going to denormalize and combine the
searchable data into one catalog. We just want to confirm that this is
the recommended approach or if there is an alternate solution anyone
has used. Please note that upgrading to SQL Server 2005 is not an
option.

Thanks.

John Fleming
jfleming@.misicompany.comYou might be able to use the ability to full text index an indexed view in
SQL 2005. Other than that you are correct, all you can do is try to create a
child table containing both tables and do some resolution back to the base
tables based on the key.

--
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

"John" <jflemingnj@.gmail.com> wrote in message
news:1137167503.574033.165460@.g47g2000cwa.googlegr oups.com...
> Hello,
> We are developing an application against an MS SQL Server 2000 database
> which requires that we implement full-text searching across columns in
> multiple tables. The research that we have conducted seems to indicate
> that this is not directly possible within SQL Server 2000. And we can
> find no way to implement this as the catalogs that are generated are
> table specific.
> As a work-around, we are planning to create a secondary table used
> strictly for searching which is going to denormalize and combine the
> searchable data into one catalog. We just want to confirm that this is
> the recommended approach or if there is an alternate solution anyone
> has used. Please note that upgrading to SQL Server 2005 is not an
> option.
> Thanks.
> John Fleming
> jfleming@.misicompany.com

Full-text search - catalog refuses to populate

Hello

I'm trying to get full-text searching to work on SQL Server 2000 (with SP3 installed).

I have a table containing many text fields that I want to search and so far have successfully gone through the "Full-Text wizard" selecting the text fields from the table I want as searchable. I then try and start full population of the catalog but its always remains zero in size when I check its properties. Hence when I try a query like:

SELECT Name FROM tblTextDocs WHERE CONTAINS(mainbody, 'find this')

It results in the error message:

Server: Msg 7619, Level 16, State 1, Line 1
Execution of a full-text operation failed. The catalog does not exist or is currently unavailable. Please retry the action again later and if this symptom persists, contact the system administrator.

It would appear that the catalog simply refuses to populate. I've tried starting population via Enterprise Manager and also by doing sp_fulltext_catalog 'MyCatalog', 'start_full'. The Full-text support service says it is running correctly and no other problems with the server have been experienced.

Any suggestions would be much appreciated.

Regards

SamBy the "Full Text Support Service" do you mean the "Microsoft Search" service?|||Originally posted by MCrowley
By the "Full Text Support Service" do you mean the "Microsoft Search" service?

Yes - the Microsoft Search service. I have no problems in stopping/starting it too.|||This error might occur if changing SQL Server Account to Non-Admin for Full-Text Search Makes Existing Catalogs Unusable.

To access existing full-text search catalogs, rebuild and repopulate them. The existing catalogs can also be accessed by switching back to an administrator account.

FullText Results from what column

I am running SQL 2000 - upgrading to 2005 hopefully Q1-2007

I have a table that has 12 columns fulltext indexed for searching. Is there a way to tell what column has the search string?

If it is not possible in 2000 what about 2005?

Thanks

If you do a query like select * from t1 where contains(*,'term') you cannot tell which column the hit was in.

You could do a set of queries with each column in turn if you wanted to tell where the term was found:

select * from t1 where contains(col1, 'term')

select * from t1 where contains(col2, 'term')

etc..

In SQL 2005 you can specify a column list in the query (rather than just a single column or *) so you could do queries like:

select * from t1 where conatins((col1,col2,col3), 'term')

Depending on your application that may be useful to you.

Hope this helps.

Dave