June 24, 2003 at 10:47 am
I am having trouble creating a mail message in a trigger. What I would like to do is copy the data that is executing the trigger and then email the user that the data has been copied.
Help
June 24, 2003 at 11:36 am
bad idea, imho. Insert the data into some table with a timestamp and then have some job come along every minute and pick up the data and send the email.
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
June 25, 2003 at 12:55 am
Two special tables are used in trigger statements: the deleted table and the inserted table. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for trigger actions; however, you cannot alter the data in the tables directly.
select values from these tables and run xp_sendmail, I am using this in my database whenever bank udpate records from web. I send this info to our checkers that bank put some charges, this shows prompt action. If you need further info, I will highly appreciate to explain in deep.
Shamshad Ali.
MCP,MCSD
RHDC International
June 25, 2003 at 9:12 am
Do not trust xp_sendmail in a trigger. We (County of Marin) have use Exchange with an Outlook client and have had trouble from time to time with email. Perhaps we have a setup problem someplace, but I'm tired of hangs and access violations. It might work most of the time, but that once can get you until you stop the mail service (e.g., xp_stopmail or perhaps a SQL Server restart).
If the email is from a SQL Server Job, then a mail hang might prevent other jobs from starting until the SQL Server Agent is restarted. I do not have a warm and fuzzy feeling when I must rely on SQL Server email. I have even less confidence when I see the list of bug fixes related to email in the MS knowledge base. How much more is there to fix? Also, do I need to be vulnerable to the Exchange server and its administration?
Steve has the right idea in using a table for a mail queue. I would go further and avoid xp_sendmail if possible. The stored procedure sp_SQLSMTPMail in the link below uses SMTP without using the Exchange/Outlook process. It can also be altered to return the error and error description as output parameters, which can be saved to the mail queue table using T-SQL. I am testing this method now. (I do not know how to get the error message from xp_sendmail into the mail queue table without going outside of SQL Server – perhaps by using a VB.Net service. @@ERROR is no help here. However, xp_sendmail does return a 0 or 1 unless there’s an access violation. Guess that’s almost better than nothing.)
At least, so far, when there is an email problem using sp_SQLSMTPMail, SQL Server can recover. I have seen 3 level 20 access violations so far, but a retry seconds later worked. So far, the SMTP email procedure is able to both save the error (excluding access violations) and resend. I still do not have a warm and fuzzy felling about all of this. I hope Yukon has reliable email.
Here’s the link if you are interested.
http://qa.sqlservercentral.com/scripts/contributions/510.asp
Randy Stone
County of Marin (CA)
Edited by - rstone on 06/25/2003 1:07:23 PM
Randy
Helpdesk: Perhaps Im not the only one that does not know what you are doing. 😉
June 25, 2003 at 12:25 pm
Can you give me an example of this?
Sorry rusty at the SQL been a while..
quote:
bad idea, imho. Insert the data into some table with a timestamp and then have some job come along every minute and pick up the data and send the email.Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply