locked
Database Mail not working with SQL Server Agent RRS feed

  • Question

  •  

    I'm running SQL Server 2005 Standard edition 64 bit with SP2 on a 64 bit machine. 

     

    Database mail does not work with SQL Server Agent.  When I configure SQL Server Agent to use database mail the test email button is greyed out and inactive therefore I cannot send emails using operators or for jub success failure etc. 

     

    I've read that there was supposed to be a fix for this with SP1 but I have SP2 and still receive the same problem.  Please can somebody help as I do not wish to use SQL Mail as a work around due to this becomming redundant in future versions of SQL Server.

     

    Kind Regards

    Thursday, January 17, 2008 11:21 AM

Answers

  • PROBLEM SOLVED.  Go to the database mail wizard click Manage Profile Security, make sure your mail profile has Default Profile set to Yes.  If you do not do this you will not be able to send notifications to operators or use Database Mail in SQL Server Agent.  This is because Operators use the default profile and if there is none then the operator will fail.

     

    I hope this helps any of you receiving the same problem.

     

    Kind Regards

     

    Monday, January 28, 2008 10:23 AM
  • The drop down list is blank after you check the e-mail check box in Properties>Notifications of a job because you do not have any operators set up on the SQL Server.

     

    Go to SQL Server Agent>Operators then add a new operator specifying the full e-mail address.  When you go back into the properties of a job you will see this operator appearing in the e-mail drop list of the notifications tab.

     

    I hope this helps.

     

    Monday, March 3, 2008 4:13 PM

All replies

  • Hi,

     

    Check the below link for configuring alert in 64 bit sql server. Are you able to execute the stored procedure msdb.dbo.sp_send_dbmail from query window, if yes did you receive the mail.

    http://support.microsoft.com/kb/908360/

     

    Thursday, January 17, 2008 6:17 PM
  •  

    Thank you for your reply.  I've seen the above link before however I don't really want to use SQL Agent Mail as I need the benefits of Database Mail SP2 doesn't fix the problem.  I can run sp_send_dbmail but this is because it is running through the engine and not SQL Server Agent.  It is SQL Server Agent where I need databse mail working.

     

    Kind Regards

    Thursday, January 17, 2008 6:38 PM
  • Hi,

     

    Under SQL Agent properties--> alert system tab, you have to enable the mail profile and configure it.

     

    Thursday, January 17, 2008 7:00 PM
  • Yes I have.   I have also chosen database mail and the mail profile I set up on the server.  The test button is greyed out.  Database mail works fine just not under sql server agent.  SP2 is supposed to fix this issue on 64 bit servers as originally you couldn't use database mail with sql server agent however I have installed SP2 for SQL Server 2005 64bit Standard Edition.

     

    Thursday, January 17, 2008 7:21 PM
  • I have enabled the mail profile via checking the check box and the database service is database mail and my progile is a working profile I use for database mail within the SQL Server Engine.

     

    Thursday, January 17, 2008 7:26 PM
  • Ensure that the DB Mail option is enabled using the Surface Area Configuration tool.

    Friday, January 18, 2008 1:17 AM
  • Database mail is enabled in SQL Server Surface Area Config Tool, if it wasn't I wouldn't be able to use database mail to send emails within the engine using sp_send_dbmail.

     

    Friday, January 18, 2008 9:58 AM
  • PROBLEM SOLVED.  Go to the database mail wizard click Manage Profile Security, make sure your mail profile has Default Profile set to Yes.  If you do not do this you will not be able to send notifications to operators or use Database Mail in SQL Server Agent.  This is because Operators use the default profile and if there is none then the operator will fail.

     

    I hope this helps any of you receiving the same problem.

     

    Kind Regards

     

    Monday, January 28, 2008 10:23 AM
  • I still have problems!

     

    Sending email through stored procedure WORKS,

    Mail Profile have DEFAULT PROFILE = YES

    but I still have the Test button greyed...

     

    Any suggestion is highly appreciated!

    Kind Regards,

    D.

    Wednesday, February 6, 2008 9:38 AM
  • You will always have the test button greyed using database mail, to test the email goto management then right click database mail choosing send test e-mail from the context menu which appears.

     

    I hope this helps

     

    Wednesday, February 13, 2008 5:01 PM
  • Hello, i have two mail profiles lets say "a" and "b".

    i set as default the "b" and i go at properties of a job at sql agent and at notification tab i check the mail box, the text box next to the check box is blank!

    The test from database mail is work. Also i went at the properties of sql and i check the "Enable mail profile", i set at "to line" the mail where i want to take the notofocation, but when i put a subject the next time i go at the same place the subject is blank.

    Is it sure that when the job fails,that it will sent a mail to me?

    Thanks
    Monday, March 3, 2008 12:23 PM
  • Just now i observed that when i go at properties of the specific job,that i checked the mail notification , this is unchecked the next time i go at the same place.

    thanks again
    Monday, March 3, 2008 12:28 PM
  • Just now i observed that when i go at properties of the specific job,that i checked the mail notification , this is unchecked the next time i go at the same place.

    thanks again
    Monday, March 3, 2008 12:29 PM
  • I am having the exact same problem.  Any ideas?  Currently running x64 SP2 build 3042.
    Monday, March 3, 2008 2:57 PM
  • The drop down list is blank after you check the e-mail check box in Properties>Notifications of a job because you do not have any operators set up on the SQL Server.

     

    Go to SQL Server Agent>Operators then add a new operator specifying the full e-mail address.  When you go back into the properties of a job you will see this operator appearing in the e-mail drop list of the notifications tab.

     

    I hope this helps.

     

    Monday, March 3, 2008 4:13 PM
  • The problems solved

    steps:

    1)we create a mail profile at at Managment->Database Mail ,of SQL Managment

    2)we set this account as "default" at Managment->Database Mail -> Configure Database Mail -> Manage profile security

    3)At tab "Alert System" of SQL Server Agent properties , we check the "Enable mail profile" , Mail System=Database Mail, Mail profile = "the profile we already create"

    4)we create a new operator at "Operators" of sql agent, where at "notification options" -> "Email name" we put the mail where we want to sent the agent the mails at failured job

    5)We go at a specific job, at tab "Notifications" ,we check the "email" check box and then we choose the operator we just create, at the text box next to check box.

    I hope not to forget something

    Thank You all for the help Smile
    Tuesday, March 4, 2008 10:47 AM
  • Thanks Richard.  That was the final missing step.
    Tuesday, March 4, 2008 1:27 PM
  • No problem.

     

    Tuesday, March 4, 2008 1:39 PM
  • This was the problem - "email name" is misleading, I had put the recipients name, not the email address.  Thanks so much!
    Monday, August 25, 2014 5:17 PM