Solved

Trying to use sp_send_dbmail to loop through a query and send email for each record it finds.

Posted on 2008-10-14
11
836 Views
Last Modified: 2012-06-27
I am attempting to write a query that allows me to send a message to a user based on a query result.

I've got a table to Transactions (PPtblTransactions) that holds transactions that are entered into the database. Each record has an EMAIL, EXDATE and STATUS field. I want to send a message to each EMAIL address when the status = '0' and the EXDATE is within one day. I need to be able to schedule this and run at specific times

So here's what I've got so far. I've not put a restriction in for the 1 day limit at this point.

begin
set nocount on
select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
    @recipients = '<<Not sure how to insert the EMAIL field here>>',
    @query = 'select * from MCGSQL.dbo.PPtblTransactions' ,
    @subject = 'Unprocessed Order',
    @attach_query_result_as_file = 0 ;
end
end

Any help would be great.
0
Comment
Question by:iansmcg
[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
  • 4
11 Comments
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 22717976
since the Email address are stored in table :PPtblTransactions, u may run thro' a cursor to concatenate all the emailaddress and store it in a variable say @emailaddress and then set the
EXEC msdb.dbo.sp_send_dbmail
     @recipients =@emailaddress

hope this help you.
0
 

Author Comment

by:iansmcg
ID: 22717979
Any idea what this would look like? I'm fairly new to MS SQL...
0
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 22718001
i have a table tbusers which store the emailid of users,
i have created a curosr to concatenate emailaddress with semicolon as delimiter.
the variable @concat will have all the emailid's once the loop completes

declare @emailAddress varchar(50)
declare @concat varchar(1000)

declare Maillist cursor
for select distinct top 5 Email from tbusers
      OPEN Maillist    
FETCH FROM Maillist into @emailAddress

WHILE NOT (@@fetch_status=-1)    
 BEGIN    
        IF len(@concat)>0  
            begin
                  select @concat = @concat +';'+@emailAddress
            end
        else
            begin
                  select @concat = @emailAddress
            end
        FETCH NEXT FROM Maillist INTO @emailAddress    
 END    
   
CLOSE maillist    
DEALLOCATE Maillist  

begin
set nocount on
select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
    @recipients = @concat,
    @query = 'select * from MCGSQL.dbo.PPtblTransactions' ,
    @subject = 'Unprocessed Order',
    @attach_query_result_as_file = 0 ;
end
end

print @concat
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:iansmcg
ID: 22718012
I'm still not sure how this is going to work... You're saying to create a table called tbusers and insert the email addresses there? The user is specifying the address when the record is written to PPtblTransactions... So I need to insert it into tbusers at the same time? Is there not another way?
0
 
LVL 6

Expert Comment

by:Ashok Kumar
ID: 22718040
the table tbusers was an example to get details and concatenate
you may make use of ur Table : select distinct Email from PPtblTransactions to retrive the details of the emailid
instead of this statement select distinct top 5 Email from tbusers
0
 

Author Comment

by:iansmcg
ID: 22718083
That get's me almost there. I'm trying to only send the user the details of their own transaction.

As the code is today each user gets every other users records in the body of the message. Is there a way to prevent this?

declare @emailAddress varchar(50)
declare @concat varchar(1000)

declare Maillist cursor
for select distinct EMAIL from PPtblTransactions where STATUS = '0'
      OPEN Maillist    
FETCH FROM Maillist into @emailAddress

WHILE NOT (@@fetch_status=-1)    
 BEGIN    
        IF len(@concat)>0  
            begin
                  select @concat = @concat +';'+@emailAddress
            end
        else
            begin
                  select @concat = @emailAddress
            end
        FETCH NEXT FROM Maillist INTO @emailAddress    
 END    
   
CLOSE Maillist    
DEALLOCATE Maillist  

begin
set nocount on
select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
    @recipients = @concat,
    @query = 'select * from MCGSQL.dbo.PPtblTransactions' ,
    @subject = 'Unprocessed Order',
    @attach_query_result_as_file = 0 ;
end
end

print @concat
0
 
LVL 6

Accepted Solution

by:
Ashok Kumar earned 500 total points
ID: 22718160
below is the code in which avoids formation of multiple users in email address
declare @emailAddress varchar(50)
--declare @concat varchar(1000)

declare Maillist cursor
for select distinct EMAIL from PPtblTransactions where STATUS = '0'
      OPEN Maillist    
FETCH FROM Maillist into @emailAddress

WHILE NOT (@@fetch_status=-1)    
 BEGIN  
 
            begin
            set nocount on
            select TRXNMBR, EMAIL from MCGSQL.dbo.PPtblTransactions where STATUS = '0'
            if @@ROWCOUNT >0
                  begin
                        EXEC msdb.dbo.sp_send_dbmail
                        @recipients = @emailAddress,
                        @query = 'select * from MCGSQL.dbo.PPtblTransactions ' ,
                        @subject = 'Unprocessed Order',
                        @attach_query_result_as_file = 0 ;
                  end
            end  
       
        FETCH NEXT FROM Maillist INTO @emailAddress  
 END    
   
CLOSE Maillist    
DEALLOCATE Maillist
0
 

Author Comment

by:iansmcg
ID: 22721808
That doesn't change the results. I think I need to change the " @query = 'select * from MCGSQL.dbo.PPtblTransactions ' , " However I cant pass @emailAddress to sp_send_dbmail
0
 
LVL 6

Assisted Solution

by:Ashok Kumar
Ashok Kumar earned 500 total points
ID: 22728908
you may modify the @query='select * from PPtblTransactions where mail=@emailAddress' to pick up only the details of the induvidual user and send the same to the corresponding user in the form of mail.
0

Featured Post

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

751 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