We help IT Professionals succeed at work.

How Can you send email from Trigger in SQL server.

aharafa
aharafa asked
on
Really, it was tough job, how can you send email from SQL server inside a trigger by specifying email address not user name or profile name, please what requirements should i have and how to do it please send me a code sample.. thank u

aharafa
Comment
Watch Question

Commented:
I created a stored procedure to send emails using a third party component.  We were using easyMail, but the script could be modified to use any of the available components out there.  Here is the code for calling the component in a stored procedure.  there is a lot of repeated code for the error handling which could be removed if you wished.



CREATE PROCEDURE [sp_SendSMTPEmail](
 @sTo    nvarchar(255),
 @sFrom   nvarchar(255),
 @sSubject  nvarchar(255),
 @sBody  nvarchar(1000),
 @sErrorMessage nvarchar(1000)  OUTPUT
)
AS
  --  send out an email using third party smtp email component.

  --  Parameter number one specifies the recipient of the email.
  --  Parameter number two specifies the sender of the email.
  --  Parameter number three specifies the subject of the email.
  --  Parameter number four specifies the body of the email.
  --  Parameter number five is a return parameter that specifies any errors that occur.
  --
  -- Author: John Haywood
  -- Created 04/31/2000
  -- Last revised: 04/31/2000
DECLARE @objectCDO int
DECLARE @hr int
SELECT @sErrorMessage = Null
-- Create an object.
EXEC @hr = sp_OACreate 'EasyMail.SMTP.5', @objectCDO OUT
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to create the object.  "
END
 
-- Set all of the properties.
EXEC @hr = sp_OASetProperty @objectCDO, 'LicenseKey',  '<license key number here>'
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to set the LicenseKey property of the object.  "
END
EXEC @hr = sp_OAMethod @objectCDO, 'AddRecipient', NULL, '', @sTo, 1
-- EXEC @hr = sp_OASetProperty @objectCDO, 'To', @sTo
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to set the To property of the object.  "
END
EXEC @hr = sp_OASetProperty @objectCDO, 'FromAddr', @sFrom
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to set the From property of the object.  "
END
EXEC @hr = sp_OASetProperty @objectCDO, 'Subject', @sSubject
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to set the Subject property of the object.  "
END
EXEC @hr = sp_OASetProperty @objectCDO, 'BodyText', @sBody
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to set the Body property of the object.  "
END
 
EXEC @hr = sp_OASetProperty @objectCDO, 'MailServer', '<your smtp server address here>'
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to set the MailServer property of the object.  "
END
 
 
-- Send the email
EXEC @hr = sp_OAMethod @objectCDO, 'Send'
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to send the email.  "
END
 
-- Destroy the object.
EXEC @hr = sp_OADestroy @objectCDO
IF @hr <> 0
BEGIN
 SELECT @sErrorMessage = @sErrorMessage + "An error occured while trying to destroy the object.  "
END

Commented:
you would then need to add code in your trigger to call the stored procedure passing in your desired parameter information.

Commented:
by the way, in my opinion, this is not an easy 50 point question.  Many, many hours spent figuring this out the first time.

John
Questions Asked 14
Last 10 Grades Given C B B B  
Question Grading Record 4 Answers Graded / 4 Answers Received

Interesting Q, but very poor grading history. I would not even try to suggest anything unless you close previous questions.
John - Don't waste time on this.

Commented:
thanks jitganguly.

aharafa, please do not award me with a low grade.  I do not want the points if they damage my grade history.  You should try to keep all of your questions up to date and provide feedback to the experts helping you out.  Close out any questions where you have recieved assistance and award good grades.  An A grade does not cost you any more to give than a C grade does.  Experts will be much happier to assist you in the future if you follow this advice.

John

Author

Commented:
Thank you John, really i appreciate your assistance, and really i don't want to degrade any of you, really it is just i am very busy these days and because of that i don't have time to give grades, any way your code is great,but i use the CDONTS.NewMail component, i tried your code it succedes when i used it with stored procedure but inside trigger it did not work ...do u know why? thanks, the points really is for you, i can't work without u guys :)

aharafa

Commented:
in the trigger make sure you use exec sp_... or execute sp_...

if this does not work using these methods, a last resort would be to create a emailQueue table, add info to it in the trigger.  schedule a task to run every 5 minutes or so. in the task, read the contents of the table that have a flag/field of MessageSent = 0.  send the message and update the flag to 1.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.