Author |
Topic |
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2013-01-31 : 09:07:54
|
trying to loop trhough a record set and send out email. But I can only send 50 emails at a time. So my regular loop works but then i put in the send variable and it does not seem to work.Select @ii = COUNT(RowId) from #TempMail; set @i = 1; While @i <= @ii BEGIN Set @Send = 1 Select @Name = Name, @Email = Mail from #TempMail where Rowid = @i; BEGIN IF @Send <= 50 set @Receipt = @Receipt + @Email + ';' set @Send = @Send + 1 ELSE --Send Email stuff set @i = (@i + 1); Set @Send = 1; set @Receipt = '' END END Drop table #TempMail; |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 09:11:42
|
whats the purpose of that additional BEGIN END inside? seems like a misplaced one to me ------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2013-01-31 : 09:28:58
|
ok took it out and it still does not work, it send everyone an email and i want to consolitate to send out 50 at a time, but its not working like such its like it skips IF @Send <= 50 and keeps on going |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 09:31:21
|
you're setting @Send =1 inside loop. then how do you think it will ever cross 50. i think it should be outside the loop------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2013-01-31 : 09:49:05
|
Hi,Ok moved it outside but now i get this.Msg 14624, Level 16, State 1, Procedure sp_send_dbmail, Line 260At least one of the following parameters must be specified. "@recipients, @copy_recipients, @blind_copy_recipients".Mail queued.Mail queued.(1 row(s) affected)it looks like the 1st email it errors out and then starts to send consecutive mails, right now it should on send 1 email with 3 people in it, but its trying to send 3 emails instead of 1. |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2013-01-31 : 09:51:59
|
Here is newest version still same email problem.Select @ii = COUNT(RowId) from #TempMail; set @i = 1; Set @Send = 1 While @i <= @ii BEGIN Select @Name = Name, @Email = Mail from #TempMail where Rowid = @i; IF @Send <= 50 BEGIN set @Receipt = @Receipt + @Email + ';' set @Send = @Send + 1 END ELSE if @Send > 50 --- do send the email here ... EXEC msdb.dbo.sp_send_dbmail @recipients = @Receipt, @body = @Body, @subject = @Subject, @profile_name = 'Testmail' set @i = (@i + 1); Set @Send = 0; set @Receipt = ''; END Drop table #TempMail;END |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 09:54:19
|
then its problem with your sp_send_dbmail call which you've not shown in posted code above and hence i cant suggest.Also make sure you dont have any records with NULL existing for Mail field in #tempmail table------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2013-01-31 : 10:17:14
|
Hi,The only thing I have not shown is the messages @body, @subject those have no effect on why it sends out 3 emails messages instead of just 1 message. |
|
|
kdeutsch
Yak Posting Veteran
83 Posts |
Posted - 2013-01-31 : 11:20:44
|
figure it out just had to put this line outside my if statement set @i = (@i + 1); |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-01-31 : 12:23:12
|
ok...good------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|