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
823 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
  • 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
 

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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now