?
Solved

sql Concantenating not working properly

Posted on 2013-02-01
5
Medium Priority
?
245 Views
Last Modified: 2013-02-01
So the objective is not to send out 1200 emails form my sql server but only send out 20 by concantenating the emails together, but when I run the proc below it does not concantonate them it is blank, but if I change to individual send out it works just fine.  This should work but its not working one bit, what amd I doing wrong here.  So what I am doing is counting 50 emails, concantonating them and then sending it out, but no emails get concatenated.


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 + ';'
                                                     END
                  ELSE
                        BEGIN
                      --- do send the email here ...
                   Set @Subject = 'AnualHR Test'
                  Set @Body = @Name
                               
                   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;
0
Comment
Question by:kdeutsch
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 33

Accepted Solution

by:
knightEknight earned 2000 total points
ID: 38843793
Did you initialize the @Receipt variable to '' ?  (Do this before entering the loop)

Also, make sure you declare it long enough to hold 50 addresses:

declare @Receipt varchar(8000) = ''
0
 
LVL 21

Expert Comment

by:mastoo
ID: 38843811
Yes, if you don't initialize @Receipt it will be null, and concatenating anything with null results in null
0
 
LVL 50

Expert Comment

by:Lowfatspread
ID: 38843858
more like this

declare @recipt varchar(8000)

   Select @Subject = 'AnualHR Test' 
                ,@Body = @Name
              ,@receipt=''
                
Select @ii = COUNT(RowId) ,@i=1,@send=1
  from #TempMail;
     
            While @i <= @ii
              BEGIN
             Select @receipt=@receipt+';'+@email
                from #TempMail 
            where Rowid between  @i and @i+49
             Select @Name = Name, @i = @i+50
                from #TempMail 
             where Rowid = @i;
              
             select @receipt=stuff(@receipt,1,1,'')     -- remove first semi-colon
                               
                   EXEC   msdb.dbo.sp_send_dbmail
                   @recipients = @Receipt,
                   @body = @Body, 
                   @subject = @Subject,
                   @profile_name = 'Testmail'
                                            
                  Set @Receipt = '';
                  END
           
              
      Drop table #TempMail;

Open in new window

0
 

Author Comment

by:kdeutsch
ID: 38843979
All,

Well, it was that i did not inialize the variable @Receipt, I called it out but forgot to inialize it.  But while looking at this here is my problem I want to send in groups of 50, but what happens if the last group is below 50, those personnel will not get the email, so this will really not work as some personnel will not get the email.
0
 

Author Closing Comment

by:kdeutsch
ID: 38844066
Thanks, thought I initalized and totally over looked it.
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

After restoring a Microsoft SQL Server database (.bak) from backup or attaching .mdf file, you may run into "Error '15023' User or role already exists in the current database" when you use the "User Mapping" SQL Management Studio functionality to al…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

764 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question