Database Mail does not work from SQL Agent Job

Question

Post reply

Database Mail does not work from SQL Agent Job

Saidba17

SSC Eights!

Points: 818
More actions
September 30, 2010 at 1:48 pm

#227226

Hi all,
We are running Sql server 2005 Sp1 on windows 2008 Sp1.The Sql server agent is up and running but we are unable to send notification's when job is done.Looked at many forums but coudn't fix it.Did telnet to check port 25 is open .
I have followed the below steps.
1. Installed/Configured Database Mail.(Testedt it and got Test mail)
2. Setup SQL Server Agent to use the Profile under the alert system tab.
3. Created an operator.
4. Created a job that would fail (select * from from tblBlah), and set to email my operator on failure.
5. Restarted SQL Server Agent.
[264] An attempt was made to send an email when no email session has been established
But still doesn't work.Any ideas
Thanks,
Pavan
[264] An attempt was made to send an email when no email session has been established

Viewing 15 posts - 1 through 15 (of 17 total)

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

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 1

Did you make the profile a public default profile?

Were there any error messages in the Database Mail Log?

Saidba17 SSC Eights! Points: 818 More actions · Answer 2

Michael,

I did make it default profile.It just shows DatabaseMail process is started/stopped.Doesn't show any errors.I also see [364] The Messenger service has not been started - NetSend notifications will not be sent in our agent logs .Is this somekind of firewall issue.

Michael Valentine Jones SSC Guru Points: 64818 More actions · Answer 3

Do you have the operator configured to do a Net Send, instead of email?

Saidba17 SSC Eights! Points: 818 More actions · Answer 4

No.Our operator is configured to send an email.I also see another error in the agent logs [LOG] Unable to read local eventlog (reason: The parameter is incorrect).These message starts once i create an alert and continue to every second .

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 5

You can try checking the sysmail items table. Maybe the sent_status will be useful.

select * from msdb.dbo.sysmail_allitems

I don't think this will help, but will mention it anyway. I had errors setting up my dbmail profile and needed to install patch KB970894 (I was already running SP3). Since you're past that step, I doubt this is your problem. This patch puts your version at 9.0.4262.

Cindy

Saidba17 SSC Eights! Points: 818 More actions · Answer 6

Thanks Cindy for your comments.I did check and database test mail shows status sent and i have received them but when we schedule a job ,we are unable to receive a notification

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 7

If I understand your problem correctly, when your agent job fails, nothing it being inserted into the msdb.dbo.sysmail_allitems table? Correct? So whatever is failing is preventing the notification from being generated.

On the surface, it seems like your agent user (the user id associated with the Windows service) may be missing some permissions. I have to head-out now, so I'll just throw this link at you. It's a good reference for permissions.

http://msdn.microsoft.com/en-us/library/ms143504.aspx

Cindy

balasach82 SSCrazy Eights Points: 9653 More actions · Answer 8

Right-click SQL Server Agent, and then click Properties.

Click Alert System.

Select Enable Mail Profile.

In the Mail system list, select Database Mail.

In the Mail profile list, select a mail profile for Database Mail.

Restart SQL Server Agent.

Saidba17 SSC Eights! Points: 818 More actions · Answer 9

Bala thanks for your response.I tried but dint work .Keeps throwing

Message

[264] An attempt was made to send an email when no email session has been established

Message

[355] The mail system failed to initialize; check configuration settings

CavyPrincess Hall of Fame Points: 3336 More actions · Answer 10

Are you running 32-bit or 64-bit? What is value of select @@version?

Does your account running the SQL Agent have the following access to these folders? Is the SQL Agent account the same account used at initial install?

Instid\MSSQL\binn - Full control

Instid\MSSQL\Log - Read, Write, Delete, Execute

100\com - Read, Execute

100\shared - Read, Execute

100\shared\Errordumps - Read, Write

ServerName\EventLog - Full control

Cindy

homebrew01 SSC Guru Points: 55539 More actions · Answer 11

Is there a good reason you are still on SP1 ? It may not apply here, but keeping current on service packs can help avoid problems.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 12

Don't know why it didn't work when I tried to post this script directly, but I've managed to post it as an attachment now. It's the script I use to configure database mail on new servers. Hope it helps!

John

Saidba17 SSC Eights! Points: 818 More actions · Answer 13

Thanks guys for all your suggestions.Coudn't figure out the problem .We need check with out windows admin and see if there are any security issues.

SkyBVI SSCoach Points: 15128 More actions · Answer 14

@ sai

I guess, you have to update the sql server to sp3 or

you can also check by restarting sql server not just agent.

Regards,

Sushant

Regards
Sushant Kumar
MCTS,MCP