Re: noise words, @@ERROR, and stop and resume indexing
From: Perre Van Wilrijk (prSPAM_at_AkoopjeskrantWAY.be)
Date: 01/31/05
- Next message: Jeremy: "FreeTextTable on image column of document metadata"
- Previous message: Steve Kass: "Re: Search Performance"
- In reply to: John Kane: "Re: noise words, @@ERROR, and stop and resume indexing"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 31 Jan 2005 10:33:26 +0100
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 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: Jeremy: "FreeTextTable on image column of document metadata"
- Previous message: Steve Kass: "Re: Search Performance"
- In reply to: John Kane: "Re: noise words, @@ERROR, and stop and resume indexing"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
