sql server 2005 stored procedure unexpected behaviour
- by user283405
i have written a simple stored procedure (run as job) that checks user subscribe keyword alerts. when article
posted the stored procedure sends email to those users if the subscribed keyword matched with article title.
One section of my stored procedure is:
OPEN @getInputBuffer
FETCH NEXT
FROM @getInputBuffer INTO @String
WHILE @@FETCH_STATUS = 0
BEGIN
--PRINT @String
INSERT INTO #Temp(ArticleID,UserID)
SELECT A.ID,@UserID
FROM CONTAINSTABLE(Question,(Text),@String) QQ
JOIN Article A WITH (NOLOCK) ON A.ID = QQ.[Key]
WHERE A.ID > @ArticleID
FETCH NEXT
FROM @getInputBuffer INTO @String
END
CLOSE @getInputBuffer
DEALLOCATE @getInputBuffer
This job run every 5 minute and it checks last 50 articles.
It was working fine for last 3 months but a week before it behaved unexpectedly.
The problem is that it sends irrelevant results.
The @String contains user alert keyword and it matches to the latest articles using Full text search. The normal execution time is 3 minutes but its execution time
is 3 days (in problem).
Now the current status is its working fine but we are unable to find any reason why it sent irrelevant results.
Note: I am already removing noise words from user alert keyword.
I am using SQL Server 2005 Enterprise Edition.
