a little help needed for a programme
-
Hi,
I'm writing a procedure that will over night check whether certain records need to be contacted by our account team and then automatically e-mail the results to said account team.
I've researched and found something that will do the e-mail section but have fallen over slightly in trying to get the contents of the e-mail to be dynamic. Basically I want it to read:
The following 3 people need contacting today
Mr Bell
Mr Hughes
Mrs Chaps.
Could anyone give me a helping hand on how to do this?
-
I do not know your tables to give you a specific suggestion. I would search this site for "dynamic" SQL. I would especially suggest you pay attention to the dangers of dynamic SQL, (especially with an email service and potential outside interacations - it has a huge potential for major damage if someone gets a hold of how it is running).
Kenneth Wilhelmsson posted the link to the article that explains the problem - here it is.
Reasons to be reluctant or/and mindful of dynamic SQL can be found here. Do read it. http://www.sommarskog.se/dynamic_sql.html 'The Curse and Blessings of Dynamic SQL'
If you lock down this area, dynamic SQL will be an easy solution to your request.
I wasn't born stupid - I had to study.
-
I really doubt you would have to resort to dynamic SQL just in order to be able to mail a number of random adresses, or with a 'dynamic' mail body. Most, if not all, 'mail solutions' will accept a variable as parameter, so you'll just have to build the variables content first.
Dynamic SQL is never an easy solution to anything
/Kenneth
-
I've used dynamic sql before and had a nightmare over it, and figured that it wouldn't be necessary with this procedure I'm writing.
The problem I've got though is building in the check and then making it change the email body.
Obviously to get a count of how many records are past a certain due date is simple.
and getting a list of those records is also easy.
But I'm having trouble actually making this update the body of the e-mail.
-
Well, it is rather complicated to explain anything, not knowing your database, but hopefully this will set you on the right track:
CREATE PROCEDURE mail_alert_contact AS
DECLARE @Email Varchar(1024)
EXECUTE GetEmailList 'mail_alert_contact', @Email OUTPUT
IF EXISTS(SELECT * FROM vw_mail_alert_contact)
BEGIN
EXEC master..xp_sendmail @recipients = @Email,
/* EXEC master..xp_sendmail @recipients = 'john.doe@company.com', */
@query = 'SELECT * FROM vw_mail_alert_contact',
@subject = 'ALERT: Contact these people',
@message = 'These people need to be contacted today: ',
@attach_results = 'FALSE', @width = 250,
@dbuse = 'your_db_name'
END
GO
We have many such mail checks and need to have an easy way to modify recipient lists. Therefore info about all checks is stored in a table along with recipients. From there we retrieve it using a simple sp "GetEmailList". In case you only have a few, you can write the addresses directly into the procedure (as the one of John Doe), but I recommend a solution that would be configurable "from outside", i.e. not editing the procedure itself.
Same goes for the query - here, a view is defined and used to find the necessary records, but in other procedures we write the query directly into the @query parameter.
The IF clause makes sure that no mail is sent without data, but it means that the query is executed twice. However, the first execution should be very quick, since it only checks whether at least one row will be returned.
HTH, Vladan
-
I just re-read your original post and explanation... maybe your only problem is to get the number of people to be contacted into the text? Well, then I would first ask whether it really makes sense to put the number of people into the mail, if there already are all their names...
If it does, I would modify the proc that way:
CREATE PROCEDURE mail_alert_contact AS
DECLARE @Email Varchar(1024), @count int
SELECT @count = count(*) from vw_mail_alert_contact
EXECUTE GetEmailList 'mail_alert_contact', @Email OUTPUT
IF ISNULL(@count,0) > 0
BEGIN
EXEC master..xp_sendmail @recipients = @Email,
/* EXEC master..xp_sendmail @recipients = 'john.doe@company.com', */
@query =
'SELECT ''The following ''+ CONVERT(VARCHAR(10),@count) + '' people need contacting today'' AS '' ''
SELECT * FROM vw_mail_alert_contact',
@subject = 'ALERT: Contact these people',
@message = 'Results of a regular check: ',
@attach_results = 'FALSE', @width = 250,
@dbuse = 'your_db_name'
END
GO
-
That explains a lot as I wasn't using SQLmail to send the program but CDOSYS as we don't have SQL mail set up and use CDOSYS which is fine until I encountered an issue getting the @body to be more flexible.
Seeing the size of the query above compared to the length of one of my CDOSYS queries has got me rethinking the whole thing though.
-
In that case, I would really recommend setting up SQL mail. We are sending almost 100 different alerts and infomessages with it (many of them daily), covering just about anything that can happen and needs attention of users.
However, the main issue that makes the code easily readable and maintainable, is the use of views and stored procedures. Then you only need to set up a job in SQL Agent that will run e.g. every workday at 6 a.m. (very simple one, the command will consist of "EXEC mail_alert_contact" only), and when the people come to their offices, they will have the info in their mail. If some day they will come to you that they want to see not only names, but also age of those to be contacted, you can leave both the agent and procedure as it is - just go to the view definition and add the respective column(s). If the recipient list changes, add the recipients to a table that gets queried by GetEmailList procedure. In fact, the code isn't shorter or simpler - it is just split into several parts (start job at a certain time, get address list, get query result, send mail) and every one of them is easy to understand and modify.
It is simple, but we learned it the hard way. First few checks were written inside the Command of a job... works just as well, but it is a nightmare to change anything. Then we moved the code to stored procedures, and finally decided to use views wherever possible. Somewhere along the line also the addresses were moved into a table and read by sp. With this experience, I can't emphasize enough how big difference it makes for the admins .. and for users too, since their calls to modify something get much quicker response.
-
Go to the following web site for another option for mail:
http://www.sqldev.net/xp/xpsmtp.htm
We don't use Exchange here so we had to find another solution to SQLMail. We have been using this for at least a couple of years and it works great for us. This gives you a lot of flexibility in what you put in your e-mail.
Good luck!
----------------------------------------------------------01010011010100010100110000100000010100110110010101110010011101100110010101110010001000000101001001101111011000110110101101110011
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply