August 29, 2006 at 9:53 am
I am ready to scream. I have configured DBMail. I can send a test email by right-clicking Database Mail in the management studio, and I can send an email with the code below, but I can't send an email when a job fails.
--this works
EXEC sp_send_dbmail @profile_name='Default',
@recipients='notmyrealaddress@notmyrealdomain.com',
@subject='Test-o-rama',
@body='DBMail is going to be the death of me.'
--this shows a status of 'STARTED'
EXECUTE dbo.sysmail_help_status_sp
--this shows a STATUS of 1
SELECT is_broker_enabled FROM sys.databases WHERE name = 'msdb'
When I view the 'Alert System' part of the SQL Server Agent properties, I see that 'Database Mail' is the enabled mail system with the default mail profile. However, the 'Test...' button is grayed-out.
Please keep me from screaming. Has anyone else seen this magic? How can I exorcise the demon?
August 29, 2006 at 2:28 pm
Nevermind... You have to have SP1 applied to the x64 edition to make Database Mail work.
http://blogs.msdn.com/sanchan/archive/2006/04/23/581639.aspx
"SQL Server 2005 RTM x64/IA64
In SQL 2005 RTM x64 and IA64 bit versions, you can use Database Mail features just as you would on 32 bit versions of SQL.
However, on 64 bit, you cannot have SQL Agent interact with Database Mail to send notification of job failure/success etc. This is by design.
This is not the case with 32 bit versions. On SQL Server 2005 32 bit RTM versions, SQLAgent can interact with and use Database Mail subsystem."
August 30, 2006 at 9:58 am
Seth, I feel your pain... Alas, I too have released many a scream (nowadays more like a whimper) when dealing with the joys of DB-Mail and SQL2005, in general.
Simply working with it on the 32 bit version, getting notifications of failure continues to mystify me! I have 2 accounts/profiles - one for our "SQL Admin" and one to spoof one of our Departments for sending reports to the corporate shot-callers. Setting up a simple DB backup maintenance plan, scheduling it so that it becomes a Job, then setting failure notification on it seemed trivial. Until it didn't work ... and notification failed. Simple enough - I hadn't enabled the mail profile in the SQL Server Agent Properties (shame on me!). It STILL won't allow me to send a Test email - that box stays grayed out no matter what. Specifying Database Mail as the mail system, I toggled around the Mail Profile and continually looked for tweaks in the DB Mail configuration of the accounts and profile, restarted the Agent - you name it, I tried it. Finally got my first notification last night, and I have no idea why it mysteriously started working.
Now I'm really looking forward to setting it up on a 64 bit box, my next challenge in about a week... thanks for the article link, though!
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply