sql server 2005 stored procedure unexpected behaviour

Posted by user283405 on Stack Overflow See other posts from Stack Overflow or by user283405
Published on 2010-03-27T12:34:56Z Indexed on 2010/03/27 12:43 UTC
Read the original article Hit count: 370

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.

© Stack Overflow or respective owner

Related posts about sql-server-2005

Related posts about stored-procedures