Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 873
  • Last Modified:

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

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
iansmcg
Asked:
iansmcg
  • 5
  • 4
2 Solutions
 
Ashok KumarCommented:
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
 
iansmcgAuthor Commented:
Any idea what this would look like? I'm fairly new to MS SQL...
0
 
Ashok KumarCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
iansmcgAuthor Commented:
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
 
Ashok KumarCommented:
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
 
iansmcgAuthor Commented:
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
 
Ashok KumarCommented:
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
 
iansmcgAuthor Commented:
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
 
Ashok KumarCommented:
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

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now