Can't get an email from my jobs HELP!

  • Just setup a new SQL server.

    I go into 'database mail' and set that up...I hit 'send test email' -works fine (I get the test email)

    When I go into my job under 'notifications' I select the user I just created in operators.

    I tried all three choices "when the job fails" etc none of them ever send me out an email

    I must be missing something....can't figure it out..

  • got it thanks anyhow..

  • And the solution was?

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

  • I'm sure it was a delay in SMTP server.

    Alex S
  • I'm sure the issue was that he set up database mail, but did not configure SQL Server Agent to use database mail. It is a common mistake to forget to configure SQL Server Agent to use mail after setting up database mail.

  • This is why I just run the following when setting up a new SQL Server instance (2005/08). Then I restart the agent, and run the job at the end:

    use msdb

    EXEC msdb.dbo.sp_add_operator @name=N'Notify DBA Support',

    @enabled=1,

    @pager_days=0,

    @email_address=N'NotifyDBASupport@DOMAIN.com'

    EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'SERVER\INSTANCE Admin Account',

    @description = 'Primary Mail Account',

    @email_address = 'NotifyDBASupport@DOMAIN.com',

    @replyto_address = 'NotifyDBASupport@DOMAIN.com',

    @display_name = 'SQL Server SERVER\INSTANCE',

    @mailserver_name = 'DCMail' ;

    EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Administrators',

    @description = 'Used by the Agent and SQL Server Engine' ;

    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Administrators',

    @account_name = 'SERVER\INSTANCE Admin Account',

    @sequence_number = 1 ;

    EXEC msdb.dbo.sp_set_sqlagent_properties @email_save_in_sent_folder=1

    GO

    EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', N'REG_DWORD', 1

    GO

    EXEC master.dbo.xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', N'REG_SZ', N'Administrators'

    ------------------------------------------------------------------------------------------->>>>>>>>>><<<<<<

    EXECUTE msdb.dbo.sysmail_add_account_sp

    @account_name = 'DBA Public Account',

    @description = 'Mail Account for public use',

    @email_address = 'NotifyDBASupport@DOMAIN.com',

    @replyto_address = 'NotifyDBASupport@DOMAIN.com',

    @display_name = 'SERVER\INSTANCE',

    @mailserver_name = 'DCMail' ;

    EXECUTE msdb.dbo.sysmail_add_profile_sp

    @profile_name = 'Public',

    @description = 'For use by developers' ;

    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

    @profile_name = 'Public',

    @account_name = 'SERVER\INSTANCE Admin Account',

    @sequence_number = 2 ;

    EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

    @profile_name = 'Public',

    @principal_name = 'public',

    @is_default = 1 ;

    Use MSDB

    select serverproperty('servername')

    exec sp_configure 'show advanced options',1

    reconfigure

    exec sp_configure 'Database Mail XPs',1

    If not exists(Select * from sys.Server_principals where name = 'Domain_Name\Group_Name')

    Exec('use Master Create Login [Domain_Name\Group_Name] from windows')

    If not exists(Select * from Sys.Database_principals where name like '%Group_Name')

    Create User [Domain_Name\Group_Name] from login [Domain_Name\Group_Name]

    Declare @user sysname

    select @user = name from sys.Database_Principals where name like '%Group_Name'

    Exec sp_addrolemember 'DatabaseMailUserRole',@user

    ------------------------------------------

    USE [msdb]

    GO

    /****** Object: Job [test mail] Script Date: 01/12/2009 10:01:34 ******/

    BEGIN TRANSACTION

    DECLARE @ReturnCode INT

    SELECT @ReturnCode = 0

    /****** Object: JobCategory [[Uncategorized (Local)]]] Script Date: 01/12/2009 10:01:34 ******/

    IF NOT EXISTS (SELECT name FROM msdb.dbo.syscategories WHERE name=N'[Uncategorized (Local)]' AND category_class=1)

    BEGIN

    EXEC @ReturnCode = msdb.dbo.sp_add_category @class=N'JOB', @type=N'LOCAL', @name=N'[Uncategorized (Local)]'

    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    END

    DECLARE @jobId BINARY(16)

    EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'test mail',

    @enabled=1,

    @notify_level_eventlog=0,

    @notify_level_email=2,

    @notify_level_netsend=0,

    @notify_level_page=0,

    @delete_level=0,

    @description=N'No description available.',

    @category_name=N'[Uncategorized (Local)]',

    @owner_login_name=N'DATACORE_KC\towhite',

    @notify_email_operator_name=N'Notify DBA Support', @job_id = @jobId OUTPUT

    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    /****** Object: Step [test mail step] Script Date: 01/12/2009 10:01:35 ******/

    EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'test mail step',

    @step_id=1,

    @cmdexec_success_code=0,

    @on_success_action=1,

    @on_success_step_id=0,

    @on_fail_action=2,

    @on_fail_step_id=0,

    @retry_attempts=0,

    @retry_interval=0,

    @os_run_priority=0, @subsystem=N'TSQL',

    @command=N'select ''f'' + 5',

    @database_name=N'master',

    @flags=0

    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1

    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId, @server_name = N'(local)'

    IF (@@ERROR 0 OR @ReturnCode 0) GOTO QuitWithRollback

    COMMIT TRANSACTION

    GOTO EndSave

    QuitWithRollback:

    IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION

    EndSave:

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

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