Solved

Stroed Proc not sending emails properly

Posted on 2013-02-01
9
643 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

 

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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
sql2016-WIn10: standard,for SQL servc-account.. 51 55
Database Owner 3 45
denied execute as 13 57
SQL Server for XML PATH giving wrong results. 6 59
I have written a PowerShell script to "walk" the security structure of each SQL instance to find:         Each Login (Windows or SQL)             * Its Server Roles             * Every database to which the login is mapped             * The associated "Database User" for this …
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

734 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