Email a Status Sheet?
-
Here is what I would like to accomplish.
My company works with truck drivers and a ton of information on each, about 200 fields on a data base table. These fields contain names, dates, ss#, memos, check boxes, etc...
What I want to do is generate an email notification to the corporate office from the remote offices that change the information on the sql driven asp pages reflecting the changes made and what driver it was. We call these "Status Sheets" as they are currently generated by a system 36. We are moving away from the old IBM36 so this becomes a requirement for us.
This how I would like it to work, the user opens the drivers record for edit, changes the drivers status to "inactive", adds say a "date" to some other field. On clicking the "Save" button it collects the changes for that driver and emails a selected person at corporate informing them of the change.
Can anyone point me in the right direction for this???
-
SQL Server is pretty nifty at sending emails as longs as you use Exchange, do a search on "SQL Mail" on books online for starters. From memory the Exchange client on the Server boxes is fiddly to set up but after that it's easy. Maybe use an update trigger on the date field to call xp_sendmail?
Are you saying you have a table with 200 columns??? How about a bit of normalization?
-
Yes, about 200. 3/4 of them are dates that we must have, for example drivers license expiration, last physical, last drug screen, last positive drug screen, accident #1,2,3,4, it goes on and on. But, thanks I will give it go.
-
If you looking to send out plain text emails then don't waste your time with SQL Mail and xp_sendmail, get xp_smtp_sendmail from http://www.sqldev.net
If you want to have some HTML formatting, or maybe the query results, you can still use xp_smtp_sendmail. One option for small messages is to concatenate the fields together with appropriate HTML tags in a single string and use that as the message body.
You can also use sp_makewebtask to generate a HTML file which you use as the message body. This has one main drawback of requiring sysadmin privelages. However, you do get a file that you can use as an attachment as well.
--------------------
Colt 45 - the original point and click interface
-
I found great information here: http://www.sqlteam.com/item.asp?ItemID=5003. I am using aspmail to send auto-emails (via a trigger) whenever new records are inserted or updated. The update trigger looks at one fields specifically and if the new value is different from the old value, it sends off an email. ASPMAIL is awesome and so easy to use.
-
I use to like xp_smtp_sendmail until I wanted to send the results of a query and could not. Now I use my own stored proc. For samples see:
http://qa.sqlservercentral.com/scripts/contributions/287.asp
http://qa.sqlservercentral.com/scripts/contributions/355.asp
http://qa.sqlservercentral.com/scripts/contributions/37.asp
or http://qa.sqlservercentral.com/articles/articlelink.asp?articleid=1843
I don't see the point of paying for a product like aspmail when you can do it all with within using native supplied routines for free.
Francis
-
You could also use reporting services if you want to go down that road. Otherwise here's a pretty good article on sending html formatted emails as well.
200 columns is gonna look pretty cruddy in an email however - I pitty the manager that has to look at that.
http://www.databasejournal.com/features/mssql/article.php/10894_3489111_2
-
Using the techniques I suggested above, I haven't had a problem sending query results using xp_smtp_sendmail.
I've used CDO to send mail as well, but trying to maintain a consistent codebase across the differing CDO versions was a real pain. Also, I've had instances of the sp_OA* procedures being unreliable on a server under load.
--------------------
Colt 45 - the original point and click interface -
The most important thing is to make sure the mail gets there. When people leave, change name etc, your mail will not work unless all the recipients are found with an exact match. That's why I wrote this procedure that will mail an account if a failure occurs. Has worked for me for the lastthree months.
CREATE procedure SR_Check_Mail
(@vRecipients varchar(3000),
@vMessage varchar(3000),
@vSubject varchar(3000))
AS
/************************************************************************************************/
/* Procedure Name: SR_Check_Mail */
/* Creation Date: 19th January 2005 */
/* Written by: Jon Stokes */
/* */
/* Purpose: */
/* This script checks that a file copy has completed successfully */
/* */
/* Input Parameters: */
/* @vRecipients The recipients to recieve the mail */
/* @vMessage The message to be used in the mail */
/* @vSubject The text to be used in the subject line of the mail */
/* */
/* Output Parameters: */
/* NONE */
/* */
/* Usage: Checks to ensure mail success. On failure send mail to DWH notification account */
/* Local Variables: See comments in code */
/* */
/* Called By: Automation stored procs that send mails to report recipients */
/* */
/* Calls: None */
/* */
/* Data Modifications: None */
/* */
/* Updates: */
/* Date Author Purpose */
/* --------- -------------- ----------------------------------------- */
/************************************************************************************************/
BEGIN
--declare local variable and set with dummy value
declare @err int
set @err = 1
--execute code to send the message
exec @err = master..xp_sendmail
@recipients = @vRecipients,
@message = @vMessage,
@subject = @vSubject
-- check to see if error occurred and send admin mail where required
IF ( @err <> 0 )
BEGIN
exec master..xp_sendmail
@recipients = 'mail account to notify',
@message = @vMessage,
@subject = 'Mail Error occurred'
END
END
GO
called by the statement
EXEC SR_Check_Mail 'recipient list',
@'message body text', 'subject text'
You can adapt slightly to add in attachments if you wish.
------------------------------
The Users are always right - when I'm not wrong! -
First allow me to thank everyone, this has been more helpful then you will ever know.
After getting it to send the email, of course this was not enough for managment, they now want to make it automatic when the user clicks either the "Add" new record (Driver) and the "Edit" submit button. They want to take as much out of the hands of the users as possible. In other words, NO excuses like I forgot to send the Status change for this driver so he won't recieve a check this week. Truckers tend to get a little testy about things like that.
I'm sure this is a simple one for those in the know, however I do not belong in that group by any means. I know what your thinking, damn rookies.
Once this one is solved, I one other question about performance and how to limit the initial information on the main "drivers" page.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply