|
|
You're in the
sql server full text search
group:noise words, @@ERROR, and stop and resume indexing
sql server full text search:
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 [quoted text, click to view] "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.aspx?scid=kb;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 [quoted text, click to view] "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, > >
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 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/ [quoted text, click to view] "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. [quoted text, click to view] "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, > > > > > >
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? [quoted text, click to view] >-----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, >> >> > > >.
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.aspx?scid=kb;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/ [quoted text, click to view] "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, > >> > >> > > > > > >. > >
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/ [quoted text, click to view] "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.aspx?scid=kb;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... > > 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
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 [quoted text, click to view] "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, > >> > >> > > > > > >. > >
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 :-( [quoted text, click to view] >-----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 >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.aspx?scid=kb;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... >> > 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
Andy,
Yes, your stored proc example was highly edited and not really functional without the UDF details. As they say, the "devil is in the details" and the "empty string" may in fact be the true problem, for example: select pub_id, pr_info from pub_info where CONTAINS (pr_info, '""') select pub_id, pr_info from pub_info where CONTAINS (pr_info, '" "') Both of the above FTS queries fail with error Msg 7619. The key to successfully using CONTAINS* or FREETEXT* queries is knowing not only how to use them, but knowing the textual data that you are searching against as well. This can be resolved without making the UDF "infallible", but having the details of the UDF would be helpful as well. If you don't want to make this code public, you can email it directly to me (jt-kane at comcast dot net). Removing the noise words, as well as not passing empty or null strings to SQL Server could help to make this work for you. Thanks, John -- SQL Full Text Search Blog http://spaces.msn.com/members/jtkane/ [quoted text, click to view] "Andy Wakeling" <anonymous@discussions.microsoft.com> wrote in message
news:2a8d01c50dcd$dba38270$a401280a@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 > >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.aspx?scid=kb;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... > >> > 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
Don't see what you're looking for? Try a search.
|
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |