locked
SQL Server Job to send email reminder message is failing RRS feed

  • Question

  • I have a sql job that runs every day at 3:15 pm to send an email reminder that another job needs to be "kicked off" by 4:00.  This job has been running since 2005 with no problems until about January 2008.  I am the manager of the Application Development and Database Administration teams where I work and recently lost my last DBA.  I have two developers with backgrounds in mainframe.  They are both trying very hard to learn SQL, VB, .NET, etc.....  but we are floundering because this job has failed and I have looked everywhere I can think of to try and fix the problem.  Here's what our code looks like for the job that is supposed to run (and does run because it shows that it is successful but no email shows up):  For security purposes I have modified the code to remove the actual account and domain names and other directory information.

    Dim objNewMail
     
    Set objNewMail = CreateObject("CDONTS.NewMail")

    objNewMail.From = "sender@domain_name"
    objNewMail.To = "mail_recipient@domain_name"

    objNewMail.Subject = "EFT file transfer reminder..."
    objNewMail.Body = "The EFTUBOC1.dat file needs to be download from the UBOC website by Production Support after 3:00pm and before 3:50pm. See document G:\SxxxKxxxPrxxx\Production Support\Prod Supp\Documentation\UBOC Processing\EFT File Processing Documentation for details."
    objNewMail.Send
     
    Set objNewMail = Nothing

    It feels like a needle in a haystack to a novice like myself, but I am hoping that some of the more seasoned professionals can give me a few more places to look.  Thanks in advance for your help.  Laurie
    Friday, May 23, 2008 4:01 PM

All replies

  • You said that this is a job that is running.  If you are using sql server 2005 I would reccommend using sp_send_dbmail which would make this a bit easier to troubleshoot and it is native to sql.  If you are using sql 2000 I would reccommend using xp_sendsmtpmail, found at the link below.

    http://www.sqldev.net/xp/xpsmtp.htm

     

    Friday, May 23, 2008 4:44 PM
  • We are using SQL Server 2008 SP4.  I have tried running a test job using xp_sendsmtpmail - it fails.  I have also tried a couple of variations, checked the login ids, verified the mail profile is working, etc.  I am at a complete loss.  This job has been running for a long time with no problems.  Now, the job still runs, shows that it was successful, but no email shows up in the recipients mailbox - very strange.  Any other thoughts?  Thank you.....Laurie
    Wednesday, May 28, 2008 12:00 AM
  • I think you mean SQL Server 2000 SP4, right?

    Can you log on to the server using the SQL Server Agent account and send email using Outlook? Does it arrive in the recipient's mailbox?

    For things like this I like Gert Draper's XPSMTP extended stored procedure.
    http://www.sqldev.net/xp/xpsmtp.htm

    But if you'd rather keep things the way they are, please post a follow up.

    Joe

    Wednesday, May 28, 2008 12:07 PM