XPSMTP to send out a list of outstanding actions
-
I want to send a reminder every morning to users with overdue actions. (Actions due by yesterday)
So I need to loop through the actions table and identify users with outstanding actions.
I then need to construct an email for each user containing a summary of overdue actions.
I guess that 8000 characters @Message probably enough but as I can't be sure might be better to create an attachment.
Not really sure where to start and would appreciate a pointer to any examples which demonstrate XPSMTP being used for multiple emails. I guess I need two loops, one for the users and one to create the message text if I create a procedure and run this from a job. Alternatively would I be better off using DTS and creating a file.
-
A thought, the userid is in the Actions table so I could order the Actions on userid, build a message while the userid stayed the same. Then email this before moving on to the next user.
I guess a cursor best way of doing this if I can live with the 8000 character limit. Otherwise I guess that I have to use DTS and create a text file.
Ideas on approach and pointers to examples would be much appreciated.
-
Check http://www.sqldev.net/xp/xpsmtp.htm#Usage for sample of "using variables".
-
Hey StephanJ
Some approaches that I could think of are:
[Approach 1 : Go SQL Server all the way]
1) Create a stored procedure to identify user e-mails with overdue stuff from yesterday then use a cursor and the xp_sendmail SQL Server system extended stored procedure to send the e-mail reminders (for info on xp_sendmail, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_6hbg.asp)
2) Create a job that calls the stored proc. Schedule the job to run daily starting at xx:xx a.m.
[Approach 2 : Go SQL Server half-way]
1) Create a VBScript file to do the e-mail stuff from step 1 in Approach 1 (do a search for CDONTS in either http://www.google.com OR http://www.msdn.com for examples on how to use the CDONTS e-mail component from VBScript). This script file would still use the "identify user e-mails with overdue stuff from yesterday" functionality of the stored proc.
2) Create another stored procedure that uses the xp_cmdshell SQL Server extended system stored procedure to run the script file from step 1 from the command prompt (for info on xp_cmdshell, refer to http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp)
Example: Command string for launching a VBScript file called send_overdue_reminders.vbs located in, say folder, C:\Scripts would be C:\Scripts\send_overdue_reminders.vbs
3) Create a job that calls the stored proc from step 2. Schedule the job to run daily starting at xx:xx a.m.
[Approach 3 : Other]
1) Same as step 1 from Approach 2
2) Run the script as a Windows NT service using the command string from step 2 in Approach 2.
Hopefully this will help you get started. At the end, you would need to choose which approach works best for your situation.
Good Luck,
JP
-
Allen, thanks for the link, I did actually look at my print out of the file before asking the question.
-
JP, thanks for your detailed answer.
[Approach 1 : Go SQL Server all the way]
I could not get XP_SendMail to work so based on the advice on this site went the XP_SMTP_SENDMAIL route and found it worked perfectly.
However following the link and looking at the examples, I guess I could adapt these using XP_SMTP_SENDMAIL. Will give it a go.
I just had another thought, in a cursor I could test for the message length > than 8000 characters and send a second email if it is.
-
StefanJ
You may find the attached useful by sending email as HTML.
VBScript Interfaces in SQL Server 2000 Let You Transform Data and Provide Reports to Your Users
http://msdn.microsoft.com/msdnmag/issues/02/08/VBScriptandSQLServer2000/default.aspx
Tony
-
Tony, that looks really good. Many thanks.
Stefan
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply