Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
853 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

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 2000 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 2000 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…

670 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