SQL Agent Mail config thru T-SQL?

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    Is it possible to set up SQL Server Agent Mail through t-sql? I am currently having a problem (SQL Server 2000 Sp3a, Windows 2003 Server) trying to configure SQL Server Agent Mail through Enterprise Manager. I go through all of the normal steps (I've read all of the Microsoft docs on setting it up and I know I'm doing the 'right' things) and when I right-click on SQL Server Agent and try to configure the SQL Server Agent Email profile, the drop down box is greyed out.

    I'm still troubleshooting that, but I was just wondering if there is a way to choose a profile, etc. through T-SQL, system tables, etc. so that I can see if it is a server thing or an Enterprise Manager thing.

    Any help or ideas would be greatly appreciated!

    Thanks,

    Chris

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Is your SQL Server agent service account a domain account (or even a local account, just long as it isn't SYSTEM)? Have you logged on as that account and created a MAPI profile for it?

    K. Brian Kelley
    @kbriankelley

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    Yes, like I said I did all of the steps that Microsoft recommends in their documentation in technet, I've set up SQL Agent mail many times, both on clusters and standalone servers (and the server in question is a clustered virtual server).

    I logged in as the account that SQL Server / SQL Server Agent runs under, configured a profile, opened up Outlook, sent and received email, etc.

    I'm just looking for an alternative approach to getting this working. I know the info has to be in either a system table or the registry somewhere. I've never had this much trouble setting up Agent Mail. usually it quits working and fixing it is the problem.

    I looked all over this site (of course) and googled, but it looks like this is one of those things that everyone uses EM for, and no one configures it through T-SQL or the command line.

  • K. Brian Kelley

    SSC Guru

    Points: 114465

    Key to finding out where any of the SQL Server Agent settings are is to take a look at sp_set_sqlagent_properties in the msdb database. It uses xp_instance_regwrite to store most of the entries in the registry. The email profile can be found (default instance) at:

    HKLM\Software\Microsoft\MSSQLSERVER\SQLServerAgent\EmailProfile

    If it's not present for you, it's a REG_SZ type entry.

    K. Brian Kelley
    @kbriankelley

  • ChrisMoix-87856

    SSCertifiable

    Points: 7288

    Cool, I'll check into that. Thanks!

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply