SQL Server Performance Forum – Threads Archive

Keyword search failing (long code, not urgent)

Hi all, Could anyone explain why the following code returns this error message?? Thanks, Jaybee. Error: Executed as user: LBH2000sqladmin. Execution of a full-text operation failed. A clause of the query contained only ignored words. [SQLSTATE 42000] (Error 7619). The step failed. Code: ———————————————————————————————– – Class:Showhomes bespoke notification process – Narrative:send E-mails relating to current notifications – Owners:Notification – History:16/05/2005 LP created –20/07/2005 LP @MailURL modification –20/06/2006 LP single quote error update –07/07/2006 LP noise word and quote function – Copyright: (C) Quicksilver 1986 – 2005 All rights reserved. ———————————————————————————————– CREATE PROCEDURE dbo.up_EmailProcess @ProcessPeriod int = 0, @MailOnly bit = 0, @Remote bit = 0 AS SET ANSI_DEFAULTS ON SET NOCOUNT ON BEGIN DECLARE @BaseURL varchar(100) DECLARE @BaseSite varchar(100) DECLARE @SubSite varchar(100) DECLARE @Directory varchar(100) DECLARE @PDFDirectory varchar(100) DECLARE @MailURL varchar(100) DECLARE @LINKEDSERVER varchar(100) DECLARE @TSQL varchar(2000) DECLARE @OPENQUERY varchar(2000) DECLARE @EXEC varchar(2000) DECLARE @iMsg int DECLARE @hr int DECLARE @source varchar(255) DECLARE @description varchar(500) DECLARE @output varchar(1000) DECLARE @ProcessDate smalldatetime DECLARE @Process varchar(50) DECLARE @ProcessCount int BEGIN TRANSACTION – Specificly update these details for new server implementation SELECT @MailURL = ‘showplan.Homestead.gov.uk’, @BaseURL = ‘123.45.6.789’ — LBH – SELECT @BaseURL = ‘LP-W2K3-‘Homestead’, @MailURL = ‘LP-W2K3-‘Homestead’– quicksand SELECT @BaseSite = ”, @SubSite = ”, @Directory = ‘Public%20Docs’, @PDFDirectory = ‘Public%20PDF’, @ProcessDate = getdate(), @Process = ‘Email' –seperate SQL server use linked server local sets database name SELECT @LINKEDSERVER = ‘sps-meetingdocs' INSERT INTO ProcessAudit VALUES(@ProcessDate, @Process,0,0,NULL) –seperate SQL server use linked server and OPENQUERY IF @Remote = 0 BEGIN SELECT @OPENQUERY = ‘INSERT INTO SearchResults SELECT UserID,[ID],SearchID,SearchKeywords, Escaped, Period, Expires, MeetingDate, DocumentType ,cast(DocumentTitle as varchar) as [DocumentTitle], Planning,Document,Extension,TimeCreated,CommitteeID,cast(Ward as varchar(8000)) as Ward, Item, NULL FROM [‘ + @LINKEDSERVER + ‘].dbo.' END ELSE BEGIN SELECT @OPENQUERY = ‘INSERT INTO SearchResults SELECT UserID,[ID],SearchID,SearchKeywords, Escaped, Period, Expires, MeetingDate, DocumentType ,cast(DocumentTitle as varchar) as [DocumentTitle], Planning,Document,Extension,TimeCreated,CommitteeID,cast(Ward as varchar(8000)) as Ward, Item, NULL FROM OPENQUERY' END IF @MailOnly = 0 BEGIN BEGIN TRANSACTION EXEC up_UpdateDecisionMakers @MailOnly, @Remote, @BaseURL, @BaseSite, @SubSite, @LINKEDSERVER TRUNCATE TABLE SearchResults COMMIT TRANSACTION END DECLARE @CurrentUserID uniqueidentifier DECLARE @CurrentEmail varchar(100) DECLARE @CurrentUserTS timestamp DECLARE @CurrentNotificationID uniqueidentifier DECLARE @LastNotificationID uniqueidentifier DECLARE @CurrentNotificationTS timestamp DECLARE @Since smalldatetime DECLARE @Keywords varchar(250) DECLARE @Escaped bit DECLARE @Period int DECLARE @CommitteeID float DECLARE @CurrentIdent int –mail detail DECLARE @html1 varchar(8000) DECLARE @html2 varchar(1000) DECLARE @intro1 varchar(1000) DECLARE @intro2 varchar(1000) DECLARE @intro3 varchar(1000) DECLARE @footer1 varchar(2000) DECLARE @footer2 varchar(2000) DECLARE @Type VarChar(255) — MAPI Type DECLARE @Recipients VarChar(255)– Semicolon-separated list DECLARE @CC VarChar(255)– Semicolon-separated list DECLARE @Subject VarChar(255) DECLARE @From varchar(255) DECLARE @Message varchar(8000) DECLARE @Message2 varchar(8000) DECLARE @ptr binary(16) DECLARE @datalen int DECLARE @notifyCount int DECLARE @Header VarChar(8000) DECLARE @Contents VarChar(8000) DECLARE @Contents2 VarChar(8000)DECLARE @ErrorMessage VarChar(255) DECLARE @SearchID int DECLARE @ListName varchar(100) DECLARE @SearchKeywords varchar(1000) DECLARE @Expires smalldatetime DECLARE @MeetingDate smalldatetime DECLARE @DocumentType varchar(255) DECLARE @Meeting varchar(8000) DECLARE @DocumentTitle varchar(255) DECLARE @PlanningApplicationNumber varchar(100) DECLARE @Document varchar(255) SELECT @html1 = ‘<HTML><HEAD></HEAD><BODY ‘ + ‘Style="’ + ‘font-family: arial;font-size:12’ + ‘"’ + ‘>’, @html2 = ‘</BODY></HTML>’, @intro1 = ‘This email is sent to ‘, @intro2 = ‘ and advises that content has been published on the Homestead website, which matches areas of interest you have registered according to the frequency you have requested i.e. weekly or monthly.&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />>’, @intro3 = ‘Follow the links below to access the documents:&lt<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />>’, @footer1 = ‘You cannot change the parameters of these notification(s), but if required, you can delete and re-create them at any time time by ‘, @footer2 = ‘ This email is sent from an un-monitored mailbox: please do not reply to it. If you require any assistance relating to meeting documents, please email <A href<a target="_blank" href=mailto:[email protected]>[email protected]</A>>mailto:[email protected]>[email protected]</A></a>, or telephone 020 1111 1111 Monday to Friday between 9:00am and 5:00pm.' SELECT @type = NULL SELECT @Recipients = ” SELECT @From = ‘ [email protected]‘ SELECT @CC = ” SELECT @Subject = ‘Homestead: Notification of content' SELECT @Message = ” SELECT @Message2 = ” IF @MailOnly = 0 BEGIN SELECT @Since = CASE @ProcessPeriod WHEN -1 THEN dateadd(hour,-1,getdate()) WHEN 0 THEN dateadd(week,-1,getdate()) ELSE dateadd(month,-1,getdate()) END – Get minimum value SELECT @CurrentUserTS = MIN(US.[Timestamp]) FROM [User] US WHERE CHARINDEX(‘@’,US.Email) > 0 AND CHARINDEX(‘.’,US.Email) > 0 – Get first user SELECT @CurrentUserID = UserID, @CurrentEmail = Email FROM [User] US WHERE [Timestamp] = @CurrentUserTS WHILE @CurrentUserID IS NOT NULL BEGIN –get minimum notification SELECT @CurrentNotificationTS = MIN(NF.[Timestamp]) FROM Notification NF WHERE UserID = @CurrentUserID AND ExpiryDate >= getdate() AND Period = @ProcessPeriod –get first notification SELECT @CurrentNotificationID = NF.ID, @CommitteeID = CommitteeID, @Keywords = Keywords, @Escaped = Escaped, @Period = Period, @Expires = ExpiryDate FROM Notification NF WHERE NF.[Timestamp] = @CurrentNotificationTS – for each notification for this user do search WHILE @CurrentNotificationID IS NOT NULL BEGIN SELECT @Keywords = dbo.EscapedCharacter(@Keywords,’/’) SELECT @Keywords = dbo.SPSCleanNoise(@Keywords) –seperate SQL server use linked server and OPENQUERY IF @Remote = 0 BEGIN SELECT @TSQL = ‘SearchResults(”’ + cast(@CurrentUserID as varchar(64)) + ”’,”’ + cast(@CurrentNotificationID as varchar(64)) + ”’,’ + char(39) + @BaseURL + char(39) + ‘,’ + char(39) + @BaseSite + char(39) + ‘,’ + char(39) + ‘ ‘ + char(39) + ‘,’ + char(39) + ‘Public Docs’ + char(39) + ‘,’ + char(39) + ‘Public Docs’ + char(39) + ‘,’ + char(39) + REPLACE(@keywords,char(39),char(39)+char(39)) + char(39) + ‘,’ + char(39) + cast(@CommitteeID as varchar) + char(39) + ‘,’ + char(39) + cast(@Escaped as varchar) + char(39) + ‘,’ + char(39) + cast(@Period as varchar) + char(39) + ‘,’ + char(39) + cast(@Expires as varchar) + ”” + ‘,’ + char(39) + cast(@Since as varchar) + ”” + ‘)’ SELECT @CurrentNotificationID = NULL, @EXEC = @OPENQUERY + @TSQL END ELSE BEGIN SELECT @TSQL = ‘SELECT * FROM dbo.SearchResults(”’ + ”” + cast(@CurrentUserID as varchar(64)) + ”” + ”’,”’ + ”” + cast(@CurrentNotificationID as varchar(64)) + ”” + ”’,’ + char(39) + ”” + @BaseURL + ”” + char(39) + ‘,’ + char(39) + ”” + @BaseSite + ”” + char(39) + ‘,’ + char(39) + ”’ ”’ + char(39) + ‘,’ + char(39) + ”’Public Docs”’ + char(39) + ‘,’ + char(39) + ”’Public Docs”’ + char(39) + ‘,’ + char(39) + ”” + REPLACE(@keywords,char(39),char(39)+char(39)) + ”” + char(39) + ‘,’ + char(39) + ”” + cast(@CommitteeID as varchar) + ”” + char(39) + ‘,’ + char(39) + ”” + cast(@Escaped as varchar) + ”” + char(39) + ‘,’ + char(39) + ”” + cast(@Period as varchar) + ”” + char(39) + ‘,’ + char(39) + ”” + cast(@Expires as varchar) + ”” + ”” + ‘,’ + char(39) + ”” + cast(@Since as varchar) + ”” + ”” + ‘)’ SELECT @CurrentNotificationID = NULL, @EXEC = @OPENQUERY + ‘([‘ + @LINKEDSERVER + ‘],’ + char(39) + @TSQL + char(39) + ‘)' END EXEC(@EXEC) SELECT @CurrentNotificationTS = MIN(NF.[Timestamp]) FROM Notification NF WHERE UserID = @CurrentUserID AND NF.[Timestamp] > @CurrentNotificationTS AND ExpiryDate >= getdate() AND Period = @ProcessPeriod –get next notification SELECT @CurrentNotificationID = NF.ID, @CommitteeID = CommitteeID, @Keywords = Keywords, @Escaped = Escaped, @Period = Period, @Expires = ExpiryDate FROM Notification NF WHERE NF.[Timestamp] = @CurrentNotificationTS END SELECT @CurrentUserTS = MIN(US.[Timestamp]) FROM [User] US WHERE CHARINDEX(‘@’,US.Email) > 0 AND CHARINDEX(‘.’,US.Email) > 0 AND US.[Timestamp] > @CurrentUserTS – Get next user SELECT @CurrentUserID = NULL, @CurrentEmail = NULL SELECT @CurrentUserID = UserID, @CurrentEmail = Email FROM [User] US WHERE [Timestamp] = @CurrentUserTS END END –mail only CREATE TABLE #tempUser ( Ident int identity(1,1), UserID uniqueidentifier, Email varchar(100) ) CREATE TABLE #tempNotify ( [ID] uniqueidentifier NULL, SearchID float NULL, SearchKeywords varchar(400) NULL, Escaped bit NULL, Period int NULL, Expires smalldatetime NULL, [Timestamp] timestamp ) INSERT INTO #tempUser SELECT DISTINCT SR.UserID, US.Email FROM SearchResults SR INNER JOIN [User] US ON SR.UserID = US.UserID –audit SELECT @ProcessCount = count(0) FROM #tempUser UPDATE ProcessAudit SET Estimated = @ProcessCount WHERE ProcessDate = @ProcessDate AND Process = @Process SELECT @ProcessCount = 0 –get first user SELECT @CurrentUserID = UserID, @CurrentEmail = Email, @CurrentIdent = 1 FROM #tempUser WHERE Ident = 1 –for each user construct mail WHILE @CurrentUserID IS NOT NULL BEGIN TRUNCATE TABLE #tempNotify SELECT @CurrentUserTS = NULL, @Message = @html1 + @intro1 + ISNULL(@CurrentEmail,’unknown’) + @intro2 + CHAR(9) + CHAR(13) + CHAR(9) + CHAR(13) + @intro3, @Header = ”, @Contents = ”, @Message2 = ”, @Contents2 = ” UPDATE [User] SET LastEmail = ” WHERE UserID = @CurrentUserID SELECT @ptr = textptr([User].LastEmail) FROM [User] WHERE UserID = @CurrentUserID UPDATETEXT [User].LastEmail @ptr null 0 @Message –get documents for user INSERT INTO #tempNotify SELECT DISTINCT [ID],SearchID,SearchKeywords,Escaped, Period,Expires,NULL FROM SearchResults WHERE UserID = @CurrentUserID IF @@ROWCOUNT > 0 BEGIN SELECT @Contents = ‘<TABLE ‘ + ‘Style="’ + ‘font-family: arial;font-size:12’ + ‘"’ + ‘><TR ‘ + ‘Style="’ + ‘font-weight: bold’ + ‘"’ + ‘><TD ‘ + ‘Width="’ + ‘200’ + ‘"’ + ‘>Meeting Name</TD><TD ‘ + ‘Width="’ + ‘100’ + ‘"’ + ‘>Keyword(s)</TD><TD ‘ + ‘Width="’ + ‘100’ + ‘"’ + ‘>Expires</TD><TD ‘ + ‘alighn:right;width="’ + ‘150’ + ‘"’ + ‘>Link</TD></TR>' UPDATETEXT [User].LastEmail @ptr null 0 @Contents ENDSELECT @CurrentUserTS = MIN([Timestamp]) FROM #tempNotify WHILE @CurrentUserTS IS NOT NULL BEGIN SELECT @CurrentNotificationID = [ID], @SearchID = cast(SearchID as int), @ListName = ISNULL(DML.ListName,”), @SearchKeywords = ISNULL(TN.SearchKeywords,”), @Escaped = TN.Escaped, @Period = TN.Period, @Expires = TN.Expires FROM #tempNotify TN LEFT JOIN DecisionMakersList DML ON cast(TN.SearchID as integer) = DML.ListID WHERE TN.[Timestamp] = @CurrentUserTS SELECT @notifyCount = COUNT(Document) WHERE [ID] = @CurrentNotificationID SELECT @Contents = ‘<TR><TD>’ + CASE @ListName WHEN ” THEN ‘Not Specified’ ELSE @ListName END + ‘</TD><TD>’ + CASE @SearchKeywords WHEN ” THEN ‘Not Specified’ ELSE CASE @Escaped WHEN 0 THEN REPLACE(@SearchKeywords,char(39), char(34)) ELSE REPLACE(REPLACE(@SearchKeywords,char(39), char(34)),char(34),”) END END + ‘</TD><TD>’ + CONVERT(varchar,@Expires,106) + ‘</TD><TD><A HREF<a target="_blank" href=http://’>http://'</a> + @MailURL + CASE @BaseSite WHEN ” THEN ” ELSE ‘/’ + @BaseSite END + ‘/Shared%20Documents/Search_Since.aspx?_Committee=’ + cast(@SearchID as varchar) + ‘&_Keyword%28s%29=’ + CASE @Escaped WHEN 0 THEN REPLACE(REPLACE(@SearchKeywords,’ ‘, ‘%20′),char(39),’%22′) ELSE REPLACE(REPLACE(REPLACE(@SearchKeywords,’ ‘, ‘%20′),char(39),’%22’),char(34),char(34)) END + ‘&_Since=’ + CASE @Period WHEN 0 THEN REPLACE(CONVERT(varchar(11),dateadd(week,-1,getdate()),106),’ ‘, ‘%20′) ELSE REPLACE(CONVERT(varchar(11),dateadd(month,-1,getdate()),106),’ ‘, ‘%20’) END + ‘>’ + cast(ISNULL(@notifyCount,0) as varchar) + ‘ document(s)</A></TD></TR>' UPDATETEXT [User].LastEmail @ptr null 0 @Contents SELECT @CurrentUserTS = MIN([Timestamp]) FROM #tempNotify WHERE [Timestamp] > @CurrentUserTS END SELECT @Contents2 = ‘</TABLE> ’ + @footer1 + ‘<A HREF<a target="_blank" href=http://’>http://'</a> + @MailURL + CASE @BaseSite WHEN ” THEN ” ELSE ‘/’ + @BaseSite END + ‘/Shared%20Documents/Notify_Update.aspx?Action=Delete&User=’ + cast(@CurrentUserID as varchar(64)) + ‘&ID=’ + cast(@CurrentNotificationID as varchar(64)) + ‘>clicking here.</A>’ + @footer2 + @html2 UPDATETEXT [User].LastEmail @ptr null 0 @Contents2 select @datalen = ISNULL(DATALENGTH(LastEmail),0) FROM [User] WHERE UserID = @CurrentUserID IF @datalen > 7999 BEGIN SELECT @Message = SUBSTRING(LastEmail, 1, 7999) FROM [User] WHERE UserID = @CurrentUserID SELECT @Message2 = SUBSTRING(LastEmail, 8000, @datalen – 7999) FROM [User] WHERE UserID = @CurrentUserID END BEGIN SELECT @Message = SUBSTRING(LastEmail, 1, 7999) FROM [User] WHERE UserID = @CurrentUserID END SELECT @Recipients = @CurrentEmail – SELECT ‘up_send_cdosysmail ‘ + ”” + @From + ”’,”’ + @Recipients + ”’,”’ + @Subject + ”’,”’ + @Message + @Message2 + ”” EXEC(‘up_send_cdosysmail ‘ + ”” + @From + ”’,”’ + @Recipients + ”’,”’ + @Subject + ”’,”’ + @Message + @Message2 + ””) –get next user SELECT @CurrentIdent = @CurrentIdent + 1, @CurrentUserID = NULL, @ProcessCount = @ProcessCount + 1 UPDATE ProcessAudit SET Actual = @ProcessCount WHERE ProcessDate = @ProcessDate AND Process = @Process SELECT @CurrentUserID = UserID, @CurrentEmail = Email FROM #tempUser WHERE Ident = @CurrentIdent END UPDATE ProcessAudit SET ProcessFinished = getdate() WHERE ProcessDate = @ProcessDate AND Process = @Process DROP TABLE #tempUser DROP TABLE #tempNotify COMMIT TRANSACTION RETURN 0 END GO

Check the following articles which may help you… http://www.databasejournal.com/features/mssql/article.php/3454281
http://dbforums.com/t968373.html
http://www.sqlservercentral.com/columnists/jwiner/20010422025723_1.asp
MohammedU.
Moderator
SQL-Server-Performance.com

]]>