locked
How to setup a mail notification RRS feed

  • Question

  • Hi All,

    I have schedule some job in sql server 2000 .Now i want to set up an email notification under scheduled jobs.

    Kindly let me know in detail steps starting from first step that how can i configure email notification. 

    Monday, August 24, 2009 5:58 PM

Answers

  • SQL Server 2000 does not itself sends email, it requires some service or support to send emails.

    As I mentioned earlier, xp_sendmail will send email using the MAPI Profiler. First you need to setup the MAPI, once MAPI is setup, then it is very easy to play with emails. You may follow the suggestion provided by Faiz to setup operator once you confirm that MAPI is working.

    You need to install Microsoft Outlook 2000 or earlier on server, than setup your email account on that. Once you setup the Outlook, after that add MAPI profile to SQL Server, so that sql server could send email using that profile. I guess you need to keep on running the outlook on that machine to take advantage of sending emails.

    I guess you know how to install Outlook and setting up your account there, you can follow the steps below to add MAPI profile to your sql service after installing Outlook.

    http://support.microsoft.com/kb/281293/EN-US/
    http://support.microsoft.com/default.aspx/kb/263556

    Thanks.

    -Swarndeep
    Tuesday, August 25, 2009 5:27 PM

All replies

  • I know two ways of doing this:

    1. The most easiest way of doing this will be to use XP_SENDMAIL procedure from sql server. However, you require to configure the MAPI mail profile on the server from where you need to send the email.

    2. Or, If you won't prefer the first method then you may use CDO object to send email. For this you need to write vbscript similar to what I have pasted below. You may either script within the job step, or you may write DTS package with vb module.

    Set objMessage = CreateObject("CDO.Message")
    objMessage.Subject = "Report"
    objMessage.From = "DBA@xyz.com"
    objMessage.CC = "me@xyz.com"
    objMessage.TO = "me@xyz.com"
    objMessage.HTMLBody = "<HTML><BODY><H5>Hi</H5><BR><p>Please find attached report<p><BR><BR><p>Thanks</p></BODY></HTML>"
    objMessage.AddAttachment   "c:\file.xtn"
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2

    'Name or IP of Remote SMTP Server
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.xyz.com" 'or IP address

    'Server port (typically 25)
    objMessage.Configuration.Fields.Item _
    ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 25
    objMessage.Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = "1"
    objMessage.Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "xyz/me"
    objMessage.Configuration.Fields.item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "*******"
    objMessage.Configuration.Fields.Update
    Monday, August 24, 2009 7:53 PM
  • In your first step from where i can get sample xp_sendmail procedure?
    In your second step i am not familiar with vbscripting,DTS package ....
    Tuesday, August 25, 2009 8:47 AM
  • Read more about xp_sendmail at stored procedure at http://msdn.microsoft.com/en-us/library/ms189505.aspx.


    Thanks, Leks
    Tuesday, August 25, 2009 8:56 AM
    Answerer
  • ravirajaryan ,


    1.First see your mail is working by running below query.

    EXEC xp_sendmail @recipients = ' ravirajaryan ,
    ',

    @message = 'Test mail .'


    2. If works : create a operator .
    3. From jobs select notify > select the operator . ... Ok

    Thanks Faiz Farazi MCDBA,MCSA
    Tuesday, August 25, 2009 4:08 PM
  • Thanks for your reply...
    my mail is not working fine..

    how to start this service.

    Tuesday, August 25, 2009 5:04 PM
  • SQL Server 2000 does not itself sends email, it requires some service or support to send emails.

    As I mentioned earlier, xp_sendmail will send email using the MAPI Profiler. First you need to setup the MAPI, once MAPI is setup, then it is very easy to play with emails. You may follow the suggestion provided by Faiz to setup operator once you confirm that MAPI is working.

    You need to install Microsoft Outlook 2000 or earlier on server, than setup your email account on that. Once you setup the Outlook, after that add MAPI profile to SQL Server, so that sql server could send email using that profile. I guess you need to keep on running the outlook on that machine to take advantage of sending emails.

    I guess you know how to install Outlook and setting up your account there, you can follow the steps below to add MAPI profile to your sql service after installing Outlook.

    http://support.microsoft.com/kb/281293/EN-US/
    http://support.microsoft.com/default.aspx/kb/263556

    Thanks.

    -Swarndeep
    Tuesday, August 25, 2009 5:27 PM
  • Thank you very much for this clear answer....
    i will try according to your suggestion and let you know if i will find any problem .
    Tuesday, August 25, 2009 6:06 PM
  • Hi Ravirajaryan

    I agree with Swarndeep; also, we could use CDO to send email by SMTP; I make an example here.
    Const cdoSendUsingMethod        = "http://schemas.microsoft.com/cdo/configuration/sendusing"
    Const cdoSendUsingPort          = 2
    Const cdoSMTPServer             = "http://schemas.microsoft.com/cdo/configuration/smtpserver"
    Const cdoSMTPServerPort         = "http://schemas.microsoft.com/cdo/configuration/smtpserverport"
    Const cdoSMTPConnectionTimeout  = "http://schemas.microsoft.com/cdo/configuration/smtpconnectiontimeout"
    Const cdoSMTPAuthenticate       = "http://schemas.microsoft.com/cdo/configuration/smtpauthenticate"
    Const cdoBasic                  = 1
    Const cdoSendUserName           = "http://schemas.microsoft.com/cdo/configuration/sendusername"
    Const cdoSendPassword           = "http://schemas.microsoft.com/cdo/configuration/sendpassword"

    Dim objMessage
    Dim objFields

    Set objMessage = CreateObject("CDO.Message")

    Set objFields = objMessage.Configuration.Fields

    ' Set config fields we care about
    objFields.Item(cdoSendUsingMethod)       = cdoSendUsingPort
    objFields.Item(cdoSMTPServer)            = "smtpauth.earthlink.net"
    objFields.Item(cdoSMTPServerPort)        = 25
    objFields.Item(cdoSMTPConnectionTimeout) = 10
    objFields.Item(cdoSMTPAuthenticate)      = 1
    objFields.Item(cdoSendUserName)   =""
    objFields.Item(cdoSendPassword)          =""

    objFields.Update

    objMessage.To       = "email_address"
    objMessage.From     = "emal_address"
    objMessage.Subject  = "SMTP Relay Test"
    objMessage.TextBody = "SMTP Relay Test Sent @ " & Now()
    objMessage.Send

    Set objMessage = Nothing

    MsgBox("DONE!")

    Regards
    Mark

    Thursday, August 27, 2009 5:41 AM