I'm trying to get a SQL server job to send me an email when the job completes.
Database mail is enabled in the SAC
SQL Server Agent's alert system is configured to use DBMail
Database mail is enabled, and "send test email" succeeds (to the same email I want to send to).
When the job completes, I don't see any new entries in msdb.sysmail_allitems
Looking at the job history, entries show:
The job succeeded. The Job was invoked by User DOMAIN\GeorgeV. The last step to run was step 1 (test). NOTE: Failed to notify 'GeorgeV' via email.
The operator I set up has the correct email address, is enabled, doesn't have the "on duty schedule" checked and in the history of this operator it says "never emailed".
both sp_notify_operator and sp_send_dbmail executes correctly.
Database Mail log looks a little funny:
17/10/2008 09:24:04 DatabaseMail process is shutting down
17/10/2008 08:56:11 DatabaseMail process is started
17/10/2008 08:28:38 DatabaseMail process is shutting down
17/10/2008 08:12:07 DatabaseMail process is started
furthe back in the log dbmail starts around 6am and stops 10 minutes later. I doubt this is normal behavior?
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
I'm all out of ideas, can anyone suggest what I can try to get this issue resolved?
If any further information is required to solve this, let me know and I'll post it. Cheers!
OK, let's see if the linkage of everything in the tables seems correct. You should get values for each column. If you don't, then Pootle wins. Also, verify your own email address, and that the sending address (while not necessarily a mailbox) is a valid email address syntactically.
set nocount on
declare @instname nvarchar(100)
declare @instnum nvarchar(20)
declare @registrypath nvarchar(1000)
set @instname = isnull(convert(nvarchar(100), serverproperty('InstanceName')), N'MSSQLSERVER')
create table #registry
insert into #registry
EXEC master.sys.xp_regread 'HKEY_LOCAL_MACHINE', 'SOFTWARE\Microsoft\Microsoft SQL Server\Instance Names\SQL', @instname
select @instnum = value from #registry
set @registrypath = N'SOFTWARE\Microsoft\Microsoft SQL Server\' + @instnum + '\SQLServerAgent'
insert into #registry
exec xp_regread N'HKEY_LOCAL_MACHINE', @registrypath, N'DatabaseMailProfile'
select j.notify_level_email, o.name as "Operator Name", o.email_address, p.name as "Profile Name", a.name as "Account Name", a.email_address
from sysjobs j join
sysoperators o on j.notify_email_operator_id = o.id cross join
(sysmail_profile p join
sysmail_profileaccount pa on p.profile_id = pa.profile_id join
sysmail_account a on pa.account_id = a.account_id join #registry r on p.name = r.Value)
where j.name = Your Job name here
drop table #registry