| Author |
 Topic  |
|
|
kdeutsch
Yak Posting Veteran
USA
75 Posts |
 Posted - 01/31/2013 : 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
India
47173 Posts |
Posted - 01/31/2013 : 09:11:42
|
whats the purpose of that additional BEGIN END inside? seems like a misplaced one to me
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
kdeutsch
Yak Posting Veteran
USA
75 Posts |
Posted - 01/31/2013 : 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
India
47173 Posts |
Posted - 01/31/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
kdeutsch
Yak Posting Veteran
USA
75 Posts |
Posted - 01/31/2013 : 09:49:05
|
Hi,
Ok moved it outside but now i get this.
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".
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
USA
75 Posts |
Posted - 01/31/2013 : 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
India
47173 Posts |
Posted - 01/31/2013 : 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 MVP
http://visakhm.blogspot.com/
|
|
|
|
kdeutsch
Yak Posting Veteran
USA
75 Posts |
Posted - 01/31/2013 : 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
USA
75 Posts |
Posted - 01/31/2013 : 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
India
47173 Posts |
Posted - 01/31/2013 : 12:23:12
|
ok...good
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
loop trhough record set
