SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 loop trhough record set
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 01/31/2013 :  09:07:54  Show Profile  Reply with Quote
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
52325 Posts

Posted - 01/31/2013 :  09:11:42  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 01/31/2013 :  09:28:58  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/31/2013 :  09:31:21  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 01/31/2013 :  09:49:05  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 01/31/2013 :  09:51:59  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/31/2013 :  09:54:19  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 01/31/2013 :  10:17:14  Show Profile  Reply with Quote
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

USA
83 Posts

Posted - 01/31/2013 :  11:20:44  Show Profile  Reply with Quote
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

India
52325 Posts

Posted - 01/31/2013 :  12:23:12  Show Profile  Reply with Quote
ok...good

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

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000