Email notification from a Job

Question

Email notification from a Job

Thermo-879282

SSC Journeyman

Points: 90
More actions
September 13, 2008 at 3:30 am

#126318

I have started watching a new batch of SQL Server 2005 instances.
I configured database mail on all of them and they all worked sending mail ususing-sql.
(The T-SQL uses xp_send_dbmail).
I added an operator and configured some jobs to notify the operator on completion.
No emails have got through from those jobs.
Does anyone have any ideas ro know where to look for error messages?
Database mail using xp_send_dbmail works fine.
Thanks
Thermo

Viewing 11 posts - 1 through 10 (of 10 total)

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

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

I think you mean sp_send_dbmail. In SSMS you can right-click on Database Mail and either View History or View Log.

My guess is that you did not set up a default profile, which is required to send mail from jobs. This is a very common error. I made it the first time as well.

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

Johan Bijnens SSC Guru Points: 135369 More actions · Answer 2

Is the job owner being granted use of the mail and the mail profile (at sqlserver level ! )

Johan

Learn to play, play to learn !

Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:

- How to post Performance Problems
- How to post data/code to get the best help[/url]

- How to prevent a sore throat after hours of presenting ppt

press F1 for solution, press shift+F1 for urgent solution 😀

Need a bit of Powershell? How about this

Who am I ? Sometimes this is me but most of the time this is me

purplebirky SSC-Addicted Points: 461 More actions · Answer 3

I am haveing the same problem, is the default profile the same as an operator? if not what is it under to set it up?

[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

The default profile is setup as part of the Database Mail setup. In SSMS connect to the server go to Management -> right-click on Databas Mail -> Configure Database Mail -> Manage Profile Security. There you see Pulic Profiles by default and the 3rd column is Default Profile, set this to Yes for the Profile you want to be the default.

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

purplebirky SSC-Addicted Points: 461 More actions · Answer 5

ahhh ok thank you 🙂

[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]

Pam Brisjar SSChampion Points: 12094 More actions · Answer 6

What Jack said. Keep in mind that you'll have to restart the Agent for the changes to take effect.

purplebirky SSC-Addicted Points: 461 More actions · Answer 7

It's working now 😀 me and my DBA team are now very happy thanks for the help

[font="Comic Sans MS"]Being "normal" is not necessarily a virtue; it rather denotes a lack of courage.
-Practical Magic[/font]

smmuntha Grasshopper Points: 15 More actions · Answer 8

I have a SQL server 2005 Express edition, there is no option for Database email. But, my task is to create a Notification email from Database to users whose doesnt fill the time sheet for the last week. Please anyone can explain how/where to write this database mail sending procedure?

Thanks

Jack Corbett SSC Guru Points: 184394 More actions · Answer 9

You may be able to use database mail with SQL Server Express with Advanced Services. If not you would have to create your own emailing process. You could use the CLR for this.

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

Thermo-879282 SSC Journeyman Points: 90 More actions · Answer 10

The express version does not have databasemail like the full version of SQL 2005. However if you have access to Access you could use Access as a front end to SQL Server Express and link the tables into Access. Then search the web to find some emailing VBA. I used Access to email out reports it works fine for simple stuff. I would not use it on large business apps though. This is a good Access web site to start with. http://www.utteraccess.com/

Back to my original post. I installed database mail on a new instance of SQL Server 2005 and tested a notification via a job and it worked once! Ahh! Why? The only thing I did differently was to add a default.

I did that on another server instance but it did not work.

I set the default like so:

EXECUTE msdb.dbo.sysmail_add_profileaccount_sp

@profile_name = 'SQL.DBAMailProfile',

@account_name = 'SQLDBAMAIL',

@sequence_number = 1

go

EXECUTE msdb.dbo.sysmail_add_principalprofile_sp

@profile_name = 'SQL.DBAMailProfile',

@principal_name = 'guest',

@is_default = '1' ;

go

EXECUTE msdb.dbo.sysmail_update_principalprofile_sp

@principal_name = 'guest',

@profile_name = 'SQL.DBAMailProfile',

@is_default = '1';

EXECUTE msdb.dbo.sysmail_help_principalprofile_sp -- Reveal settings

Does anyone have a step by step guide on this?

Thanks