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)
 Concantenate email string within temptable loop
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 01/31/2013 :  11:26:56  Show Profile  Reply with Quote
I am tring to concantenate a string and send out less emails with multiple personnel in them. however my string never gets populated, its always empty when i look at it. so if I set this line like so
. set @Receipt = @Email + ';'

Itgets a single email and populates just fine but I want to send multiple personnel per mail and change like so but nothing is concantanated.
set @Receipt = @Receipt + @Email + ';'


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 = @Email + ';'
print @Send
print @Receipt
END
ELSE
BEGIN
--- do send the email here ...
EXEC msdb.dbo.sp_send_dbmail
@recipients = @Receipt,
@body = @Body,
@subject = @Subject,
@profile_name = 'Testmail'

Set @Send = 1;
Set @Receipt = '';
END
set @Send = (@Send + 1)
set @i = (@i + 1);
END
Drop table #TempMail;
END

jackv
Flowing Fount of Yak Knowledge

United Kingdom
2039 Posts

Posted - 01/31/2013 :  13:39:30  Show Profile  Visit jackv's Homepage  Reply with Quote
use

set @Receipt = @Receipt + @Email + ';'


Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

kdeutsch
Yak Posting Veteran

USA
83 Posts

Posted - 02/01/2013 :  09:15:07  Show Profile  Reply with Quote
hi,
Ok, I am using that as per my second paragraph but it still does not work, rather perplexed as to why this will not work. Dont want to send 1200 emails out of my sql server, would rather send out 20.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 02/01/2013 :  09:21:50  Show Profile  Reply with Quote
I see you have PRINT statements for @Send and @Receipt immediately after your concatenation statement. What are you seeing in the printed output? NULLS? If it is NULL, are you initializing @Receipt to something? If not, insert a statement at the very beginning, immediately after the declaration.
SET @Receipt = '';
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.05 seconds. Powered By: Snitz Forums 2000