iansmcg
asked on
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.PPtblTransactio ns 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.PPtblTransactio ns' ,
@subject = 'Unprocessed Order',
@attach_query_result_as_fi le = 0 ;
end
end
Any help would be great.
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.PPtblTransactio
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.PPtblTransactio
@subject = 'Unprocessed Order',
@attach_query_result_as_fi
end
end
Any help would be great.
ASKER
Any idea what this would look like? I'm fairly new to MS SQL...
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.PPtblTransactio ns where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = @concat,
@query = 'select * from MCGSQL.dbo.PPtblTransactio ns' ,
@subject = 'Unprocessed Order',
@attach_query_result_as_fi le = 0 ;
end
end
print @concat
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.PPtblTransactio
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = @concat,
@query = 'select * from MCGSQL.dbo.PPtblTransactio
@subject = 'Unprocessed Order',
@attach_query_result_as_fi
end
end
print @concat
ASKER
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?
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
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
ASKER
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.PPtblTransactio ns where STATUS = '0'
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = @concat,
@query = 'select * from MCGSQL.dbo.PPtblTransactio ns' ,
@subject = 'Unprocessed Order',
@attach_query_result_as_fi le = 0 ;
end
end
print @concat
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.PPtblTransactio
if @@ROWCOUNT >0
begin
EXEC msdb.dbo.sp_send_dbmail
@recipients = @concat,
@query = 'select * from MCGSQL.dbo.PPtblTransactio
@subject = 'Unprocessed Order',
@attach_query_result_as_fi
end
end
print @concat
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That doesn't change the results. I think I need to change the " @query = 'select * from MCGSQL.dbo.PPtblTransactio ns ' , " However I cant pass @emailAddress to sp_send_dbmail
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
EXEC msdb.dbo.sp_send_dbmail
@recipients =@emailaddress
hope this help you.