Re: noise words, @@ERROR, and stop and resume indexing
From: John Kane (jt-kane_at_comcast.net)
Date: 01/29/05
- Next message: bracke eric: "SQL search engine - SQL Digger"
- Previous message: Hilary Cotter: "Re: noise words, @@ERROR, and stop and resume indexing"
- In reply to: Perre Van Wilrijk: "noise words, @@ERROR, and stop and resume indexing"
- Next in thread: Perre Van Wilrijk: "Re: noise words, @@ERROR, and stop and resume indexing"
- Reply: Perre Van Wilrijk: "Re: noise words, @@ERROR, and stop and resume indexing"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 28 Jan 2005 22:13:04 -0800
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 Primary Key Clustered
(
Noiseword
)
Go
Then you can use BULK INSERT, BCP or DTS to copy the contents of the file
into the database. Before you copy in the language-specific noise word
file, you will need to make some changes to the initial file from the end
of the file as the noise word files contain a list of "white space" single
letters and characters at the end of the file, for example, from noise.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 string (no CR/LF), so
you will need to separate out the row above such that each letter takes up
its own row in the file. Open the language specific noise word 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 spaces for each
character. Once that's done, you can use BULK INSERT, BCP or 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 SQL statement such as:
select count(*) from noise_words where Noiseword = "between"
to use in a string parser function to remove the noise words in your users
input string and then pass this edited string to a SQL Server 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.aspx?scid=kb;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, > >
- Next message: bracke eric: "SQL search engine - SQL Digger"
- Previous message: Hilary Cotter: "Re: noise words, @@ERROR, and stop and resume indexing"
- In reply to: Perre Van Wilrijk: "noise words, @@ERROR, and stop and resume indexing"
- Next in thread: Perre Van Wilrijk: "Re: noise words, @@ERROR, and stop and resume indexing"
- Reply: Perre Van Wilrijk: "Re: noise words, @@ERROR, and stop and resume indexing"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
