Fulltable scan/sec - How to resolve

Hi All,

When we did PT for our application, Full Scans/sec is really huge and it is between 75 to 150. When we did stress testing, it is above 500.

Please help me in identifying the problem that causes too much of FT scans.

I tried doing this, but I'm not sure whether the approach is correct:

Step 1. Create performance log counter against SQLServer:Access Methods\Full Scans/sec
(Results as)
Time \SQLServer:Access Methods\Full Scans/sec
07/19/2006 04:56:03 PM 13.518823923
07/19/2006 04:56:13 PM 3.600423705
07/19/2006 04:56:23 PM 30
07/19/2006 04:56:33 PM 10.099895061

Step 2. Create Trace (using SQL Profiler)
Start both the Performance log and SQL Profiler at same time.

Step 3. Pick a time duration from Performance Log results and identify the queries that got executed during this time frame using Profiler.

Step 4. From Profiler results, identify the queries that got executed and evaluate the query plan of these queries.
Here I'm not clear with the queries that i pick, because there are different eventclass during this time like RPC:Completed,SQL:BatchStarting, SQL:BatchCompleted. I did analysis on "TextData" only against RPC:Completed and counted on Table/Clustered Index Scans from Query Plan.

Full Scans/sec. isn't always an indicator of a problem needing resolution. For instance, if I have a read-only lookup table that has 5 rows, chances are, I won't pay much attention to indexing this column. If it's used a lot, then you'll likely see a table scan every time. Is that a problem? Maybe, maybe not. If reading 5 rows (probably a single IO) from a table is your only application bottleneck, fixing it may take higher priority.

So, make sure that you're really looking at a problem that needs to be solved. Regarding your question, it sounds like you're on the right track. Find the plan events, and then locate the ones that have Table Scans in the operator tree.

Thanks,
--R

full text serch with no user istance?

HI

I' making an archive applicaton that will be distribuited on cd.

the application contain a simple database witn one million of document with author , title and full document in text format.

i want to make a full text serch on the documents.

this cd will be distribuited to normal user so it have to be simple to install and use.

in the cd I'have put the setup of sql server express advenced with the .ini file for an automatic setup with the required component(full-text serch engine too)

when the installation program has finished you have a directory with a simple c# program and the .mdf and .ldf file for the database.

in the database you have 4 columns "id" "title" "author" "text"

with "SQL Server Management Studio Express" I have create the full text on the database using this sql line

"CREATE FULLTEXT CATALOG cat" to create the catalog then I have create an index key on ID column and then a full text for text column.

now the probelm is that i can make a full-text serch in a user istance.

in visual studio 2005 i select the option "insert existing element in the project" then I'have select the mdf file, but after the insert all the query work fine expet the "SELECT * FROM db WHERE CONTAINS(tilte,'word')" it say me "unable to do a full-text serch in an user istance.

I'have tried to manipulate the connection string setting user istance=false but I have encountered two problem.

1- I can't set a file location like |program dir|\db.mdb

2- if i set a real file location the program work only on my pc and only if the visul studio in open.

whot I have to do to make it work?

Full-text Search does not work in User Instances.

Mike

|||

this means that is impossible with c# and sql server express advanced to make an application with full text serch, that can be distribuite on cd for normal user?

Full Text Search Population Method...

Hi,
I am confused by the different method of population, of both Full Text
index and Full Text Catalog.
Currently, we are running a application (3rd party) on SQL 2000, and we
have set the Full Text indexing to be "Change Tracking" and "background
update". By having this combination, do we still need to instruct the SQL
Server to do an incremental update on both the Catalog and Indexes? If yes,
which method should I use?
TTS,
The short answer is no. You do not need to run a scheduled, i.e.. instruct
the SQL Server to do an incremental population. In the background, SQL
Server uses an internal (and un-documented) system table to gather changes
based upon the database logged changes (updates, inserts & deletes) for the
FT-enable table column changes and then pushes these changes to the FT
Catalog in near real-time performance.
However and depending upon the amount (number of rows) and the frequency
(once every minute, once ever hour, etc.) you may want to turn off "Update
Index in Background" if the amount and frequency of changes is very high,
say >50% of the FT-enable table would change. In this case, you would
disenable "Update Index in Background" and setup a scheduled Incremental
Population to handle this one time massive update. You can read more about
this in the SQL Server 2000 BOL title "Maintaining Full-Text Indexes" -
specifically, "The method you use depends on factors such as the CPU and
available memory, the amount and rate of change of data, the amount of
available disk space, and the importance of the full-text index being
current. Use these recommendations as a guide for selecting a maintenance
method".
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:C120738B-E239-46F5-BF5D-CFA3F908AFA6@.microsoft.com...
> Hi,
> I am confused by the different method of population, of both Full Text
> index and Full Text Catalog.
> Currently, we are running a application (3rd party) on SQL 2000, and
we
> have set the Full Text indexing to be "Change Tracking" and "background
> update". By having this combination, do we still need to instruct the SQL
> Server to do an incremental update on both the Catalog and Indexes? If
yes,
> which method should I use?
>
|||Thanks John.
So to conclude, even though the status of the Full Text Catelog is
"IDLE", the Full Text index and Full Text Catelog will be populated with the
"Change Tracking" and "background update" configuration. And we will not have
any ways of telling whether the Full Text index and Catelog has been
populated, we can only trust that it has been populated.
"John Kane" wrote:

> TTS,
> The short answer is no. You do not need to run a scheduled, i.e.. instruct
> the SQL Server to do an incremental population. In the background, SQL
> Server uses an internal (and un-documented) system table to gather changes
> based upon the database logged changes (updates, inserts & deletes) for the
> FT-enable table column changes and then pushes these changes to the FT
> Catalog in near real-time performance.
> However and depending upon the amount (number of rows) and the frequency
> (once every minute, once ever hour, etc.) you may want to turn off "Update
> Index in Background" if the amount and frequency of changes is very high,
> say >50% of the FT-enable table would change. In this case, you would
> disenable "Update Index in Background" and setup a scheduled Incremental
> Population to handle this one time massive update. You can read more about
> this in the SQL Server 2000 BOL title "Maintaining Full-Text Indexes" -
> specifically, "The method you use depends on factors such as the CPU and
> available memory, the amount and rate of change of data, the amount of
> available disk space, and the importance of the full-text index being
> current. Use these recommendations as a guide for selecting a maintenance
> method".
> Regards,
> John
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:C120738B-E239-46F5-BF5D-CFA3F908AFA6@.microsoft.com...
> we
> yes,
>
>
|||Hi,
Can anybody confirm my conclusion?
"tts" wrote:
[vbcol=seagreen]
> Thanks John.
> So to conclude, even though the status of the Full Text Catelog is
> "IDLE", the Full Text index and Full Text Catelog will be populated with the
> "Change Tracking" and "background update" configuration. And we will not have
> any ways of telling whether the Full Text index and Catelog has been
> populated, we can only trust that it has been populated.
> "John Kane" wrote:
|||Hi TTS,
Sorry for the delay in replying! No, and depending upon how much content you
changed, you would see the following status values as well (from SQL 2000
BOL titles "sp_help_fulltext_catalogs" and FULLTEXTCATALOGPROPERTY)
Property Description
PopulateStatus 0 = Idle
1 = Full population in progress
2 = Paused
3 = Throttled
4 = Recovering
5 = Shutdown
6 = Incremental population in progress
7 = Building index
8 = Disk is full. Paused.
9 = Change tracking
For CT with UIiB and depending upon what was occurring, you could see
PopulateStatus values of 1, 6, 7 and 9. You can also use the
FULLTEXTCATALOGPROPERTY metadata query to get these values as well, for
example:
select
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
Hope that help!
John
"tts" <tts@.discussions.microsoft.com> wrote in message
news:F8A58279-7077-4CFC-9F3F-57F60787F615@.microsoft.com...[vbcol=seagreen]
> Hi,
> Can anybody confirm my conclusion?
> "tts" wrote:
the[vbcol=seagreen]
have[vbcol=seagreen]
instruct[vbcol=seagreen]
changes[vbcol=seagreen]
for the[vbcol=seagreen]
frequency[vbcol=seagreen]
"Update[vbcol=seagreen]
high,[vbcol=seagreen]
Incremental[vbcol=seagreen]
about[vbcol=seagreen]
Indexes" -[vbcol=seagreen]
and[vbcol=seagreen]
maintenance[vbcol=seagreen]
Full Text[vbcol=seagreen]
and[vbcol=seagreen]
"background[vbcol=seagreen]
the SQL[vbcol=seagreen]
If[vbcol=seagreen]
|||Hello John,
Thanks for your explanation. I have encountered a strange situation.
The index is not populated everytime with the insertion of record.
Please correct me if I am wrong. From what I understand, if I am
using Change Tracking and Background update, the index will be updated almost
immediately, if not, when the CPU is free to do the processing. However, this
do not apply all the time. I have tested out quite a number of time, not all
the insertion will cause the population of the indexes. From what I have
tested, if the keyword has been inserted into index, then the index will be
updated. Otherwise, the keyword will not be inserted into the index. Please
advise me what I should lookup for in the population and using of Full Text
Search.
Although I have done quite a number of testing, the size and
datetime stamp of the Full Text Catalog remain the same (as of last Friday),
and the status is IDLE. So I am not sure whether the population did take
place or not.
"John Kane" wrote:

