Re: noise words, @@ERROR, and stop and resume indexing

From: Hilary Cotter (hilary.cotter_at_gmail.com)
Date: 02/08/05 

Date: Mon, 7 Feb 2005 22:46:56 -0500

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/offer-listing/0974973602/ref=dp_more-buying-choices_2//102-1802128-2428137?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...
> 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
> >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,
> >>
> >>
> >
> >
> >.
> >

Relevant Pages

  • Re: noise words, @@ERROR, and stop and resume indexing
    ... Could you re-post your SQL script with the cursor code? ... >>Create table Noise ... >>Looking for a SQL Server replication book? ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... > Noiseword varcharNot Null ... > the data from the noise file to the noise_words table. ... >> A clause of the query contained only ignored words. ... >> into query analyzer before starting the stored procedure. ...
    (microsoft.public.sqlserver.fulltext)
  • Re: SQL stored procedure not working.
    ... If I do not use that, how can I get the total record count? ... >> I create a stored procedure and a asp page. ... Even if you could specify the cursor type there, ... > interested in the RETURN value, you do not need to use a Command object. ...
    (microsoft.public.inetserver.asp.db)
  • Re: Case statement issue
    ... except with a cursor other than the default ... > I have taken the sample of paging using a stored procedure from the ... > case statement, then the error message. ... > The for loop also seems to be a problem. ...
    (microsoft.public.inetserver.asp.general)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... within a cursor (Original posting ... Resume Next" in SQL Server) and I want the procedure to ... >Create table Noise ... >> A clause of the query contained only ignored words. ...
    (microsoft.public.sqlserver.fulltext)