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