Hi.
I have an Sql server 2005 beta2 problem with the full-text index
functionality.
I have a table with a full text index. In the definition of the full-text
index, I have specified the neutral language. The problem domain specifies
that texts can be in arbitrary languages.
create fulltext index on mytable
(
myfield language 0x0
)
..
Then I insert a record containing the word "and" (which in Danish means
duck). When searching for that record
select * from mytable where contains(myfield, 'and')
nothing is returned, and I get the message,"Informational: The full-text
search condition contained noise word(s)."
Apperantly, it uses the noise-word list for english, when I insert records
and search on that field.
I thought that when I specify the language as 0x0, the full-text index
should be language neutral and that would mean that it doesn't filter out
noise words(because those are language specific). Am I mistaken, or is it an
Sql Server 2005 beta2 bug?
Thanks in advance,
Peter Striman> I have an Sql server 2005 beta2 problem with the full-text index
> functionality.
Please post to the beta newsgroups.
http://www.aspfaq.com/sql2005/show.asp?id=1|||Hello,
For questions of SQL server 2005, please post at the following newsgroup:
Welcome to the Microsoft SQL Server 2005 Community Technology (CTP)
Newsgroups
<http://communities.microsoft.com/ne...sqlserver2005&s
lcid=us>
Thanks for cooperation.
Sophie Guo
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
========================================
=============
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.sql
Showing posts with label noise. Show all posts
Showing posts with label noise. Show all posts
Wednesday, March 21, 2012
Wednesday, March 7, 2012
noise words, @@ERROR, and stop and resume indexing
Hi,
I'm FTS newbee, and have some questions
1) check noise words inside stored procedure
2) @.@.Error fails
3) The best way to stop and restart indexing
1)
Just found out that this error
Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
A clause of the query contained only ignored words.
triggered when executing
SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
can be solved by changing the language specific file with noise words ...
but that's not really an option in our server configuration. Now I wondered
is there a way to query via TSQL a list of the noisewords
... so I can exclude them before parsing the query? I could import the
noisewordfile into a tabel, but isn't there an easier way?
2)
Concerning the error above I dedected I can not catch the error in my stored
procedure with the instruction IF @.@.error ... So is it true that I can only
handle this error in my client software that calls the stored procedure?
3)
My full text index works fine (SQLSERVER2000/WIN2000). It requires to
update indexes immediately, so I use a timestamp field to enable this. Now,
I've got a stored procedures which nearly daily inserts about 10.000 rows.
When doing this while full text indexing is active, all users start
complaining about performance. In order to work around this problem I
tried doing the following ...
Create myStoredProcedure
-- begin of stored procedure
exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
-- insert 10.000 rows
-- end of stored procedure
exec sp_fulltext_table 'adsfull', 'start_change_tracking'
exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
Now, it seems this doesn't work. SQL Server keeps tracking changes and
updating indexes. Also if I cut away the stop instructions and paste them
into query analyzer before starting the stored procedure.
So, if I check the status via select fulltextcatalogproperty('FTADS',
'Populatestatus') ... it returns value 6 (incremental in progress) instead
of 0 (idle) while executing the stored procedure.
The only way I can resolve this issue is to stop the indexing via the
enterprise manager and to restart after the stored procedure is executed.
Is there a better/other way to stop and restart indexing instead of the 4
lines I used above?
Any help appreciated.
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,
1) You could do something like this:
set nocount on
GO
Create table Noise
(noiseword varchar(100))
GO
insert into noise
exec master.dbo.xp_Cmdshell 'type c:\"Program Files\Microsoft SQL
Server"\mssql\ftdata\sqlserver\config\noise.enu'
GO
delete from noise where NoiseWord is null
GO
declare @.string varchar(100)
select @.string=noiseword from noise where charindex(' ',noiseword)>0
while charindex(' ',@.string)>0
begin
insert into noise (noiseword) values (left(@.string,charindex(' ',@.string)))
select @.string=substring(@.string,charindex(' ',@.string)+1,100)
end
GO
delete from noise where len(noiseword)-len(replace(noiseword,' ',''))>0
GO
select * from noise order by 1
For use US English.
2) no there is no good way of doing this. I normally check at the client,
for instance errors messages will be returned via ado saying MSSearch
service not runing,
3) Whenever you kick of change tracking a full or incremental population is
started. I can't think of a way to get around this right now. I'd try to
investigate exactly why you are experiencing locking on your table with the
insert proc. Perhaps you have having data page movement associated with
cluster index reorgs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||BTW - you might want to check out this kb article. It claims some of the
errors raised by incorrect searches do generate a correct @.@.error value.
http://support.microsoft.com/default...b;en-us;287167
My experience is still that it does not capture all errors.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||Perre,
Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
question in this newsgroup, and there are many ways of accomplishing this,
but the best one (IMHO) is one that I posted to this newsgroup back on March
21, 2003 as recorded in Google Groups via the following shortened url:
http://tinyurl.com/69kyy. Specifically:
Create Table noise_words
(
Noiseword varchar(50) Not Null
)
Go
Alter Table noise_words Add Constraint PK_noise_words PrimarXy Key Clustered
(
Noiseword
)
Go
Then you can use BULK INSERT, BCP or DTS to copy the contentXs of the file
into the database. Before you copy in the language-specific Xnoise word
file, you will need to make some changes to the initial file from Xthe end
of the file as the noise word files contain a list of "white space"X single
letters and characters at the end of the file, for example, from noiXse.enu:
a b c d e f g h i j k l m n o p q r s t u v w x y z
BULK INSERT or BCP will fail or think this is one big stringX (no CR/LF), so
you will need to separate out the row above such that each lXetter takes up
its own row in the file. Open the language specific noise woXrd file in a
text editor (notepad.exe) and change the above list to:
a
b
c
d
e
f
and so on. Be sure to eliminate any leading or trailing spacXes for each
character. Once that's done, you can use BULK INSERT, BCP orX DTS to copy
the data from the noise file to the noise_words table. Once the data is
imported correctly, you can use a standard XSQL statement such as:
select count(*) from noise_words where Noiseword = "between"
to use in a string parser function to remove the noise wordsX in your users
input string and then pass this edited string to a SQL ServeXr Full-Text
Search query.
In regards to FTS setting @.@.error, see KB article: Q287167 "FIX: Some
Full-Text Search Failures Do Not Set @.@.ERROR" at
http://support.microsoft.com/default...;en-us;Q287167
In regards to stopping and then restarting FT Indexing, try the following:
EXEC sp_fulltext_table 'adsfull', 'stop_background_updateindex'
EXEC sp_fulltext_catalog 'adsfull', 'stop'
-- insert 10,000 rows
exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
go
If that does not work, then try stopping the MSSearch service via
xp_cmdshell, inserting your 10,000 rows and then re-starting the MSSearch
service, for example:
-- Stop the MSSearch Service
exec master..xp_cmdshell 'net stop "Microsoft Search"'
go
-- Start the MSSearch Service
exec master..xp_cmdshell 'net start "Microsoft Search"'
go
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||Hilary, John,
Clarifying solutions,
Thanks a lot.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OeHOZmcBFHA.2572@.tk2msftngp13.phx.gbl...
> Perre,
> Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
> question in this newsgroup, and there are many ways of accomplishing this,
> but the best one (IMHO) is one that I posted to this newsgroup back on
March
> 21, 2003 as recorded in Google Groups via the following shortened url:
> http://tinyurl.com/69kyy. Specifically:
> Create Table noise_words
> (
> Noiseword varchar(50) Not Null
> )
> Go
> Alter Table noise_words Add Constraint PK_noise_words PrimarXy Key
Clustered
> (
> Noiseword
> )
> Go
> Then you can use BULK INSERT, BCP or DTS to copy the contentXs of the file
> into the database. Before you copy in the language-specific Xnoise word
> file, you will need to make some changes to the initial file from Xthe
end
> of the file as the noise word files contain a list of "white space"X
single
> letters and characters at the end of the file, for example, from
noiXse.enu:
> a b c d e f g h i j k l m n o p q r s t u v w x y z
> BULK INSERT or BCP will fail or think this is one big stringX (no CR/LF),
so
> you will need to separate out the row above such that each lXetter takes
up
> its own row in the file. Open the language specific noise woXrd file in a
> text editor (notepad.exe) and change the above list to:
> a
> b
> c
> d
> e
> f
> and so on. Be sure to eliminate any leading or trailing spacXes for each
> character. Once that's done, you can use BULK INSERT, BCP orX DTS to copy
> the data from the noise file to the noise_words table. Once the data is
> imported correctly, you can use a standard XSQL statement such as:
> select count(*) from noise_words where Noiseword = "between"
> to use in a string parser function to remove the noise wordsX in your
users[vbcol=seagreen]
> input string and then pass this edited string to a SQL ServeXr Full-Text
> Search query.
> In regards to FTS setting @.@.error, see KB article: Q287167 "FIX: Some
> Full-Text Search Failures Do Not Set @.@.ERROR" at
> http://support.microsoft.com/default...;en-us;Q287167
> In regards to stopping and then restarting FT Indexing, try the following:
> EXEC sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> EXEC sp_fulltext_catalog 'adsfull', 'stop'
> --
> -- insert 10,000 rows
> --
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> go
> If that does not work, then try stopping the MSSearch service via
> xp_cmdshell, inserting your 10,000 rows and then re-starting the MSSearch
> service, for example:
> -- Stop the MSSearch Service
> exec master..xp_cmdshell 'net stop "Microsoft Search"'
> go
> -- Start the MSSearch Service
> exec master..xp_cmdshell 'net start "Microsoft Search"'
> go
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
> news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
...[vbcol=seagreen]
> wondered
the[vbcol=seagreen]
> stored
> only
> procedure?
> Now,
rows.[vbcol=seagreen]
them[vbcol=seagreen]
instead[vbcol=seagreen]
executed.[vbcol=seagreen]
4
>
|||Hilary,
Re point 2, I am running multiple full-text searches
within a cursor (Original posting
in .sqlserver.programming, 4th Feb , Subject: "On Error
Resume Next" in SQL Server) and I want the procedure to
run through the entire cursor whatever but if there is an
ignored-words error the whole thing stops and does not
reach the end of the cursor so it's not so much that I
want to handle an error as to ignore it but I can't seem
to do this either. Any ideas?
>--Original Message--
>1) You could do something like this:
>set nocount on
>GO
>Create table Noise
>(noiseword varchar(100))
>GO
>insert into noise
>exec master.dbo.xp_Cmdshell 'type c:\"Program
Files\Microsoft SQL
>Server"\mssql\ftdata\sqlserver\config\noise.enu '
>GO
>delete from noise where NoiseWord is null
>GO
>declare @.string varchar(100)
>select @.string=noiseword from noise where charindex
(' ',noiseword)>0
>while charindex(' ',@.string)>0
>begin
>insert into noise (noiseword) values (left
(@.string,charindex(' ',@.string)))
>select @.string=substring(@.string,charindex(' ',@.string)
+1,100)
>end
>GO
>delete from noise where len(noiseword)-len(replace
(noiseword,' ',''))>0
>GO
>select * from noise order by 1
>For use US English.
>2) no there is no good way of doing this. I normally
check at the client,
>for instance errors messages will be returned via ado
saying MSSearch
>service not runing,
>3) Whenever you kick of change tracking a full or
incremental population is
>started. I can't think of a way to get around this right
now. I'd try to
>investigate exactly why you are experiencing locking on
your table with the
>insert proc. Perhaps you have having data page movement
associated with
>cluster index reorgs.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in
message[vbcol=seagreen]
>news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
Line 2[vbcol=seagreen]
fads_adid[vbcol=seagreen]
with noise words ...[vbcol=seagreen]
configuration. Now I[vbcol=seagreen]
>wondered
noisewords[vbcol=seagreen]
I could import the[vbcol=seagreen]
way?[vbcol=seagreen]
the error in my[vbcol=seagreen]
>stored
true that I can[vbcol=seagreen]
>only
the stored[vbcol=seagreen]
>procedure?
It requires to[vbcol=seagreen]
to enable this.[vbcol=seagreen]
>Now,
about 10.000 rows.[vbcol=seagreen]
users start[vbcol=seagreen]
this problem I[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_background_updateindex'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_background_updateindex'[vbcol=seagreen]
tracking changes and[vbcol=seagreen]
instructions and paste them[vbcol=seagreen]
procedure.[vbcol=seagreen]
fulltextcatalogproperty('FTADS',[vbcol=seagreen]
in progress) instead[vbcol=seagreen]
indexing via the[vbcol=seagreen]
procedure is executed.[vbcol=seagreen]
indexing instead of the 4
>
>.
>
|||Andy,
Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
question in this newsgroup, and there are many ways of accomplishing this,
but the best one (IMHO) is one that I posted to this newsgroup back on March
21, 2003 as recorded in Google Groups via the following shortened url:
http://tinyurl.com/69kyy.
In regards, to point 2, have you read KB article: Q287167 "FIX: Some
Full-Text Search Failures Do Not Set @.@.ERROR" at
http://support.microsoft.com/default...;en-us;Q287167 ?
Could you re-post your SQL script with the cursor code? Depending upon what
you are trying to do, the use of cursors, may not be the best approach.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Re point 2, I am running multiple full-text searches
> within a cursor (Original posting
> in .sqlserver.programming, 4th Feb , Subject: "On Error
> Resume Next" in SQL Server) and I want the procedure to
> run through the entire cursor whatever but if there is an
> ignored-words error the whole thing stops and does not
> reach the end of the cursor so it's not so much that I
> want to handle an error as to ignore it but I can't seem
> to do this either. Any ideas?
> Files\Microsoft SQL
> (' ',noiseword)>0
> (@.string,charindex(' ',@.string)))
> +1,100)
> (noiseword,' ',''))>0
> check at the client,
> saying MSSearch
> incremental population is
> now. I'd try to
> your table with the
> associated with
> message
> Line 2
> fads_adid
> with noise words ...
> configuration. Now I
> noisewords
> I could import the
> way?
> the error in my
> true that I can
> the stored
> It requires to
> to enable this.
> about 10.000 rows.
> users start
> this problem I
> sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> sp_fulltext_table 'adsfull', 'start_change_tracking'
> sp_fulltext_table 'adsfull', 'start_background_updateindex'
> tracking changes and
> instructions and paste them
> procedure.
> fulltextcatalogproperty('FTADS',
> in progress) instead
> indexing via the
> procedure is executed.
> indexing instead of the 4
|||there doesn't seem to be a clean way to handle this other than to extract
these words at the beginning before sending them to the cursor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Re point 2, I am running multiple full-text searches
> within a cursor (Original posting
> in .sqlserver.programming, 4th Feb , Subject: "On Error
> Resume Next" in SQL Server) and I want the procedure to
> run through the entire cursor whatever but if there is an
> ignored-words error the whole thing stops and does not
> reach the end of the cursor so it's not so much that I
> want to handle an error as to ignore it but I can't seem
> to do this either. Any ideas?
> Files\Microsoft SQL
> (' ',noiseword)>0
> (@.string,charindex(' ',@.string)))
> +1,100)
> (noiseword,' ',''))>0
> check at the client,
> saying MSSearch
> incremental population is
> now. I'd try to
> your table with the
> associated with
> message
> Line 2
> fads_adid
> with noise words ...
> configuration. Now I
> noisewords
> I could import the
> way?
> the error in my
> true that I can
> the stored
> It requires to
> to enable this.
> about 10.000 rows.
> users start
> this problem I
> sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> sp_fulltext_table 'adsfull', 'start_change_tracking'
> sp_fulltext_table 'adsfull', 'start_background_updateindex'
> tracking changes and
> instructions and paste them
> procedure.
> fulltextcatalogproperty('FTADS',
> in progress) instead
> indexing via the
> procedure is executed.
> indexing instead of the 4
|||Andy,
FYI, on KB article Q287167 as it is a "FIX" kb article, and this issue with
@.@.error was fixed in SQL Server 2000 SP1.
What is the full output of -- SELECT @.@.version -- on your server where you
are executing the below example code?
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you reckon?
I reckon that if you enclose your @.SearchText in double quotes, i.e., a
phrase search, the noise word would be truly ignored. See SQL Server 2000
BOL title "Full-text Search Recommendations" - "Consider rewriting this
query to a phrase-based query, removing the noise word, or options offered
in Knowledge Base article Q246800, "INF: Correctly Parsing Quotation Marks
in FTS Queries". For example using the pubs database table pr_info:
select pub_id, pr_info from pub_info where CONTAINS(pr_info, '"between AND
books"')
In the above query the noise word "between" is truly ignored or you can use
FREETEXT if the search string cannot be fully placed within double quotes,
for example:
select pub_id, pr_info from pub_info where freetext(pr_info, '"between" AND
"books"')
Finally, you can remove all noise words from your language-specific noise
word file. These files are located under \FTDATA\SQLServer\Config\noise.*
where * represents the language - enu = US_English. You will need to stop
the MSSearch service, edit noise.enu (assuming US_English) with notepad.exe
and remove the words and save the file, restart the MSSearch service and run
a Full Population on all of your FT Catalogs. I'd recommend that you remove
all noise words that you may want to search on, but not empty the entire
file and at least leave a single space in the file.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e4tWiVTDFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Andy,
> Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
> question in this newsgroup, and there are many ways of accomplishing this,
> but the best one (IMHO) is one that I posted to this newsgroup back on
March
> 21, 2003 as recorded in Google Groups via the following shortened url:
> http://tinyurl.com/69kyy.
> In regards, to point 2, have you read KB article: Q287167 "FIX: Some
> Full-Text Search Failures Do Not Set @.@.ERROR" at
> http://support.microsoft.com/default...;en-us;Q287167 ?
> Could you re-post your SQL script with the cursor code? Depending upon
what
> you are trying to do, the use of cursors, may not be the best approach.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...
>
|||John,
I didn't make it clear but the UDF encloses everything in
double quotes unless it resolves to nothing in which case
an empty string is returned and the search is not
performed. It's not the noise words I have a problem with,
I just wanted to be able to skip over the ignored-words
error and carry on. Obviously the best solution would be
to make the UDF infallible but as good as I think I've got
it something else comes along e.g. one user had a search
text of a single DEL (ASCII 127) character and that broke
it. Now without going through the entire database and
reformatting all the users' search terms the only way of
proceding is to either account for absolutely every
possible ASCII/Unicode character combination or to simply
ignore examples such as the above which is what I'm trying
to do. As far as the @.@.version it's SQL 2000 SP3.
I may just have to return the initial results and do each
full-text search from the client code but i'm just a bit
miffed of having to make all those extra DB calls :-(
>--Original Message--
>Andy,
>FYI, on KB article Q287167 as it is a "FIX" kb article,
and this issue with
>@.@.error was fixed in SQL Server 2000 SP1.
>What is the full output of -- SELECT @.@.version -- on your
server where you
>are executing the below example code?
>DECLARE TestCursor CURSOR FOR /*WHATEVER*/
>OPEN TestCursor
>WHILE (1 = 1)
>BEGIN
>FETCH NEXT FROM TestCursor INTO @.SearchText
>SET @.SearchText = FormatSearchText(@.SearchText)
>/*
>This is a UDF with output as per KB article as mentioned.
>If anything goes wrong this returns '' as I am not
>bothered if it cannot resolve input but note: This can
>still output junk that can break the CONTAINS search.
>*/
>INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
>WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
>/*
>When run in QA, if this query causes an ignored-word error
>the SP stops dead. I need it to carry on to the end of the
>cursor.
>*/
>END, CLOSE, DEALLOCATE etc.
>SELECT * FROM #TEMPTABLE /* Output of entire cursor */
>That's pretty much what I'm trying to achieve. What do
you reckon?
>I reckon that if you enclose your @.SearchText in double
quotes, i.e., a
>phrase search, the noise word would be truly ignored. See
SQL Server 2000
>BOL title "Full-text Search Recommendations" - "Consider
rewriting this
>query to a phrase-based query, removing the noise word,
or options offered
>in Knowledge Base article Q246800, "INF: Correctly
Parsing Quotation Marks
>in FTS Queries". For example using the pubs database
table pr_info:
>select pub_id, pr_info from pub_info where CONTAINS
(pr_info, '"between AND
>books"')
>In the above query the noise word "between" is truly
ignored or you can use
>FREETEXT if the search string cannot be fully placed
within double quotes,
>for example:
>select pub_id, pr_info from pub_info where freetext
(pr_info, '"between" AND
>"books"')
>Finally, you can remove all noise words from your
language-specific noise
>word file. These files are located under
\FTDATA\SQLServer\Config\noise.*
>where * represents the language - enu = US_English. You
will need to stop
>the MSSearch service, edit noise.enu (assuming
US_English) with notepad.exe
>and remove the words and save the file, restart the
MSSearch service and run
>a Full Population on all of your FT Catalogs. I'd
recommend that you remove
>all noise words that you may want to search on, but not
empty the entire[vbcol=seagreen]
>file and at least leave a single space in the file.
>Regards,
>John
>--
>SQL Full Text Search Blog
>http://spaces.msn.com/members/jtkane/
>
>"John Kane" <jt-kane@.comcast.net> wrote in message
>news:e4tWiVTDFHA.2180@.TK2MSFTNGP12.phx.gbl...
frequently asked[vbcol=seagreen]
accomplishing this,[vbcol=seagreen]
newsgroup back on[vbcol=seagreen]
>March
shortened url:[vbcol=seagreen]
Q287167 "FIX: Some[vbcol=seagreen]
us;Q287167 ?[vbcol=seagreen]
Depending upon[vbcol=seagreen]
>what
the best approach.[vbcol=seagreen]
wrote in message[vbcol=seagreen]
Error[vbcol=seagreen]
to[vbcol=seagreen]
is an[vbcol=seagreen]
seem[vbcol=seagreen]
(' ',@.string)[vbcol=seagreen]
right[vbcol=seagreen]
on[vbcol=seagreen]
movement[vbcol=seagreen]
wrote in[vbcol=seagreen]
usp_ft,[vbcol=seagreen]
ads_adid =[vbcol=seagreen]
AND "astra"'))))[vbcol=seagreen]
file[vbcol=seagreen]
query?[vbcol=seagreen]
easier[vbcol=seagreen]
catch[vbcol=seagreen]
is it[vbcol=seagreen]
calls[vbcol=seagreen]
(SQLSERVER2000/WIN2000).[vbcol=seagreen]
field[vbcol=seagreen]
inserts[vbcol=seagreen]
active, all[vbcol=seagreen]
around[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_background_updateindex'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_background_updateindex'[vbcol=seagreen]
(incremental[vbcol=seagreen]
the
>
>.
>
I'm FTS newbee, and have some questions
1) check noise words inside stored procedure
2) @.@.Error fails
3) The best way to stop and restart indexing
1)
Just found out that this error
Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
A clause of the query contained only ignored words.
triggered when executing
SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
can be solved by changing the language specific file with noise words ...
but that's not really an option in our server configuration. Now I wondered
is there a way to query via TSQL a list of the noisewords
... so I can exclude them before parsing the query? I could import the
noisewordfile into a tabel, but isn't there an easier way?
2)
Concerning the error above I dedected I can not catch the error in my stored
procedure with the instruction IF @.@.error ... So is it true that I can only
handle this error in my client software that calls the stored procedure?
3)
My full text index works fine (SQLSERVER2000/WIN2000). It requires to
update indexes immediately, so I use a timestamp field to enable this. Now,
I've got a stored procedures which nearly daily inserts about 10.000 rows.
When doing this while full text indexing is active, all users start
complaining about performance. In order to work around this problem I
tried doing the following ...
Create myStoredProcedure
-- begin of stored procedure
exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
-- insert 10.000 rows
-- end of stored procedure
exec sp_fulltext_table 'adsfull', 'start_change_tracking'
exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
Now, it seems this doesn't work. SQL Server keeps tracking changes and
updating indexes. Also if I cut away the stop instructions and paste them
into query analyzer before starting the stored procedure.
So, if I check the status via select fulltextcatalogproperty('FTADS',
'Populatestatus') ... it returns value 6 (incremental in progress) instead
of 0 (idle) while executing the stored procedure.
The only way I can resolve this issue is to stop the indexing via the
enterprise manager and to restart after the stored procedure is executed.
Is there a better/other way to stop and restart indexing instead of the 4
lines I used above?
Any help appreciated.
Kind regards,
Perre Van Wilrijk,
Remove capitals to get my real email address,
1) You could do something like this:
set nocount on
GO
Create table Noise
(noiseword varchar(100))
GO
insert into noise
exec master.dbo.xp_Cmdshell 'type c:\"Program Files\Microsoft SQL
Server"\mssql\ftdata\sqlserver\config\noise.enu'
GO
delete from noise where NoiseWord is null
GO
declare @.string varchar(100)
select @.string=noiseword from noise where charindex(' ',noiseword)>0
while charindex(' ',@.string)>0
begin
insert into noise (noiseword) values (left(@.string,charindex(' ',@.string)))
select @.string=substring(@.string,charindex(' ',@.string)+1,100)
end
GO
delete from noise where len(noiseword)-len(replace(noiseword,' ',''))>0
GO
select * from noise order by 1
For use US English.
2) no there is no good way of doing this. I normally check at the client,
for instance errors messages will be returned via ado saying MSSearch
service not runing,
3) Whenever you kick of change tracking a full or incremental population is
started. I can't think of a way to get around this right now. I'd try to
investigate exactly why you are experiencing locking on your table with the
insert proc. Perhaps you have having data page movement associated with
cluster index reorgs.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||BTW - you might want to check out this kb article. It claims some of the
errors raised by incorrect searches do generate a correct @.@.error value.
http://support.microsoft.com/default...b;en-us;287167
My experience is still that it does not capture all errors.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||Perre,
Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
question in this newsgroup, and there are many ways of accomplishing this,
but the best one (IMHO) is one that I posted to this newsgroup back on March
21, 2003 as recorded in Google Groups via the following shortened url:
http://tinyurl.com/69kyy. Specifically:
Create Table noise_words
(
Noiseword varchar(50) Not Null
)
Go
Alter Table noise_words Add Constraint PK_noise_words PrimarXy Key Clustered
(
Noiseword
)
Go
Then you can use BULK INSERT, BCP or DTS to copy the contentXs of the file
into the database. Before you copy in the language-specific Xnoise word
file, you will need to make some changes to the initial file from Xthe end
of the file as the noise word files contain a list of "white space"X single
letters and characters at the end of the file, for example, from noiXse.enu:
a b c d e f g h i j k l m n o p q r s t u v w x y z
BULK INSERT or BCP will fail or think this is one big stringX (no CR/LF), so
you will need to separate out the row above such that each lXetter takes up
its own row in the file. Open the language specific noise woXrd file in a
text editor (notepad.exe) and change the above list to:
a
b
c
d
e
f
and so on. Be sure to eliminate any leading or trailing spacXes for each
character. Once that's done, you can use BULK INSERT, BCP orX DTS to copy
the data from the noise file to the noise_words table. Once the data is
imported correctly, you can use a standard XSQL statement such as:
select count(*) from noise_words where Noiseword = "between"
to use in a string parser function to remove the noise wordsX in your users
input string and then pass this edited string to a SQL ServeXr Full-Text
Search query.
In regards to FTS setting @.@.error, see KB article: Q287167 "FIX: Some
Full-Text Search Failures Do Not Set @.@.ERROR" at
http://support.microsoft.com/default...;en-us;Q287167
In regards to stopping and then restarting FT Indexing, try the following:
EXEC sp_fulltext_table 'adsfull', 'stop_background_updateindex'
EXEC sp_fulltext_catalog 'adsfull', 'stop'
-- insert 10,000 rows
exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
go
If that does not work, then try stopping the MSSearch service via
xp_cmdshell, inserting your 10,000 rows and then re-starting the MSSearch
service, for example:
-- Stop the MSSearch Service
exec master..xp_cmdshell 'net stop "Microsoft Search"'
go
-- Start the MSSearch Service
exec master..xp_cmdshell 'net start "Microsoft Search"'
go
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
> Hi,
> I'm FTS newbee, and have some questions
> 1) check noise words inside stored procedure
> 2) @.@.Error fails
> 3) The best way to stop and restart indexing
> 1)
> Just found out that this error
> Server: Msg 7619, Level 16, State 1, Procedure usp_ft, Line 2
> A clause of the query contained only ignored words.
> triggered when executing
> SELECT TOP 1000 * FROM ADS JOIN ADSFULL ON ads_adid = fads_adid
> WHERE (ADS_ADID IN (SELECT FADS_ADID FROM ADSFULL
> WHERE (CONTAINS(*, '"opel" AND "and" AND "astra"'))))
> can be solved by changing the language specific file with noise words ...
> but that's not really an option in our server configuration. Now I
wondered
> is there a way to query via TSQL a list of the noisewords
> ... so I can exclude them before parsing the query? I could import the
> noisewordfile into a tabel, but isn't there an easier way?
> 2)
> Concerning the error above I dedected I can not catch the error in my
stored
> procedure with the instruction IF @.@.error ... So is it true that I can
only
> handle this error in my client software that calls the stored
procedure?
> 3)
> My full text index works fine (SQLSERVER2000/WIN2000). It requires to
> update indexes immediately, so I use a timestamp field to enable this.
Now,
> I've got a stored procedures which nearly daily inserts about 10.000 rows.
> When doing this while full text indexing is active, all users start
> complaining about performance. In order to work around this problem I
> tried doing the following ...
> Create myStoredProcedure
> -- begin of stored procedure
> exec sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> exec sp_fulltext_table 'adsfull', 'stop_change_tracking'
> --
> -- insert 10.000 rows
> --
> -- end of stored procedure
> exec sp_fulltext_table 'adsfull', 'start_change_tracking'
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> Now, it seems this doesn't work. SQL Server keeps tracking changes and
> updating indexes. Also if I cut away the stop instructions and paste them
> into query analyzer before starting the stored procedure.
> So, if I check the status via select fulltextcatalogproperty('FTADS',
> 'Populatestatus') ... it returns value 6 (incremental in progress) instead
> of 0 (idle) while executing the stored procedure.
> The only way I can resolve this issue is to stop the indexing via the
> enterprise manager and to restart after the stored procedure is executed.
> Is there a better/other way to stop and restart indexing instead of the 4
> lines I used above?
> Any help appreciated.
> --
> Kind regards,
> Perre Van Wilrijk,
> Remove capitals to get my real email address,
>
|||Hilary, John,
Clarifying solutions,
Thanks a lot.
"John Kane" <jt-kane@.comcast.net> wrote in message
news:OeHOZmcBFHA.2572@.tk2msftngp13.phx.gbl...
> Perre,
> Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
> question in this newsgroup, and there are many ways of accomplishing this,
> but the best one (IMHO) is one that I posted to this newsgroup back on
March
> 21, 2003 as recorded in Google Groups via the following shortened url:
> http://tinyurl.com/69kyy. Specifically:
> Create Table noise_words
> (
> Noiseword varchar(50) Not Null
> )
> Go
> Alter Table noise_words Add Constraint PK_noise_words PrimarXy Key
Clustered
> (
> Noiseword
> )
> Go
> Then you can use BULK INSERT, BCP or DTS to copy the contentXs of the file
> into the database. Before you copy in the language-specific Xnoise word
> file, you will need to make some changes to the initial file from Xthe
end
> of the file as the noise word files contain a list of "white space"X
single
> letters and characters at the end of the file, for example, from
noiXse.enu:
> a b c d e f g h i j k l m n o p q r s t u v w x y z
> BULK INSERT or BCP will fail or think this is one big stringX (no CR/LF),
so
> you will need to separate out the row above such that each lXetter takes
up
> its own row in the file. Open the language specific noise woXrd file in a
> text editor (notepad.exe) and change the above list to:
> a
> b
> c
> d
> e
> f
> and so on. Be sure to eliminate any leading or trailing spacXes for each
> character. Once that's done, you can use BULK INSERT, BCP orX DTS to copy
> the data from the noise file to the noise_words table. Once the data is
> imported correctly, you can use a standard XSQL statement such as:
> select count(*) from noise_words where Noiseword = "between"
> to use in a string parser function to remove the noise wordsX in your
users[vbcol=seagreen]
> input string and then pass this edited string to a SQL ServeXr Full-Text
> Search query.
> In regards to FTS setting @.@.error, see KB article: Q287167 "FIX: Some
> Full-Text Search Failures Do Not Set @.@.ERROR" at
> http://support.microsoft.com/default...;en-us;Q287167
> In regards to stopping and then restarting FT Indexing, try the following:
> EXEC sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> EXEC sp_fulltext_catalog 'adsfull', 'stop'
> --
> -- insert 10,000 rows
> --
> exec sp_fulltext_table 'adsfull', 'start_background_updateindex'
> go
> If that does not work, then try stopping the MSSearch service via
> xp_cmdshell, inserting your 10,000 rows and then re-starting the MSSearch
> service, for example:
> -- Stop the MSSearch Service
> exec master..xp_cmdshell 'net stop "Microsoft Search"'
> go
> -- Start the MSSearch Service
> exec master..xp_cmdshell 'net start "Microsoft Search"'
> go
> Hope that helps!
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in message
> news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
...[vbcol=seagreen]
> wondered
the[vbcol=seagreen]
> stored
> only
> procedure?
> Now,
rows.[vbcol=seagreen]
them[vbcol=seagreen]
instead[vbcol=seagreen]
executed.[vbcol=seagreen]
4
>
|||Hilary,
Re point 2, I am running multiple full-text searches
within a cursor (Original posting
in .sqlserver.programming, 4th Feb , Subject: "On Error
Resume Next" in SQL Server) and I want the procedure to
run through the entire cursor whatever but if there is an
ignored-words error the whole thing stops and does not
reach the end of the cursor so it's not so much that I
want to handle an error as to ignore it but I can't seem
to do this either. Any ideas?
>--Original Message--
>1) You could do something like this:
>set nocount on
>GO
>Create table Noise
>(noiseword varchar(100))
>GO
>insert into noise
>exec master.dbo.xp_Cmdshell 'type c:\"Program
Files\Microsoft SQL
>Server"\mssql\ftdata\sqlserver\config\noise.enu '
>GO
>delete from noise where NoiseWord is null
>GO
>declare @.string varchar(100)
>select @.string=noiseword from noise where charindex
(' ',noiseword)>0
>while charindex(' ',@.string)>0
>begin
>insert into noise (noiseword) values (left
(@.string,charindex(' ',@.string)))
>select @.string=substring(@.string,charindex(' ',@.string)
+1,100)
>end
>GO
>delete from noise where len(noiseword)-len(replace
(noiseword,' ',''))>0
>GO
>select * from noise order by 1
>For use US English.
>2) no there is no good way of doing this. I normally
check at the client,
>for instance errors messages will be returned via ado
saying MSSearch
>service not runing,
>3) Whenever you kick of change tracking a full or
incremental population is
>started. I can't think of a way to get around this right
now. I'd try to
>investigate exactly why you are experiencing locking on
your table with the
>insert proc. Perhaps you have having data page movement
associated with
>cluster index reorgs.
>--
>Hilary Cotter
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>"Perre Van Wilrijk" <prSPAM@.AkoopjeskrantWAY.be> wrote in
message[vbcol=seagreen]
>news:aeadnXXravNPo2fcRVnytg@.scarlet.biz...
Line 2[vbcol=seagreen]
fads_adid[vbcol=seagreen]
with noise words ...[vbcol=seagreen]
configuration. Now I[vbcol=seagreen]
>wondered
noisewords[vbcol=seagreen]
I could import the[vbcol=seagreen]
way?[vbcol=seagreen]
the error in my[vbcol=seagreen]
>stored
true that I can[vbcol=seagreen]
>only
the stored[vbcol=seagreen]
>procedure?
It requires to[vbcol=seagreen]
to enable this.[vbcol=seagreen]
>Now,
about 10.000 rows.[vbcol=seagreen]
users start[vbcol=seagreen]
this problem I[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_background_updateindex'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_background_updateindex'[vbcol=seagreen]
tracking changes and[vbcol=seagreen]
instructions and paste them[vbcol=seagreen]
procedure.[vbcol=seagreen]
fulltextcatalogproperty('FTADS',[vbcol=seagreen]
in progress) instead[vbcol=seagreen]
indexing via the[vbcol=seagreen]
procedure is executed.[vbcol=seagreen]
indexing instead of the 4
>
>.
>
|||Andy,
Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
question in this newsgroup, and there are many ways of accomplishing this,
but the best one (IMHO) is one that I posted to this newsgroup back on March
21, 2003 as recorded in Google Groups via the following shortened url:
http://tinyurl.com/69kyy.
In regards, to point 2, have you read KB article: Q287167 "FIX: Some
Full-Text Search Failures Do Not Set @.@.ERROR" at
http://support.microsoft.com/default...;en-us;Q287167 ?
Could you re-post your SQL script with the cursor code? Depending upon what
you are trying to do, the use of cursors, may not be the best approach.
Thanks,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Re point 2, I am running multiple full-text searches
> within a cursor (Original posting
> in .sqlserver.programming, 4th Feb , Subject: "On Error
> Resume Next" in SQL Server) and I want the procedure to
> run through the entire cursor whatever but if there is an
> ignored-words error the whole thing stops and does not
> reach the end of the cursor so it's not so much that I
> want to handle an error as to ignore it but I can't seem
> to do this either. Any ideas?
> Files\Microsoft SQL
> (' ',noiseword)>0
> (@.string,charindex(' ',@.string)))
> +1,100)
> (noiseword,' ',''))>0
> check at the client,
> saying MSSearch
> incremental population is
> now. I'd try to
> your table with the
> associated with
> message
> Line 2
> fads_adid
> with noise words ...
> configuration. Now I
> noisewords
> I could import the
> way?
> the error in my
> true that I can
> the stored
> It requires to
> to enable this.
> about 10.000 rows.
> users start
> this problem I
> sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> sp_fulltext_table 'adsfull', 'start_change_tracking'
> sp_fulltext_table 'adsfull', 'start_background_updateindex'
> tracking changes and
> instructions and paste them
> procedure.
> fulltextcatalogproperty('FTADS',
> in progress) instead
> indexing via the
> procedure is executed.
> indexing instead of the 4
|||there doesn't seem to be a clean way to handle this other than to extract
these words at the beginning before sending them to the cursor.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Now available on Amazon.com
http://www.amazon.com/gp/product/off...?condition=all
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...[vbcol=seagreen]
> Hilary,
> Re point 2, I am running multiple full-text searches
> within a cursor (Original posting
> in .sqlserver.programming, 4th Feb , Subject: "On Error
> Resume Next" in SQL Server) and I want the procedure to
> run through the entire cursor whatever but if there is an
> ignored-words error the whole thing stops and does not
> reach the end of the cursor so it's not so much that I
> want to handle an error as to ignore it but I can't seem
> to do this either. Any ideas?
> Files\Microsoft SQL
> (' ',noiseword)>0
> (@.string,charindex(' ',@.string)))
> +1,100)
> (noiseword,' ',''))>0
> check at the client,
> saying MSSearch
> incremental population is
> now. I'd try to
> your table with the
> associated with
> message
> Line 2
> fads_adid
> with noise words ...
> configuration. Now I
> noisewords
> I could import the
> way?
> the error in my
> true that I can
> the stored
> It requires to
> to enable this.
> about 10.000 rows.
> users start
> this problem I
> sp_fulltext_table 'adsfull', 'stop_background_updateindex'
> sp_fulltext_table 'adsfull', 'start_change_tracking'
> sp_fulltext_table 'adsfull', 'start_background_updateindex'
> tracking changes and
> instructions and paste them
> procedure.
> fulltextcatalogproperty('FTADS',
> in progress) instead
> indexing via the
> procedure is executed.
> indexing instead of the 4
|||Andy,
FYI, on KB article Q287167 as it is a "FIX" kb article, and this issue with
@.@.error was fixed in SQL Server 2000 SP1.
What is the full output of -- SELECT @.@.version -- on your server where you
are executing the below example code?
DECLARE TestCursor CURSOR FOR /*WHATEVER*/
OPEN TestCursor
WHILE (1 = 1)
BEGIN
FETCH NEXT FROM TestCursor INTO @.SearchText
SET @.SearchText = FormatSearchText(@.SearchText)
/*
This is a UDF with output as per KB article as mentioned.
If anything goes wrong this returns '' as I am not
bothered if it cannot resolve input but note: This can
still output junk that can break the CONTAINS search.
*/
INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
/*
When run in QA, if this query causes an ignored-word error
the SP stops dead. I need it to carry on to the end of the
cursor.
*/
END, CLOSE, DEALLOCATE etc.
SELECT * FROM #TEMPTABLE /* Output of entire cursor */
That's pretty much what I'm trying to achieve. What do you reckon?
I reckon that if you enclose your @.SearchText in double quotes, i.e., a
phrase search, the noise word would be truly ignored. See SQL Server 2000
BOL title "Full-text Search Recommendations" - "Consider rewriting this
query to a phrase-based query, removing the noise word, or options offered
in Knowledge Base article Q246800, "INF: Correctly Parsing Quotation Marks
in FTS Queries". For example using the pubs database table pr_info:
select pub_id, pr_info from pub_info where CONTAINS(pr_info, '"between AND
books"')
In the above query the noise word "between" is truly ignored or you can use
FREETEXT if the search string cannot be fully placed within double quotes,
for example:
select pub_id, pr_info from pub_info where freetext(pr_info, '"between" AND
"books"')
Finally, you can remove all noise words from your language-specific noise
word file. These files are located under \FTDATA\SQLServer\Config\noise.*
where * represents the language - enu = US_English. You will need to stop
the MSSearch service, edit noise.enu (assuming US_English) with notepad.exe
and remove the words and save the file, restart the MSSearch service and run
a Full Population on all of your FT Catalogs. I'd recommend that you remove
all noise words that you may want to search on, but not empty the entire
file and at least leave a single space in the file.
Regards,
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"John Kane" <jt-kane@.comcast.net> wrote in message
news:e4tWiVTDFHA.2180@.TK2MSFTNGP12.phx.gbl...
> Andy,
> Yes, getting Error Msg 7619 and how to avoid it is a frequently asked
> question in this newsgroup, and there are many ways of accomplishing this,
> but the best one (IMHO) is one that I posted to this newsgroup back on
March
> 21, 2003 as recorded in Google Groups via the following shortened url:
> http://tinyurl.com/69kyy.
> In regards, to point 2, have you read KB article: Q287167 "FIX: Some
> Full-Text Search Failures Do Not Set @.@.ERROR" at
> http://support.microsoft.com/default...;en-us;Q287167 ?
> Could you re-post your SQL script with the cursor code? Depending upon
what
> you are trying to do, the use of cursors, may not be the best approach.
> Thanks,
> John
> --
> SQL Full Text Search Blog
> http://spaces.msn.com/members/jtkane/
>
>
> "Andy Wakeling" <anonymous@.discussions.microsoft.com> wrote in message
> news:2b8a01c50d0b$6bdf12f0$a601280a@.phx.gbl...
>
|||John,
I didn't make it clear but the UDF encloses everything in
double quotes unless it resolves to nothing in which case
an empty string is returned and the search is not
performed. It's not the noise words I have a problem with,
I just wanted to be able to skip over the ignored-words
error and carry on. Obviously the best solution would be
to make the UDF infallible but as good as I think I've got
it something else comes along e.g. one user had a search
text of a single DEL (ASCII 127) character and that broke
it. Now without going through the entire database and
reformatting all the users' search terms the only way of
proceding is to either account for absolutely every
possible ASCII/Unicode character combination or to simply
ignore examples such as the above which is what I'm trying
to do. As far as the @.@.version it's SQL 2000 SP3.
I may just have to return the initial results and do each
full-text search from the client code but i'm just a bit
miffed of having to make all those extra DB calls :-(
>--Original Message--
>Andy,
>FYI, on KB article Q287167 as it is a "FIX" kb article,
and this issue with
>@.@.error was fixed in SQL Server 2000 SP1.
>What is the full output of -- SELECT @.@.version -- on your
server where you
>are executing the below example code?
>DECLARE TestCursor CURSOR FOR /*WHATEVER*/
>OPEN TestCursor
>WHILE (1 = 1)
>BEGIN
>FETCH NEXT FROM TestCursor INTO @.SearchText
>SET @.SearchText = FormatSearchText(@.SearchText)
>/*
>This is a UDF with output as per KB article as mentioned.
>If anything goes wrong this returns '' as I am not
>bothered if it cannot resolve input but note: This can
>still output junk that can break the CONTAINS search.
>*/
>INSERT INTO #TEMPTABLE SELECT /*WHATEVER FROM WHEREVER*/
>WHERE CONTAINS(/*SEARCHFIELD*/, @.SearchText)
>/*
>When run in QA, if this query causes an ignored-word error
>the SP stops dead. I need it to carry on to the end of the
>cursor.
>*/
>END, CLOSE, DEALLOCATE etc.
>SELECT * FROM #TEMPTABLE /* Output of entire cursor */
>That's pretty much what I'm trying to achieve. What do
you reckon?
>I reckon that if you enclose your @.SearchText in double
quotes, i.e., a
>phrase search, the noise word would be truly ignored. See
SQL Server 2000
>BOL title "Full-text Search Recommendations" - "Consider
rewriting this
>query to a phrase-based query, removing the noise word,
or options offered
>in Knowledge Base article Q246800, "INF: Correctly
Parsing Quotation Marks
>in FTS Queries". For example using the pubs database
table pr_info:
>select pub_id, pr_info from pub_info where CONTAINS
(pr_info, '"between AND
>books"')
>In the above query the noise word "between" is truly
ignored or you can use
>FREETEXT if the search string cannot be fully placed
within double quotes,
>for example:
>select pub_id, pr_info from pub_info where freetext
(pr_info, '"between" AND
>"books"')
>Finally, you can remove all noise words from your
language-specific noise
>word file. These files are located under
\FTDATA\SQLServer\Config\noise.*
>where * represents the language - enu = US_English. You
will need to stop
>the MSSearch service, edit noise.enu (assuming
US_English) with notepad.exe
>and remove the words and save the file, restart the
MSSearch service and run
>a Full Population on all of your FT Catalogs. I'd
recommend that you remove
>all noise words that you may want to search on, but not
empty the entire[vbcol=seagreen]
>file and at least leave a single space in the file.
>Regards,
>John
>--
>SQL Full Text Search Blog
>http://spaces.msn.com/members/jtkane/
>
>"John Kane" <jt-kane@.comcast.net> wrote in message
>news:e4tWiVTDFHA.2180@.TK2MSFTNGP12.phx.gbl...
frequently asked[vbcol=seagreen]
accomplishing this,[vbcol=seagreen]
newsgroup back on[vbcol=seagreen]
>March
shortened url:[vbcol=seagreen]
Q287167 "FIX: Some[vbcol=seagreen]
us;Q287167 ?[vbcol=seagreen]
Depending upon[vbcol=seagreen]
>what
the best approach.[vbcol=seagreen]
wrote in message[vbcol=seagreen]
Error[vbcol=seagreen]
to[vbcol=seagreen]
is an[vbcol=seagreen]
seem[vbcol=seagreen]
(' ',@.string)[vbcol=seagreen]
right[vbcol=seagreen]
on[vbcol=seagreen]
movement[vbcol=seagreen]
wrote in[vbcol=seagreen]
usp_ft,[vbcol=seagreen]
ads_adid =[vbcol=seagreen]
AND "astra"'))))[vbcol=seagreen]
file[vbcol=seagreen]
query?[vbcol=seagreen]
easier[vbcol=seagreen]
catch[vbcol=seagreen]
is it[vbcol=seagreen]
calls[vbcol=seagreen]
(SQLSERVER2000/WIN2000).[vbcol=seagreen]
field[vbcol=seagreen]
inserts[vbcol=seagreen]
active, all[vbcol=seagreen]
around[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_background_updateindex'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'stop_change_tracking'[vbcol=seagreen]
sp_fulltext_table 'adsfull', 'start_background_updateindex'[vbcol=seagreen]
(incremental[vbcol=seagreen]
the
>
>.
>
Noise words issue in full text seach Sql server 2000
Hi All,
I have been using full text search in my application for search purpose.
But on certain words i.e Noise Words such as And,After,About etc.
I am receiving the following error:
Execution of a full-text operation failed. A clause of the query contained only ignored words.
I tried a lot to sort out this issue,but this is going to be out of my scope.
If anybody can help me then it would be really appreciable.
Thanks in Advance,
Vivek RathoreFull-text maintains a list of noise words in different files which are language specific. When you receive the Msg 7619 The query contained only ignored words" SQL server has eliminated all words in the query. If you would like to search on those words you can try utilizing FREETEXT or editing the files maintaining the list of noise words.
These noise-word lists should be sufficient for most normal operations, but can be modified for specific environments with a text editor. For more information, see the Indexing Service 2.0 documentation in the Windows NT 4.0 Option Pack.
Noise Words for CONTAINSTABLE
Ladies and Gentleman;
Where might one obtain an entire list of noise words that cannot be used
with a CONTAINSTABLE keyword.
Thanks,
PaulThey are the noise.* files (noise.eng is British English, noise.enu is the
US English version). These can be found in (by default) C:\Program
Files\Common Files\System\MSSearch\Data\Applications\SQLServer\Config and
C:\WINNT\system32.
You can read the files with notepad or load them into a SQL table
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul McKirdy" <paul@.traderonline.com> wrote in message
news:u7xZbJRrDHA.644@.TK2MSFTNGP11.phx.gbl...
Ladies and Gentleman;
Where might one obtain an entire list of noise words that cannot be used
with a CONTAINSTABLE keyword.
Thanks,
Paul
Where might one obtain an entire list of noise words that cannot be used
with a CONTAINSTABLE keyword.
Thanks,
PaulThey are the noise.* files (noise.eng is British English, noise.enu is the
US English version). These can be found in (by default) C:\Program
Files\Common Files\System\MSSearch\Data\Applications\SQLServer\Config and
C:\WINNT\system32.
You can read the files with notepad or load them into a SQL table
--
HTH
Jasper Smith (SQL Server MVP)
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Paul McKirdy" <paul@.traderonline.com> wrote in message
news:u7xZbJRrDHA.644@.TK2MSFTNGP11.phx.gbl...
Ladies and Gentleman;
Where might one obtain an entire list of noise words that cannot be used
with a CONTAINSTABLE keyword.
Thanks,
Paul
noise words and language support
Hi,
I am using sql2000 server full-text search and neutral word braeker is set.
I am having problem with noise words becouse the sql query returns with
ignored word error.
The sql query somethink like that
SELECT title FROM tFtest WHERE contains (title,'"*nokia*" near
"*phone*" near "*-*"' )
"-" is not in ignored words and also "/ * ' " etc words give the error. I
cleared the noise.dat file and restat the mssearch but I got still the same
error. how can I avoid the error. The table rows are less than 100 thousand
so the file size is not important.
The second question is sql2000 fulltext search server doesn't support
turkish language and that is why I had to choose neutral setting. Is there
any why to use turkish language in fulltext search.
Thankstolgay wrote on Wed, 10 Oct 2007 11:54:15 +0300:
> Hi,
> I am using sql2000 server full-text search and neutral word braeker is
> set.
> I am having problem with noise words becouse the sql query returns with
> ignored word error.
> The sql query somethink like that
> SELECT title FROM tFtest WHERE contains (title,'"*nokia*" near
> "*phone*" near "*-*"' )
> "-" is not in ignored words and also "/ * ' " etc words give the
> error. I cleared the noise.dat file and restat the mssearch but I got
> still the same error. how can I avoid the error. The table rows are
> less than 100 thousand so the file size is not important.
- / * are not words. Also, you cannot use a term like *nokia* - the *
operator is for prefix searches only (eg. nokia* finds all words that start
with nokia).
Your search should look like this:
SELECT title FROM tFtest WHERE contains (title,'"nokia" near
"phone"')
Dan|||Thanks you light me up :)
I know if I erase the line the code will work but dou you know which words
cannot be used which are not in the noise.dat becouse the search text comes
from search page and user can write everythink or anythink what they want
and my code must work with out given error.
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eT$KkBzCIHA.1204@.TK2MSFTNGP03.phx.gbl...
> tolgay wrote on Wed, 10 Oct 2007 11:54:15 +0300:
> > Hi,
> > I am using sql2000 server full-text search and neutral word braeker is
> > set.
> > I am having problem with noise words becouse the sql query returns with
> > ignored word error.
> > The sql query somethink like that
> > SELECT title FROM tFtest WHERE contains (title,'"*nokia*" near
> > "*phone*" near "*-*"' )
> > "-" is not in ignored words and also "/ * ' " etc words give the
> > error. I cleared the noise.dat file and restat the mssearch but I got
> > still the same error. how can I avoid the error. The table rows are
> > less than 100 thousand so the file size is not important.
>
> - / * are not words. Also, you cannot use a term like *nokia* - the *
> operator is for prefix searches only (eg. nokia* finds all words that
start
> with nokia).
> Your search should look like this:
> SELECT title FROM tFtest WHERE contains (title,'"nokia" near
> "phone"')
> Dan
>|||tolgay wrote on Wed, 10 Oct 2007 14:48:54 +0300:
> Thanks you light me up :)
> I know if I erase the line the code will work but dou you know which
> words cannot be used which are not in the noise.dat becouse the search
> text comes from search page and user can write everythink or anythink
> what they want and my code must work with out given error.
Just strip out all non-alphanumeric "words".
Dan
I am using sql2000 server full-text search and neutral word braeker is set.
I am having problem with noise words becouse the sql query returns with
ignored word error.
The sql query somethink like that
SELECT title FROM tFtest WHERE contains (title,'"*nokia*" near
"*phone*" near "*-*"' )
"-" is not in ignored words and also "/ * ' " etc words give the error. I
cleared the noise.dat file and restat the mssearch but I got still the same
error. how can I avoid the error. The table rows are less than 100 thousand
so the file size is not important.
The second question is sql2000 fulltext search server doesn't support
turkish language and that is why I had to choose neutral setting. Is there
any why to use turkish language in fulltext search.
Thankstolgay wrote on Wed, 10 Oct 2007 11:54:15 +0300:
> Hi,
> I am using sql2000 server full-text search and neutral word braeker is
> set.
> I am having problem with noise words becouse the sql query returns with
> ignored word error.
> The sql query somethink like that
> SELECT title FROM tFtest WHERE contains (title,'"*nokia*" near
> "*phone*" near "*-*"' )
> "-" is not in ignored words and also "/ * ' " etc words give the
> error. I cleared the noise.dat file and restat the mssearch but I got
> still the same error. how can I avoid the error. The table rows are
> less than 100 thousand so the file size is not important.
- / * are not words. Also, you cannot use a term like *nokia* - the *
operator is for prefix searches only (eg. nokia* finds all words that start
with nokia).
Your search should look like this:
SELECT title FROM tFtest WHERE contains (title,'"nokia" near
"phone"')
Dan|||Thanks you light me up :)
I know if I erase the line the code will work but dou you know which words
cannot be used which are not in the noise.dat becouse the search text comes
from search page and user can write everythink or anythink what they want
and my code must work with out given error.
"Daniel Crichton" <msnews@.worldofspack.com> wrote in message
news:eT$KkBzCIHA.1204@.TK2MSFTNGP03.phx.gbl...
> tolgay wrote on Wed, 10 Oct 2007 11:54:15 +0300:
> > Hi,
> > I am using sql2000 server full-text search and neutral word braeker is
> > set.
> > I am having problem with noise words becouse the sql query returns with
> > ignored word error.
> > The sql query somethink like that
> > SELECT title FROM tFtest WHERE contains (title,'"*nokia*" near
> > "*phone*" near "*-*"' )
> > "-" is not in ignored words and also "/ * ' " etc words give the
> > error. I cleared the noise.dat file and restat the mssearch but I got
> > still the same error. how can I avoid the error. The table rows are
> > less than 100 thousand so the file size is not important.
>
> - / * are not words. Also, you cannot use a term like *nokia* - the *
> operator is for prefix searches only (eg. nokia* finds all words that
start
> with nokia).
> Your search should look like this:
> SELECT title FROM tFtest WHERE contains (title,'"nokia" near
> "phone"')
> Dan
>|||tolgay wrote on Wed, 10 Oct 2007 14:48:54 +0300:
> Thanks you light me up :)
> I know if I erase the line the code will work but dou you know which
> words cannot be used which are not in the noise.dat becouse the search
> text comes from search page and user can write everythink or anythink
> what they want and my code must work with out given error.
Just strip out all non-alphanumeric "words".
Dan
Noise words
Could someone tell me how I "turn off" the noise word filtering for SQL server?
My problem is that I use the neutral language (defaults to english) and it strips out some words I do not want stripped from my queries. So in this particular case, I would like to be able to turn off noise word filtering altogether.
Ideas?
Noise words are stored in a text file, by default, it is in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\
Just edit the noiseNEU.txt for neutral language. Delete the word in the file. You may need to restart sql server and recrawl your index.
Noise Words
http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-Dan
Please forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
> get SQL Server to ignore noise words in the query, BOL's discussion on noise
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged for
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I must
> be overlooking something. Can I seriously edit the noise words, or is it a
> tease?
> -Dan
|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...[vbcol=seagreen]
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-Dan
Please forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
> get SQL Server to ignore noise words in the query, BOL's discussion on noise
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged for
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I must
> be overlooking something. Can I seriously edit the noise words, or is it a
> tease?
> -Dan
|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...[vbcol=seagreen]
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
Noise Words
http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how t
o
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-DanPlease forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how
to
> get SQL Server to ignore noise words in the query, BOL's discussion on noi
se
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged fo
r
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I mus
t
> be overlooking something. Can I seriously edit the noise words, or is it
a
> tease?
> -Dan|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...[vbcol=seagreen]
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
>
o
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-DanPlease forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how
to
> get SQL Server to ignore noise words in the query, BOL's discussion on noi
se
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged fo
r
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I mus
t
> be overlooking something. Can I seriously edit the noise words, or is it
a
> tease?
> -Dan|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...[vbcol=seagreen]
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
>
Noise Words
http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-DanPlease forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
> get SQL Server to ignore noise words in the query, BOL's discussion on noise
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged for
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I must
> be overlooking something. Can I seriously edit the noise words, or is it a
> tease?
> -Dan|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
>> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely
>> how to
>> get SQL Server to ignore noise words in the query, BOL's discussion on
>> noise
>> words is all nice except things don't seem as they appear.
>> Right from BOL:
>> Important:
>> If you edit a noise-word file, you must repopulate the full-text catalogs
>> before the changes will take effect.
>> Yes, the DBA has a choice in the matter. No, wait...I made changes,
>> repopulated the index, and I'm still not getting the results I want.
>> There are words in the $SQL_Server_Install_Path\Microsoft SQL
>> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
>> words. When I remove them and repopulate the index, I still get tagged
>> for
>> "Informational: The full-text search condition contained noise word(s)."
>>
>> Is there a literal difference from words of BOL "repopulate the full-text
>> catalogs" and the context menu item "Start full Population"? I know I
>> must
>> be overlooking something. Can I seriously edit the noise words, or is it
>> a
>> tease?
>> -Dan
get SQL Server to ignore noise words in the query, BOL's discussion on noise
words is all nice except things don't seem as they appear.
Right from BOL:
Important:
If you edit a noise-word file, you must repopulate the full-text catalogs
before the changes will take effect.
Yes, the DBA has a choice in the matter. No, wait...I made changes,
repopulated the index, and I'm still not getting the results I want.
There are words in the $SQL_Server_Install_Path\Microsoft SQL
Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
words. When I remove them and repopulate the index, I still get tagged for
"Informational: The full-text search condition contained noise word(s)."
Is there a literal difference from words of BOL "repopulate the full-text
catalogs" and the context menu item "Start full Population"? I know I must
be overlooking something. Can I seriously edit the noise words, or is it a
tease?
-DanPlease forgive me, I did discover that rebuilding the Catelog is much
different than repopulating the indexes. Maybe the BOL could differentiate
this better since Index manuveres can be achieved thru context menus and
catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
real clear in the docs
-Dan
"Dan Davis" wrote:
> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely how to
> get SQL Server to ignore noise words in the query, BOL's discussion on noise
> words is all nice except things don't seem as they appear.
> Right from BOL:
> Important:
> If you edit a noise-word file, you must repopulate the full-text catalogs
> before the changes will take effect.
> Yes, the DBA has a choice in the matter. No, wait...I made changes,
> repopulated the index, and I'm still not getting the results I want.
> There are words in the $SQL_Server_Install_Path\Microsoft SQL
> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
> words. When I remove them and repopulate the index, I still get tagged for
> "Informational: The full-text search condition contained noise word(s)."
>
> Is there a literal difference from words of BOL "repopulate the full-text
> catalogs" and the context menu item "Start full Population"? I know I must
> be overlooking something. Can I seriously edit the noise words, or is it a
> tease?
> -Dan|||Hi Dan
There is a .fulltext newsgroup. You might have better luck posting your
questions there.
--
HTH
Kalen Delaney, SQL Server MVP
http://sqlblog.com
"Dan Davis" <In_Finite@.news.postalias> wrote in message
news:9F9E5E9A-1A7F-484A-98B3-E3E8F5C95F27@.microsoft.com...
> Please forgive me, I did discover that rebuilding the Catelog is much
> different than repopulating the indexes. Maybe the BOL could
> differentiate
> this better since Index manuveres can be achieved thru context menus and
> catelogs have to be rebuilt via a ALTER FULLTEXT CATALOG statement. not
> real clear in the docs
> -Dan
> "Dan Davis" wrote:
>> http://msdn2.microsoft.com/en-us/library/ms345119.aspx explains nicely
>> how to
>> get SQL Server to ignore noise words in the query, BOL's discussion on
>> noise
>> words is all nice except things don't seem as they appear.
>> Right from BOL:
>> Important:
>> If you edit a noise-word file, you must repopulate the full-text catalogs
>> before the changes will take effect.
>> Yes, the DBA has a choice in the matter. No, wait...I made changes,
>> repopulated the index, and I'm still not getting the results I want.
>> There are words in the $SQL_Server_Install_Path\Microsoft SQL
>> Server\MSSQL.1\MSSQL\FTDATA\ noise files that I don't agree with as noise
>> words. When I remove them and repopulate the index, I still get tagged
>> for
>> "Informational: The full-text search condition contained noise word(s)."
>>
>> Is there a literal difference from words of BOL "repopulate the full-text
>> catalogs" and the context menu item "Start full Population"? I know I
>> must
>> be overlooking something. Can I seriously edit the noise words, or is it
>> a
>> tease?
>> -Dan
Noise words
Could someone tell me how I "turn off" the noise word filtering for SQL server?
My problem is that I use the neutral language (defaults to english) and it strips out some words I do not want stripped from my queries. So in this particular case, I would like to be able to turn off noise word filtering altogether.
Ideas?
Noise words are stored in a text file, by default, it is in
C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\FTData\
Just edit the noiseNEU.txt for neutral language. Delete the word in the file. You may need to restart sql server and recrawl your index.
Noise Words
We were getting errors when users searched for the term "r&d" - with or
without quotes - so we removed all single-letter words from the noise
word files (.eng, .enu and .dat). We then started Microsoft Search
again and did a full population - "rebuild" was grayed out.
This stopped the error but now we get no results.
Questions:
1) Why are we getting no results?
2) Is "&" a word breaker? Can we change this?
Are there any messages in the event log from MSSearch or MSSCI?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<s.cawood@.gmail.com> wrote in message
news:1143716799.014202.246690@.i39g2000cwa.googlegr oups.com...
> We were getting errors when users searched for the term "r&d" - with or
> without quotes - so we removed all single-letter words from the noise
> word files (.eng, .enu and .dat). We then started Microsoft Search
> again and did a full population - "rebuild" was grayed out.
> This stopped the error but now we get no results.
> Questions:
> 1) Why are we getting no results?
> 2) Is "&" a word breaker? Can we change this?
>
without quotes - so we removed all single-letter words from the noise
word files (.eng, .enu and .dat). We then started Microsoft Search
again and did a full population - "rebuild" was grayed out.
This stopped the error but now we get no results.
Questions:
1) Why are we getting no results?
2) Is "&" a word breaker? Can we change this?
Are there any messages in the event log from MSSearch or MSSCI?
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<s.cawood@.gmail.com> wrote in message
news:1143716799.014202.246690@.i39g2000cwa.googlegr oups.com...
> We were getting errors when users searched for the term "r&d" - with or
> without quotes - so we removed all single-letter words from the noise
> word files (.eng, .enu and .dat). We then started Microsoft Search
> again and did a full population - "rebuild" was grayed out.
> This stopped the error but now we get no results.
> Questions:
> 1) Why are we getting no results?
> 2) Is "&" a word breaker? Can we change this?
>
Noise word blues
Hi,
I'm having a noise word problem that I don't seem to see in any of the other
postings.
Here is the setup:
SQL Server 2000 Standard Edition SP3 running on Win 2003 Server.
In order to avoid the normal noise word problems we edited the noise word
file for english by just putting a space in it and saving the file and
restarting SQL Server and the search service (reboot of machine actually).
However there are still some characters that when used with a CONTAINS still
give back the noise word error. The ones we found tend to be special single
characters such as "?" or "_".
An example would be: WHERE CONTAINS(*, '"Pick" AND "?" AND "List"')
Is there any way to get these to be ignored as well or is there a
comprehensive list of these characters so we could parse them out on in the
application code? We've tried neutral word breaking and using FREETEXT and
that didn't work either.
Thanks in advance for any help,
Wayne Antinore
Unfortunately what you have to do is parse your content and replace these
characters with a token, i.e. replace "?" with " QuestionMark " and then in
your queries when someone is querying on a ? expand this to a query on
QuestionMark.
You will need to replace the ? mark with a token which will not be searched
on and you will need to surround this token with white space for it to work
correctly.
When you return the data to the client make sure you replace the token back
with ?, or have non marked up content somewhere and return this content
instead.
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
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uZNyLtirFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm having a noise word problem that I don't seem to see in any of the
other
> postings.
> Here is the setup:
> SQL Server 2000 Standard Edition SP3 running on Win 2003 Server.
> In order to avoid the normal noise word problems we edited the noise word
> file for english by just putting a space in it and saving the file and
> restarting SQL Server and the search service (reboot of machine actually).
> However there are still some characters that when used with a CONTAINS
still
> give back the noise word error. The ones we found tend to be special
single
> characters such as "?" or "_".
> An example would be: WHERE CONTAINS(*, '"Pick" AND "?" AND "List"')
> Is there any way to get these to be ignored as well or is there a
> comprehensive list of these characters so we could parse them out on in
the
> application code? We've tried neutral word breaking and using FREETEXT
and
> that didn't work either.
> Thanks in advance for any help,
> Wayne Antinore
>
|||Thanks Hilary,
Yuck!, but thanks anyway at least now I know there isn't a quick fix that
I'm missing
Wayne
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJv7yJjrFHA.2272@.TK2MSFTNGP11.phx.gbl...
> Unfortunately what you have to do is parse your content and replace these
> characters with a token, i.e. replace "?" with " QuestionMark " and then
> in
> your queries when someone is querying on a ? expand this to a query on
> QuestionMark.
> You will need to replace the ? mark with a token which will not be
> searched
> on and you will need to surround this token with white space for it to
> work
> correctly.
> When you return the data to the client make sure you replace the token
> back
> with ?, or have non marked up content somewhere and return this content
> instead.
> --
> 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
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:uZNyLtirFHA.2212@.TK2MSFTNGP15.phx.gbl...
> other
> still
> single
> the
> and
>
I'm having a noise word problem that I don't seem to see in any of the other
postings.
Here is the setup:
SQL Server 2000 Standard Edition SP3 running on Win 2003 Server.
In order to avoid the normal noise word problems we edited the noise word
file for english by just putting a space in it and saving the file and
restarting SQL Server and the search service (reboot of machine actually).
However there are still some characters that when used with a CONTAINS still
give back the noise word error. The ones we found tend to be special single
characters such as "?" or "_".
An example would be: WHERE CONTAINS(*, '"Pick" AND "?" AND "List"')
Is there any way to get these to be ignored as well or is there a
comprehensive list of these characters so we could parse them out on in the
application code? We've tried neutral word breaking and using FREETEXT and
that didn't work either.
Thanks in advance for any help,
Wayne Antinore
Unfortunately what you have to do is parse your content and replace these
characters with a token, i.e. replace "?" with " QuestionMark " and then in
your queries when someone is querying on a ? expand this to a query on
QuestionMark.
You will need to replace the ? mark with a token which will not be searched
on and you will need to surround this token with white space for it to work
correctly.
When you return the data to the client make sure you replace the token back
with ?, or have non marked up content somewhere and return this content
instead.
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
"Wayne Antinore" <wantinore@.veramark.com> wrote in message
news:uZNyLtirFHA.2212@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I'm having a noise word problem that I don't seem to see in any of the
other
> postings.
> Here is the setup:
> SQL Server 2000 Standard Edition SP3 running on Win 2003 Server.
> In order to avoid the normal noise word problems we edited the noise word
> file for english by just putting a space in it and saving the file and
> restarting SQL Server and the search service (reboot of machine actually).
> However there are still some characters that when used with a CONTAINS
still
> give back the noise word error. The ones we found tend to be special
single
> characters such as "?" or "_".
> An example would be: WHERE CONTAINS(*, '"Pick" AND "?" AND "List"')
> Is there any way to get these to be ignored as well or is there a
> comprehensive list of these characters so we could parse them out on in
the
> application code? We've tried neutral word breaking and using FREETEXT
and
> that didn't work either.
> Thanks in advance for any help,
> Wayne Antinore
>
|||Thanks Hilary,
Yuck!, but thanks anyway at least now I know there isn't a quick fix that
I'm missing
Wayne
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OJv7yJjrFHA.2272@.TK2MSFTNGP11.phx.gbl...
> Unfortunately what you have to do is parse your content and replace these
> characters with a token, i.e. replace "?" with " QuestionMark " and then
> in
> your queries when someone is querying on a ? expand this to a query on
> QuestionMark.
> You will need to replace the ? mark with a token which will not be
> searched
> on and you will need to surround this token with white space for it to
> work
> correctly.
> When you return the data to the client make sure you replace the token
> back
> with ?, or have non marked up content somewhere and return this content
> instead.
> --
> 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
> "Wayne Antinore" <wantinore@.veramark.com> wrote in message
> news:uZNyLtirFHA.2212@.TK2MSFTNGP15.phx.gbl...
> other
> still
> single
> the
> and
>
Noise File.
After changing a noise file - does the FTS have to be restarted?
Also, any way to determine which noise.eng file is being used? There
are several locations...
On Apr 16, 3:41 pm, "AF" <af.at.w...@.gmail.com> wrote:
> After changing a noise file - does the FTS have to be restarted?
> Also, any way to determine which noise.eng file is being used? There
> are several locations...
It looks like C:\Program Files\Common Files\System\MSSearch\Data\Config
\noise.enu is the file used... *but* this is in a clustered
environment... how would this noise file get to the other server?
|||The path should be X:\Program Files\Microsoft SQL
Server\MSSQL$instancename\MSSQL\FTData\Server\Conf ig, IIRC
"AF" <af.at.work@.gmail.com> wrote in message
news:1176753798.884846.13040@.q75g2000hsh.googlegro ups.com...
> On Apr 16, 3:41 pm, "AF" <af.at.w...@.gmail.com> wrote:
> It looks like C:\Program Files\Common Files\System\MSSearch\Data\Config
> \noise.enu is the file used... *but* this is in a clustered
> environment... how would this noise file get to the other server?
>
|||On Apr 18, 10:05 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> The path should be X:\Program Files\Microsoft SQL
> Server\MSSQL$instancename\MSSQL\FTData\Server\Conf ig, IIRC"AF" <af.at.w...@.gmail.com> wrote in message
> news:1176753798.884846.13040@.q75g2000hsh.googlegro ups.com...
>
OK... how can I change that?
Also, any way to determine which noise.eng file is being used? There
are several locations...
On Apr 16, 3:41 pm, "AF" <af.at.w...@.gmail.com> wrote:
> After changing a noise file - does the FTS have to be restarted?
> Also, any way to determine which noise.eng file is being used? There
> are several locations...
It looks like C:\Program Files\Common Files\System\MSSearch\Data\Config
\noise.enu is the file used... *but* this is in a clustered
environment... how would this noise file get to the other server?
|||The path should be X:\Program Files\Microsoft SQL
Server\MSSQL$instancename\MSSQL\FTData\Server\Conf ig, IIRC
"AF" <af.at.work@.gmail.com> wrote in message
news:1176753798.884846.13040@.q75g2000hsh.googlegro ups.com...
> On Apr 16, 3:41 pm, "AF" <af.at.w...@.gmail.com> wrote:
> It looks like C:\Program Files\Common Files\System\MSSearch\Data\Config
> \noise.enu is the file used... *but* this is in a clustered
> environment... how would this noise file get to the other server?
>
|||On Apr 18, 10:05 am, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:[vbcol=seagreen]
> The path should be X:\Program Files\Microsoft SQL
> Server\MSSQL$instancename\MSSQL\FTData\Server\Conf ig, IIRC"AF" <af.at.w...@.gmail.com> wrote in message
> news:1176753798.884846.13040@.q75g2000hsh.googlegro ups.com...
>
OK... how can I change that?
Subscribe to:
Posts (Atom)