September 16, 2009 at 1:06 pm
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..
September 16, 2009 at 1:17 pm
got it thanks anyhow..
September 16, 2009 at 1:33 pm
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
September 16, 2009 at 2:12 pm
I'm sure it was a delay in SMTP server.
September 17, 2009 at 7:44 am
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.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
September 17, 2009 at 9:18 am
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