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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | 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
 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
52326 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
52326 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
52326 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
52326 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  
 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.19 seconds. Powered By: Snitz Forums 2000