> Hi TTS,
> Sorry for the delay in replying! No, and depending upon how much content you
> changed, you would see the following status values as well (from SQL 2000
> BOL titles "sp_help_fulltext_catalogs" and FULLTEXTCATALOGPROPERTY)
> Property Description
> PopulateStatus 0 = Idle
> 1 = Full population in progress
> 2 = Paused
> 3 = Throttled
> 4 = Recovering
> 5 = Shutdown
> 6 = Incremental population in progress
> 7 = Building index
> 8 = Disk is full. Paused.
> 9 = Change tracking
>
> For CT with UIiB and depending upon what was occurring, you could see
> PopulateStatus values of 1, 6, 7 and 9. You can also use the
> FULLTEXTCATALOGPROPERTY metadata query to get these values as well, for
> example:
> select
> objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
> Hope that help!
> John
>
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:F8A58279-7077-4CFC-9F3F-57F60787F615@.microsoft.com...
> the
> have
> instruct
> changes
> for the
> frequency
> "Update
> high,
> Incremental
> about
> Indexes" -
> and
> maintenance
> Full Text
> and
> "background
> the SQL
> If
>
>
|||You're welcome, TTS,
Is it not populating every time or just some of the time? If it is the
former, then this is not only possible, but can also occur when CT & UIiB is
not enabled. The usual symptom is that the FT Catalog property indicates
that the FT Catalog only contains an item count of 1 (row count + 1) row and
a unique key count (unique non-noise word count) of 0.
If this is the case, the most likely the FT Catalog is not populating for
several reasons, but the most likely is that the MSSQLServer service account
&/or password has been changed by Win2K's Component services (or control
panel) and not via the Enterprise Manager's server security tab as this is
the only place that the security of the two services (MSSQLServer &
MSSearch) are sync'ed up.
Also, could you confirm that your server's "Microsoft Search" (mssearch.exe)
service is started & under the "system account" (LocalSystem) and not some
other account? If it is not started under the LocalSystem, please change it
to LocalSystem and stop & restart the service. See the following KB article
for more details:
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...B;EN-US;277549
Yes, your assumption is correct, but the actual *near* real-time update can
sometimes take up to 5 seconds before you can issue a SQL FTS query and see
the inserted row. This can vary to as much as 7 to 10 seconds, depending
upon the amount & frequency of updates.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:449F08E2-2246-4393-B383-CE8779E80933@.microsoft.com...
> Hello John,
> Thanks for your explanation. I have encountered a strange
situation.
> The index is not populated everytime with the insertion of record.
> Please correct me if I am wrong. From what I understand, if I am
> using Change Tracking and Background update, the index will be updated
almost
> immediately, if not, when the CPU is free to do the processing. However,
this
> do not apply all the time. I have tested out quite a number of time, not
all
> the insertion will cause the population of the indexes. From what I have
> tested, if the keyword has been inserted into index, then the index will
be
> updated. Otherwise, the keyword will not be inserted into the index.
Please
> advise me what I should lookup for in the population and using of Full
Text
> Search.
> Although I have done quite a number of testing, the size and
> datetime stamp of the Full Text Catalog remain the same (as of last
Friday),[vbcol=seagreen]
> and the status is IDLE. So I am not sure whether the population did take
> place or not.
> "John Kane" wrote:
you[vbcol=seagreen]
2000[vbcol=seagreen]
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')[vbcol=seagreen]
is[vbcol=seagreen]
with[vbcol=seagreen]
not[vbcol=seagreen]
SQL[vbcol=seagreen]
deletes)[vbcol=seagreen]
the FT[vbcol=seagreen]
very[vbcol=seagreen]
would[vbcol=seagreen]
more[vbcol=seagreen]
CPU[vbcol=seagreen]
amount of[vbcol=seagreen]
being[vbcol=seagreen]
2000,[vbcol=seagreen]
instruct[vbcol=seagreen]
Indexes?[vbcol=seagreen]
|||Hello John,
Sorry, did not get back to you regarding this issue. The problem that
I have raised is because of the software bug, so the indexes are not
populated correctly.
However, this lead me to another few questions.
1) If a table is Change Tracking enabled, when we specify a "On
demand" or "Schedule" population, is it performing a Full Population? The
table does not have any date-time stamp.
2) If a full population is taking place, and there are records going
into the Indexed table, how will the FTS behave? Does that means that the FTS
will redo the full population of the index again, or it will just include the
new text, and perform a incremental population?
I am trying to determine which type of population method has the best
perfomance since there is a slight flaw in the software.
"John Kane" wrote:

