Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 loop trhough record set

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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 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.
Go to Top of Page

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
Go to Top of Page

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 MVP
http://visakhm.blogspot.com/

Go to Top of Page

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.
Go to Top of Page

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);
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-31 : 12:23:12
ok...good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
   

- Advertisement -