Solved

Stroed Proc not sending emails properly

Posted on 2013-02-01
9
615 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
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 

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
 
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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
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.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …

776 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