> You're welcome, TTS,
> Is it not populating every time or just some of the time? If it is the
> former, then this is not only possible, but can also occur when CT & UIiB is
> not enabled. The usual symptom is that the FT Catalog property indicates
> that the FT Catalog only contains an item count of 1 (row count + 1) row and
> a unique key count (unique non-noise word count) of 0.
> If this is the case, the most likely the FT Catalog is not populating for
> several reasons, but the most likely is that the MSSQLServer service account
> &/or password has been changed by Win2K's Component services (or control
> panel) and not via the Enterprise Manager's server security tab as this is
> the only place that the security of the two services (MSSQLServer &
> MSSearch) are sync'ed up.
> Also, could you confirm that your server's "Microsoft Search" (mssearch.exe)
> service is started & under the "system account" (LocalSystem) and not some
> other account? If it is not started under the LocalSystem, please change it
> to LocalSystem and stop & restart the service. See the following KB article
> for more details:
> 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...B;EN-US;277549
> Yes, your assumption is correct, but the actual *near* real-time update can
> sometimes take up to 5 seconds before you can issue a SQL FTS query and see
> the inserted row. This can vary to as much as 7 to 10 seconds, depending
> upon the amount & frequency of updates.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
> "tts" <tts@.discussions.microsoft.com> wrote in message
> news:449F08E2-2246-4393-B383-CE8779E80933@.microsoft.com...
> situation.
> almost
> this
> all
> be
> Please
> Text
> Friday),
> you
> 2000
> objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')
> is
> with
> not
> SQL
> deletes)
> the FT
> very
> would
> more
> CPU
> amount of
> being
> 2000,
> instruct
> Indexes?
>
>
|||Not to worry, TTS, as Christmas was this past weekend!
1) If a table is Change Tracking enabled, when we specify a "On demand" or
"Schedule" population, is it performing a Full Population? The table does
not have any date-time stamp.
A. Yes. Because the table does not have a timestamp column, a Full
Population is always done, even if you select an Incremental Population.
2) If a full population is taking place, and there are records going into
the Indexed table, how will the FTS behave?
A. An interesting question... I've not actually tested this, as a Full
Population removes all previous data in the FT Catalog (much like a Truncate
table in T-SQL does to a sql table's data), and then starts reading from the
SQL Table's data from the beginning. I do *suspect* that the new data will
be read from the database log and populated to the FT Catalog after Full
Population is completed, but I think this may only be true if a timestamp
column was present. You should test this yourself before making any
decisions.
Hope this helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"tts" <tts@.discussions.microsoft.com> wrote in message
news:5B0EC1EA-0DA4-4A5E-B3DC-52A3F8C6C5C0@.microsoft.com...
> Hello John,
> Sorry, did not get back to you regarding this issue. The problem
that
> I have raised is because of the software bug, so the indexes are not
> populated correctly.
> However, this lead me to another few questions.
> 1) If a table is Change Tracking enabled, when we specify a "On
> demand" or "Schedule" population, is it performing a Full Population? The
> table does not have any date-time stamp.
> 2) If a full population is taking place, and there are records going
> into the Indexed table, how will the FTS behave? Does that means that the
FTS
> will redo the full population of the index again, or it will just include
the
> new text, and perform a incremental population?
> I am trying to determine which type of population method has the
best[vbcol=seagreen]
> perfomance since there is a slight flaw in the software.
> "John Kane" wrote:
UIiB is[vbcol=seagreen]
and[vbcol=seagreen]
for[vbcol=seagreen]
account[vbcol=seagreen]
is[vbcol=seagreen]
(mssearch.exe)[vbcol=seagreen]
some[vbcol=seagreen]
it[vbcol=seagreen]
article[vbcol=seagreen]
Component[vbcol=seagreen]
http://support.microsoft.com/default...B;EN-US;277549[vbcol=seagreen]
can[vbcol=seagreen]
see[vbcol=seagreen]
am[vbcol=seagreen]
However,[vbcol=seagreen]
not[vbcol=seagreen]
have[vbcol=seagreen]
will[vbcol=seagreen]
take[vbcol=seagreen]
content[vbcol=seagreen]
see[vbcol=seagreen]
for[vbcol=seagreen]
objectproperty(object_id(N'<table_name>'),N'TableF ullTextPopulateStatus')[vbcol=seagreen]
Catelog[vbcol=seagreen]
populated[vbcol=seagreen]
will[vbcol=seagreen]
been[vbcol=seagreen]
i.e..[vbcol=seagreen]
background,[vbcol=seagreen]
gather[vbcol=seagreen]
to[vbcol=seagreen]
off[vbcol=seagreen]
you[vbcol=seagreen]
read[vbcol=seagreen]
the[vbcol=seagreen]
index[vbcol=seagreen]
both[vbcol=seagreen]
SQL[vbcol=seagreen]

Full Text Search Indexing HTML - does the filter expect certain tags to be present as standard?

Hi, I was wondering if any SQL Server gurus out there could help me...

I have a table which contains text resources for my application. The text resources are multi-lingual so I've read that if I add a html language indicator meta tag e.g.
<META NAME="MS.LOCALE" CONTENT="ES">
and store the text in a varbinary column with a supporting Document Type column containing ".html" of varchar(5) then the full text index service should be intelligent about the language word breakers it applies when indexing the text. (I hope this is correct technique for best multi-lingual support in a single table?)

However, when I come to query this data the results always return 0 rows (no errors are encountered). e.g.
DECLARE @.SearchWord nvarchar(256)
SET @.SearchWord = 'search' -- Yes, this word is definitely present in my resources.
SELECT * FROM Resource WHERE CONTAINS(Document, @.SearchWord)

I'm a little puzzled as Full Text search is working fine on another table that employs an nvarchar column (just plain text, no html).

Does the filter used for full text indexing of html expect certain tags to be present as standard? E.g. <html> and <body> tags? At present the data I have stored might look like this (no html or body wrapping tags):

Example record 1 data: <META NAME="MS.LOCALE" CONTENT="EN">Search for keywords:

Example record 2 data: <META NAME="MS.LOCALE" CONTENT="EN">Sorry no results were found for your search.

etc.

Any pointers / suggestions would be greatly appreciated. Cheers,
Gavin.

UPDATE: I have tried wrapping the text in more usual html tags and re-built the full text index but I still never get any rows returned for my query results. Example of content wrapping tried - <HTML><HEAD><META NAME="MS.LOCALE" CONTENT="EN"></HEAD><BODY>Test text.</BODY></HTML>

I've also tried stripping all html tags from the content and set the Document Type column = .txt but I still get no rows returned?!?I've further isolated what the problem is and have started a new thread to request more specific help...
http://forums.microsoft.com/TechNet/ShowPost.aspx?PostID=1844786&SiteID=17

Full Text Search for Stand Alone Windows App

I am working on a stand-alone windows application in which I need to provide full text search capability. I was using SQL Express edition and was planning to deploy the database (.mdf) with the application.

Is it possible to enable Full Text search for a database which does not belong to a database server (embedded with the app)? If yes the how can you do it?

If its not possible then what are the alternatives/options of data-storage for a windows app which will allow me to enable Full Text search?

Thanks for your help.

To enable full text search your sever must be installed with this capability.

To enable this capability in the sql server 2005 express edition, you have to download the sql server 2005 express edition advanced. This will install the database server, the sql managment studio and the full text serch.

|||

Thanks for your reply. However I already have the "advanced services" installed but my full text button is still disabled in VS Server Explorer.

If I add the database on a named instance for instance MyBox\SQLExpress then i get an option of enabling the Full Text Search but I dont see any option for a stand-alone database file.

|||

Hi,

I would like to introduce some good video learning resources to you which demonstrates how to set up Full-Text within SQL Server Express.These resources demonstrate how to set up Full-Text search paying particular attention to the nuances of setting it up within SQL Server Express.

http://msdn.microsoft.com/vstudio/express/sql/learning/default.aspx#13

Hope that helps. Thanks.

|||

Thanks a lot for your reply. I found out that full text is not available for "user instances" that is why I am not able to get it to work.

I think I may just have to go with SQL Lite. Does any one know any other database solution which might be better then this? Thanks.

Full Text Search Engine and russian language support...

I try to develope application with full text search in SQl tables by fields, that containing russian text. I had to use "neutral" for language for word breaker.
Is there are russian addon's for MS SQL 2000, that can solve this problem? Would be included in MS SQL 2005 russian language support? If not - how can I solve it by other way?

You may want to post this on the database engine forum (for full-text search) - http://forums.microsoft.com/msdn/ShowForum.aspx?ForumID=93

Full Text Repair

