sql agent wont notify operator of failed job

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

sql agent wont notify operator of failed job

Author

Topic

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-06-25 : 15:17:34

i keep getting the following error when i see sql agent jobs fail:

NOTE: Failed to notify 'Albert Kohl' via email.

after looking @ the SQL Server Agent log, i always find:

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

odd thing is though, database mail works fine, and backup plans notify the same operator upon completion, or failure every night... any ideas on what could be wrong?

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-06-25 : 15:56:15

I would suggest googling your error to see if any of the solutions fix your issue. I googled it for you and found this: http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/c63d64c0-abac-45bf-b0c3-97f060361118/

There were many other links returned when I googled it though, so you should google it too.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-01 : 02:33:22

touché... however i already did digging on that problem, and 90 % of the solutions out there are restart the sql agent, no dice, everything else i found didnt work either.

after some more log digging, i found:

Date 6/30/2010 11:31:47 PM
Log SQL Server Agent (Current - 6/30/2010 11:31:00 PM)

Message
[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.Data.SqlClient.SqlException: profile name is not valid
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStre)

some googling on this one isnt yielding much make-sense results for me... any pointers?

sorry for taking so long to get back to you, i decided to slide my bike into a curb @ 40mph and put my self in the hospital for a few days...

Thanks!

Lumbago
Norsk Yak Master

3271 Posts

Posted - 2010-07-01 : 02:38:11

This is pretty obvious isn't it...? "profile name is not valid" tells met here might be a typo or something in the profile name...? Or maybe an old account is referenced or something?

- Lumbago

My blog (yes, I have a blog now! just not that much content yet)
-> www.thefirstsql.com

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-01 : 02:46:27

i've already deleted, recreated, and re-set it in the agent and reset everything...

mind you by the way that i have to select the profile from a dropdown box, so it's kinda hard for me ty type-o it i would think...

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-01 : 02:49:17

just created a new one too for grins, got this error now when i restarted the service.

Date 6/30/2010 11:51:14 PM
Log SQL Server Agent (Current - 6/30/2010 11:51:00 PM)

Message
[260] Unable to start mail session (reason: Microsoft.SqlServer.Management.SqlIMail.Server.Common.BaseException: Mail configuration information could not be read from the database. ---> System.InvalidCastException: Unable to cast object of type 'System.DBNull' to type 'System.Byte[]'.
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
--- End of inner exception stack trace ---
at Microsoft.SqlServer.Management.SqlIMail.Server.DataAccess.DataAccessAdapter.GetAccount(Int32 accountID)
)

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-06 : 02:53:27

any ideas?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-07 : 21:50:11

no? nothing?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-07 : 22:04:15

Actually, this might be really simple. Go into database mail and view your profiles - are any of them set as the default profile? If not, that is your problem - since the procedure used to notify operators (sp_notify_operators) uses the default profile. If one doesn't exist, it fails.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-08 : 15:28:04

i cant find in the database mail wizard where i set default.

is there any way to clear out ALL the databasemail options, reset it as if it were never turned on, and then i can try resetting it up?

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-08 : 15:28:16

or anyone got another idea?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-08 : 21:57:22

Right-click on database mail - select properties. View or change profiles - on that first page is where you'll find that value.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-09 : 00:28:21

not the same for me, i got right-click > Configure Database Mail > Manage Database Mail accounts and profiles > View, change, or delete an existing profile.

nothing in there to make it a default...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-07-09 : 01:24:10

Which version of SQL Server are you using?
Have you set your email server to relay emails from your database server?

N 56°04'39.26"
E 12°55'05.63"

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-09 : 01:50:29

2008, and yes, it's allowed to relay, everything works except the operator notification.

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-09 : 16:28:45

If you don't have a profile in there to manage - then you need to set one up. I don't know how you could have configured database mail without it, but that sounds like what happened.

Create a profile - associate an account with the profile (or create a new one) and make that profile the default profile.

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-10 : 16:54:00

perhaps you misunderstood, i have a profile, i just dont see any option anywhere to set it as the default.

is there any tsql i can use to manually set the default?

jeffw8713
Aged Yak Warrior

819 Posts

Posted - 2010-07-10 : 22:28:04

Yes, you can use one of the stored procedures to set this up. But, the option to define the default profile is in profile security.

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2010-07-11 : 02:21:50

If you want to use the stored procedures , it will be a combination of :

EXEC msdb.dbo.sysmail_delete_principalprofile_sp @principal_name=N'guest', @profile_name=N'Main'
EXEC msdb.dbo.sysmail_add_principalprofile_sp @principal_name=N'guest', @profile_name=N'Main', @is_default=1

Jack Vamvas
--------------------
http://www.sqlserver-dba.com

albertkohl
Aged Yak Warrior

740 Posts

Posted - 2010-07-11 : 13:24:08

i did it though the wizard now that you told me where it was, that didnt work, but then i deleted all profiles, and email accounts, reset them up, and then tried again, and it worked perfectly...

thanks for the help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources