Solved

Stroed Proc not sending emails properly

Posted on 2013-02-01
9
613 Views
Last Modified: 2013-02-04
I created a stored Proc to send email in batches of 50 to people, but one oversite is that if the last group in under 50 they will not get the emails at all.  tried to corret but alls I get is errors theat @Receipt is missing or it does not send at all becuase of the @Send variable must be greater than 50 to send it.  So how can I resolve so i dont send out 1200 emails out of sql server but reduced to about 24 approx.

Select @ii = COUNT(RowId) from #TempMail;
      set @i = 1;
      Set @Send = 1
      set @Receipt = ''
            While @i <= @ii
              BEGIN
            Select @Name = Name, @Email = Mail from #TempMail where Rowid = @i;
            IF @Send < 3 and @i < @ii
                BEGIN
                   set @Receipt = @Receipt + @Email + ';'
                 END
            ELSE
                 BEGIN
            --- do send the email here ...
            set @Body = 'Test of Annual Hr'
            set @Subject = 'Test of Annual HR'
                                           
            EXEC   msdb.dbo.sp_send_dbmail
             @recipients = @Receipt,
             @body = @Body,
             @subject = @Subject,
             @profile_name = 'Testmail'
                                            
            Set @Send = 1;
            Set @Receipt = '';
             END
           set @Send = (@Send + 1)
           set @i = (@i + 1)
END
Drop table #TempMail;
0
Comment
Question by:kdeutsch
  • 5
  • 3
9 Comments
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38844290
why don't you use my method as i provided on your last attempt,,,,

which doesn't loop to get the 50 but processes them in batches of 50 at a time...

and will deal with any number in the last batch

ID: 38843858
0
 

Author Comment

by:kdeutsch
ID: 38844662
Ok, I will go back and try it out, just used 1st solution becuase thats what my question and error was about, given everyone thier fair due.
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 38849628
From reading your code it appears that if @Send < 3 and @i < @ii then no email is sent out.
Is that what you intended?
0
 

Author Comment

by:kdeutsch
ID: 38851158
lowfatSpread,

Ok tried the code you had posted in the question befor ethis one and it errors out on me,
it gieves  the following error

Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 260
At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".

(1 row(s) affected)


Here is the code
Select @Subject = 'AnualHR Test' , @Body = @Name,@receipt=''
               
      Select @ii = COUNT(RowId) ,@i=1,@send=1 from #TempMail;
            While @i <= @ii
                  BEGIN
      Select      @receipt=@receipt+';'+@email from #TempMail
      where      Rowid between  @i and @i+49
                        
      Select @Name = Name, @i = @i+50 from #TempMail where Rowid = @i;
             
             select @receipt=stuff(@receipt,1,1,'')     -- remove first semi-colon
                               
                   EXEC   msdb.dbo.sp_send_dbmail
                   @recipients = @Receipt,
                   @body = @Body,
                   @subject = @Subject,
                   @profile_name = 'Testmail'
                                           
                  Set @Receipt = '';
                  END
      Drop table #TempMail;
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 38851204
try with some debugging?

Select @Subject = 'AnualHR Test' , @Body = @Name,@receipt=''
               
      Select @ii = COUNT(RowId) ,@i=1,@send=1 from #TempMail;
            While @i <= @ii
                  BEGIN
      Select      @receipt=@receipt+';'+@email from #TempMail
      where      Rowid between  @i and @i+49
                       
      Select @Name = Name, @i = @i+50 from #TempMail where Rowid = @i;
             
             select @receipt=stuff(@receipt,1,1,'')     -- remove first semi-colon

          print @i
          print @name
         print @receipt
                               
                   EXEC   msdb.dbo.sp_send_dbmail
                   @recipients = @Receipt,
                   @body = @Name,     --<  @name ????
                   @subject = @Subject,
                   @profile_name = 'Testmail'
                                           
                  Set @Receipt = '';
                  END
      Drop table #TempMail;
0
 

Author Comment

by:kdeutsch
ID: 38851278
HI,
It prints out

51
My Name

Then error
Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 260
At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".

(1 row(s) affected)
0
 

Author Comment

by:kdeutsch
ID: 38851294
Ok figured it out

I replaced

 Select      @receipt=@receipt+';'+ @email from #TempMail
where      Rowid between  @i and @i+49

With
 Select      @receipt=@receipt+';'+ Mail from #TempMail
 where      Rowid between  @i and @i+49

@email was not being set on temp mail it was mail.  It works now.
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38851300
what is/how is rowid defined?

what does select min(rowid) from #TempMail   return?
0
 

Author Closing Comment

by:kdeutsch
ID: 38851315
Thanks works great.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

There have been several questions about Large Transaction Log Files in SQL Server 2008, and how to get rid of them when disk space has become critical. This article will explain how to disable full recovery and implement simple recovery that carries…
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video I am going to show you how to back up and restore Office 365 mailboxes using CodeTwo Backup for Office 365. Learn more about the tool used in this video here: http://www.codetwo.com/backup-for-office-365/ (http://www.codetwo.com/ba…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now