We had a customer request a full text installation after
the application of SP4 and the slammer hotfix. It throws
an error that refers to a re-installation of SP4, which
will not work with any hot fixes applied after the fact.
Does anyone have a different route to follow that
uninstall the hotfix, then reapply the SP4, and then
reapply the hot fix?
Thanks in advance for any information.
Emil
what is the exact error message you are getting?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Emil" <emilcaruthers@.hotmail.com> wrote in message
news:698301c4758b$4e941cd0$a301280a@.phx.gbl...
> We had a customer request a full text installation after
> the application of SP4 and the slammer hotfix. It throws
> an error that refers to a re-installation of SP4, which
> will not work with any hot fixes applied after the fact.
> Does anyone have a different route to follow that
> uninstall the hotfix, then reapply the SP4, and then
> reapply the hot fix?
> Thanks in advance for any information.
> Emil
|||use culture;
SELECT * from FREETEXTTABLE(tblMasterEvents,*,'art');
Get this:
Search on full-text catalog 'culture' for database ID 41, table ID
1381579960 with search condition 'art' failed with unknown result (565f9c4).
The name of the database is not relevant, as all return the error.
Thanks for looking.
Emil Caruthers
"Hilary Cotter" wrote:

> what is the exact error message you are getting?
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Emil" <emilcaruthers@.hotmail.com> wrote in message
> news:698301c4758b$4e941cd0$a301280a@.phx.gbl...
>
>
|||Emil,
Since your customer has already applied SP4, I'm assuming that this is a SQL
Server 7.0 installation as there currently is no publicly released SP4 for
SQL Sever 2000.
For SQL Server 7.0 installations, there is a registry key and value that you
can change in order to successfully re-install SP4 after applying a post-SP4
hotfix. Specifically, registry key called
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL Server\Current Version\
"CSDVersion"
It will contain your hotfix build version number such that 7.00.xxx is the
build number. Record this value and then change it to the SP4 build value of
7.00.1063 and then re-install SP4. If successful, then re-install your
hotfix and confirm that this registry key value has changed back to your
recorded hotfix build number.
BTW, the hex value in the unknown result below is a 'garbage' value as this
is a bug with SQL Server 7.0 FTS and is fixed by applying (or re-applying)
the latest service pack.
Regards,
John
"Emil" <Emil@.discussions.microsoft.com> wrote in message
news:76173F35-121A-4ED7-852F-3328824CEC69@.microsoft.com...
> use culture;
> SELECT * from FREETEXTTABLE(tblMasterEvents,*,'art');
> Get this:
> Search on full-text catalog 'culture' for database ID 41, table ID
> 1381579960 with search condition 'art' failed with unknown result
(565f9c4).[vbcol=seagreen]
>
> The name of the database is not relevant, as all return the error.
> Thanks for looking.
> --
> Emil Caruthers
>
> "Hilary Cotter" wrote:
|||Thank you kindly, and I will try this.
Emil
Emil Caruthers
"John Kane" wrote:

> Emil,
> Since your customer has already applied SP4, I'm assuming that this is a SQL
> Server 7.0 installation as there currently is no publicly released SP4 for
> SQL Sever 2000.
> For SQL Server 7.0 installations, there is a registry key and value that you
> can change in order to successfully re-install SP4 after applying a post-SP4
> hotfix. Specifically, registry key called
> HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MSSQL Server\Current Version\
> "CSDVersion"
> It will contain your hotfix build version number such that 7.00.xxx is the
> build number. Record this value and then change it to the SP4 build value of
> 7.00.1063 and then re-install SP4. If successful, then re-install your
> hotfix and confirm that this registry key value has changed back to your
> recorded hotfix build number.
> BTW, the hex value in the unknown result below is a 'garbage' value as this
> is a bug with SQL Server 7.0 FTS and is fixed by applying (or re-applying)
> the latest service pack.
> Regards,
> John
>
> "Emil" <Emil@.discussions.microsoft.com> wrote in message
> news:76173F35-121A-4ED7-852F-3328824CEC69@.microsoft.com...
> (565f9c4).
>
>

Full text query contained only ignored words

Hello All,
Please explain this Fulltext behavior to me.
We have an asp application that is using sql that returns the above error
when you search for the word can't. If you search for "don't" or "Doesn't"
or pretty much anything else with an apostrophe, results are returned.
The same thing happens when you search for a decimal number. If I search
for 4.0, I get the above error. If I search for 4.00.0 I will get results.
Thanks in advance!
Jake
"GitarJake" <spamaintme@.oz.net> wrote in message
news:C0vRe.101151$Ep.81169@.lakeread02...
> Hello All,
> Please explain this Fulltext behavior to me.
> We have an asp application that is using sql that returns the above error
> when you search for the word can't. If you search for "don't" or
> "Doesn't" or pretty much anything else with an apostrophe, results are
> returned.
> The same thing happens when you search for a decimal number. If I search
> for 4.0, I get the above error. If I search for 4.00.0 I will get
> results.
Check the noise.X file in your FTDATA\Config folder (where X is the
extension related to your word breaker language - eng for English, enu for
US English, etc).
4 is in the list. I'm guessing that when 4.0 is passed FTS treats it a
number and so finds it in the noise word file, whereas when 4.00.0 is used
it's obviously not a number and so does a search for it as is.
Can is a word in the noise word list too. I'm guessing FTS splits can't into
can and not internally, and errors because can is in the list.
What you could do is empty the noise word file - leave only a single line in
it with a single space on it. Then run a full population. This has the
effect of not erroring on any words (although some characters that are used
as word breakers will still throw the noise words error, you'll have to
filter these out in your query builder).
Dan
|||You might want to review the following
http://www.indexserverfaq.com/noise.htm
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
"GitarJake" <spamaintme@.oz.net> wrote in message
news:C0vRe.101151$Ep.81169@.lakeread02...
> Hello All,
> Please explain this Fulltext behavior to me.
> We have an asp application that is using sql that returns the above error
> when you search for the word can't. If you search for "don't" or
"Doesn't"
> or pretty much anything else with an apostrophe, results are returned.
> The same thing happens when you search for a decimal number. If I search
> for 4.0, I get the above error. If I search for 4.00.0 I will get
results.
>
> Thanks in advance!
> Jake
>
|||Hi Daniel,
Thanks for responding.
I have removed "can" from both the fulltext noise.enu word list in
ftdata\config; and the Indexing noise.enu. The wordbreaker in FTI is
neutral. I completely deleted the FT catalogs and manually recreated and
repopulated them. The results were the same.
Then I deleted the contents FTI's noise.enu except for a space and now, the
search simply does not return results. It seems like I can search for the
word can't on any other search engine but ours. Below is the query we are
passing using just the word *can't*. Can you tell if there is anything
within the query that might be better able to produce results?
select ARTICLE_ID, ARTICLE_STITLE, ARTICLE_LTITLE, ARTICLE_SUMMARY
,ARTICLE_STATUS, ARTICLE_APPROVED, DEPART_REF, USER_REF_OWNER,
USER_REF_EDITOR, ARTICLE_REVIEW_DATE, ARTICLE_EXPIRE, RANK,
PUBLIC_DIVISION_REF From ARTICLES as A, containstable(ARTICLES, *, '
"can''t" ') as AST where (LANGUAGE_REF= 1 and ARTICLE_DISABLE_SEARCH <> 1 )
and AST.[KEY] = A.ARTICLE_ID order by RANK desc
MIS query: "can''t"
TIA
Jake
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:%23BYyg8vrFHA.3096@.TK2MSFTNGP15.phx.gbl...
> "GitarJake" <spamaintme@.oz.net> wrote in message
> news:C0vRe.101151$Ep.81169@.lakeread02...
> Check the noise.X file in your FTDATA\Config folder (where X is the
> extension related to your word breaker language - eng for English, enu for
> US English, etc).
> 4 is in the list. I'm guessing that when 4.0 is passed FTS treats it a
> number and so finds it in the noise word file, whereas when 4.00.0 is used
> it's obviously not a number and so does a search for it as is.
> Can is a word in the noise word list too. I'm guessing FTS splits can't
> into can and not internally, and errors because can is in the list.
> What you could do is empty the noise word file - leave only a single line
> in it with a single space on it. Then run a full population. This has the
> effect of not erroring on any words (although some characters that are
> used as word breakers will still throw the noise words error, you'll have
> to filter these out in your query builder).
> Dan
>
|||To test this I added "Can't you see?" to the end of a book title in the pubs
database. I then created a new catalog on the title column, ran a full
population, and then ran this:
select * from titles where contains(title,'can''t')
and up popped the row I'd added the phrase to. I then tried this:
select a.* from titles a ,containstable(titles,*,'can''t') b where
a.title_id = b.[key]
select a.* from titles a ,containstable(titles,*,'"can''t"') b where
a.title_id = b.[key]
and again, I get the same row back from both versions. So it's not a problem
with the apostrophe, nor an issue with the double quotes.
The only other thing in your query that will prevent the row from appearing
will be if ARTICLE_DISABLE_SEARCH = 1 or LANGUAGE_REF <> 1. Have you tried
running the query with these clauses removed just in case?
Dan
GitarJake wrote on Sun, 4 Sep 2005 12:33:07 -0400:
[vbcol=seagreen]
> Hi Daniel,
> Thanks for responding.
> I have removed "can" from both the fulltext noise.enu word list in
> ftdata\config; and the Indexing noise.enu. The wordbreaker in FTI is
> neutral. I completely deleted the FT catalogs and manually recreated and
> repopulated them. The results were the same.
> Then I deleted the contents FTI's noise.enu except for a space and now,
> the search simply does not return results. It seems like I can search for
> the word can't on any other search engine but ours. Below is the query we
> are passing using just the word *can't*. Can you tell if there is
> anything within the query that might be better able to produce results?
> select ARTICLE_ID, ARTICLE_STITLE, ARTICLE_LTITLE, ARTICLE_SUMMARY
> ,ARTICLE_STATUS, ARTICLE_APPROVED, DEPART_REF, USER_REF_OWNER,
> USER_REF_EDITOR, ARTICLE_REVIEW_DATE, ARTICLE_EXPIRE, RANK,
> PUBLIC_DIVISION_REF From ARTICLES as A, containstable(ARTICLES, *, '
> "can''t" ') as AST where (LANGUAGE_REF= 1 and ARTICLE_DISABLE_SEARCH <>
> 1 ) and AST.[KEY] = A.ARTICLE_ID order by RANK desc
> MIS query: "can''t"
> TIA
> Jake
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message news:%23BYyg8vrFHA.3096@.TK2MSFTNGP15.phx.gbl...

Full Text index catalog population error

Does anyone know what the services are involved during the SQL full
text index catalog population?
Some of my application event log errors are "Error: 80070426 - The
service has not been started.".
I know that my SQL server is running and MSsearch is running. I don't
know any other services needed for this operation.
Thank you in advance for any help!
Its MSSearch, SQL Server, SQL Server Agent (if you are scheduling your
populations).
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
"discover" <hongc@.lanproinc.com> wrote in message
news:1163533752.261545.225040@.f16g2000cwb.googlegr oups.com...
> Does anyone know what the services are involved during the SQL full
> text index catalog population?
> Some of my application event log errors are "Error: 80070426 - The
> service has not been started.".
> I know that my SQL server is running and MSsearch is running. I don't
> know any other services needed for this operation.
> Thank you in advance for any help!
>

Full Tex Search question...

I am trying to get a Full Text Search function together for a new application, but don't know the Full Text commands too well.

Does anyone happen to know if it is possible to query a Full Text Indexed table without using dynamic SQL? Here is the example

create table test1
(col1 varchar(10),
col2 varchar(100))
-- You have to do the full text indexing yourself here...
insert into test1
select 'good row', 'here is some talk about supplies'
union
select 'good row', 'this is what we supplied'
union
select 'good row', 'supply and demand. Live it. Love it.'
union
select 'bad row', 'This is a row that should be ignored'
go
-- Don't forget to update the full text index...
declare @.word varchar(20)
declare @.query varchar(500)

set @.word = 'supply'

set @.query = 'select * from test1 where contains (Col2, ''formsof (inflectional, "' + @.word + '") or "*' + @.word + '*"'')'

print @.query

exec (@.query)

I can do the simple queries with

where contains (col2, "@.word")

But the FORMSOF part breaks down, and will not accept variables from me. Am I doomed to Dynamic SQL? Has anyone found better ways of doing this? Thanks in advance.This works for me - try it.

drop table ftable
go
create table ftable(id int identity primary key, code varchar(25))
go
insert ftable(code) values('table')
insert ftable(code) values('tables')
insert ftable(code) values('tables table')
insert ftable(code) values('tables able')
go
-- do what it needs to do for creating a full-text index
declare @.word varchar(20)
declare @.query varchar(500)
set @.word = 'table'
set @.query = 'select * from ftable where contains (*, ''formsof (inflectional, "' + @.word + '") or "*' + @.word + '*"'')'
print @.query
exec (@.query)|||Thanks, Snail. I have gotten about that far, too. I was hoping for a non-Dynamic SQL way out. Unfortunately, I do not think you can supply paramters directly to a FORMSOF clause.|||OK, Now I am ticked at Microsoft.

I put in the stored procedure with the Dynamic SQL, but I forgot one important "feature" of dynamic SQL. The users need permissions on the tables AND the stored proc in order to run it. No problem, I say. I can protect the tables with a view. After all, that IS what Microsoft says in ALL of their documentation, right?

Turns out, you can not do a Full Text Query on a view of a Full Text Indexed column..

Server: Msg 7601, Level 16, State 2, Line 1
Cannot use a CONTAINS or FREETEXT predicate on table 'DatabaseSearchResults' because it is not full-text indexed.

So, has Microsoft turned their backs on "Best Practices"?

Alternatively, how do the rest of you deal with Full Text Indexing? Any pointers?