Loop problem in stored procedure

Results 1 to 2 of 2

Thread: Loop problem in stored procedure

  1. #1
    Join Date
    Dec 1969

    Default Loop problem in stored procedure

    Hi,<BR><BR>I am trying to use stored procedure and SQL mail to send out email alert to selected users in the SQL7 DB. When I tested the following code it only sent out one email (to the first record in the table). How do I get it to loop through all the records and send out batch emails?<BR><BR>Code : <BR><BR><BR>CREATE Procedure sn_SMTPMail <BR><BR>AS <BR>SET nocount on <BR>declare @oMail int --Object reference <BR>declare @resultcode int <BR>EXEC @resultcode = sp_OACreate &#039;CDONTS.NewMail&#039;, @oMail OUT <BR>if @resultcode = 0 <BR>BEGIN <BR>DECLARE @firstname varchar(30)<BR>DECLARE @email varchar(100)<BR><BR>DECLARE cur CURSOR FOR Select FIRSTNAME,EMAIL from tb_user where STATUS = 1 <BR>OPEN cur<BR>FETCH NEXT FROM cur INTO @firstname ,@email<BR><BR>WHILE @@FETCH_STATUS = 0 BEGIN<BR>EXEC @resultcode = sp_OASetProperty @oMail, &#039;From&#039;, &#039;info@abcde.com&#039;<BR>EXEC @resultcode = sp_OASetProperty @oMail, &#039;To&#039;, @email<BR>EXEC @resultcode = sp_OASetProperty @oMail, &#039;Subject&#039;, &#039;Account status&#039;<BR>EXEC @resultcode = sp_OASetProperty @oMail, &#039;Body&#039;, &#039;Hi &#039; + @firstname +&#039;, &#039; +&#039;Your account has expired. Please Take Appropriate Action&#039;<BR>EXEC @resultcode = sp_OAMethod @oMail, &#039;Send&#039;, NULL <BR>EXEC sp_OADestroy @oMail<BR>FETCH NEXT FROM cur INTO @firstname ,@email<BR>END<BR>CLOSE cur<BR>DEALLOCATE cur<BR>END <BR>SET nocount off<BR>GO<BR><BR>Many thanks<BR><BR>James

  2. #2
    Join Date
    Dec 1969

    Default You are destroying the object...

    ...after first time through and you never re-create it.<BR><BR>I *think* you simply need to move the line<BR> EXEC @resultcode = sp_OACreate &#039;CDONTS.NewMail&#039;, @oMail OUT <BR>to right after the WHILE line.<BR><BR>Because you do need to create a new object for each email, per the CDONTS spec.<BR><BR>Not to ask silly questions, but... Why do you bother setting the @resultcode in all those lines when you never use it??? <BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts