Found a weirdness this morning that I thought I would share with you. I have recently setup up two new SQL Server 2008 clusters and setup the database mail yesterday so that I can receive a nightly backup report, which is much easier than 50-100 individual emails per server. The database mail worked on the send test email, but was not sending out for jobs.
I talked to our exchange guy to make sure there was nothing on that end preventing the flow. I reviewed the settings and review the SQL agent job. Nothing jumped out at me. The job history showed SQLSTATE 42000, ERROR 14607.
Upon bingling the error code, I found the error to mean incorrect profile name. I named the profile, so now SQL Server is judging my naming convention?
Wait, I scripted out the job from another server, but I changed the names in the script. I rechecked the profile on the source server and it was named “<server name> Mail”. However my profile on the new server was simply named after the server name. Once I changed the name by deleting the profile and creating a new one, the job worked successfully. Attention to details! I hope this helps someone, enjoy!