Solved

Stroed Proc not sending emails properly

Posted on 2013-02-01
9
605 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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

746 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

13 Experts available now in Live!

Get 1:1 Help Now