April 5, 2009 at 12:09 pm
Hi,
I have been given the task of setting up alerts that send e-mail to employees that have outstanding contracts to review before a certain date. so far I have managed the following:
_______________________________________________________________
Declare @unknowns as int
set @unknowns =
(select count (*)from purchline
join INVENTDIMCOMBINATION on purchline.inventdimid = INVENTDIMCOMBINATION.inventdimid
where INVENTDIMCOMBINATION.configid = 'Unknown' and datediff(day,getdate(),purchline.vctmovestartdate)>=7)
If @unknowns > 0
begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'Test',
@recipients = 'Test@test.com,
@Query = 'SELECT DISTINCT purchid as "Purchase Contract Number" from a41gletes.dbo.purchline join a41gletes.dbo.INVENTDIMCOMBINATION on purchline.inventdimid = INVENTDIMCOMBINATION.inventdimid
where INVENTDIMCOMBINATION.configid = "Unkown" and datediff(day,getdate(),purchline.vctmovestartdate)>=7 order by PURCHID DESC ',
@Subject = 'Contracts with Unknown Configuration',
@Body = 'The Following Contracts need to be moved within 7 Days of this message and still has a unknown combination, please review these contracts:'
end
_____________________________________________________________________________________________________________
This works well so far but what I need to do is do this for each employee that responsible for these contracts .I can probably get these database but I have 2 problems that I can see:
1. How to I get the e-mail address from the table to the @recipients to send them an e-mail
2. how to I do this for every instance. i.e there will be more that one person responsible for the contracts.
I hope this is clear, please don't hesitate to reply requesting more info. Any help appriciated.
Thanks
Jason
April 5, 2009 at 4:40 pm
1. To get the email address from a table just do a DECLARE and SELECT statement to pull the information like you did to see if the email needs to be set and set it equal to the @Recipient variable.
2. So you need the email to go to multiple people? You can enter in multiple email address in the @Recipient variable separated by ";".
When you have the list of email addresses you can concatenate them like following to get a list.
DECLARE @T AS Table (EmailAdd varchar(255))
INSERT INTO @T
SELECT 'email1@shop.com' UNION ALL
SELECT 'email1@shop.com'
SELECT EmailAdd + ';'
FROM @T
FOR XML PATH('')
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
April 6, 2009 at 7:30 am
Just a sidenote... we are sending lots of mail checks to users, and we have found it best to separate the logic of the check and the action of sending the mail.
What I mean is that we have the checks implemented as views (so that the view can be accessed also from Intranet and other applications and return precisely the same rows as in the mail check run at the same time), and the actual sending is implemented in a stored procedure with a standardized design:
- check whether view contains rows, if not, do nothing
- find addresses of recipients and concatenate them into one variable
- select the info from the view and send mail
Stored procedure is then executed from SQL Agent as often as necessary (in our case daily, sometimes several times a day).
Stored procedure has no parameters and the mail contains the name of stored procedure, which means if there is a need to send a check outside normal schedule, you just run EXEC procedure_name and everyone gets what they need.
April 7, 2009 at 1:56 pm
thanks guys
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply