Need to update SQL job notifications operator

Question

Post reply

Need to update SQL job notifications operator

anitadba2010

Old Hand

Points: 366
More actions
January 28, 2011 at 12:53 pm

#244379

Hi All-

Could anyone guide me how to change/update the current SQL agent job notification operator ?

The current value for the notifications is selected to E-mail domain\abc when job fails and i need to update it domain\xyz

i tried executing below command but i received the error msg as below

sp_update_job @job_name ='perfdata' @notify_email_operator_name = 'domain\xyz'

Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 243

The specified '@notify_email_operator_name' is invalid (valid values are returned by sp_help_operator).

Thanks

Anita

Viewing 4 posts - 1 through 3 (of 3 total)

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

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 1

- Are you trying to validate the operators email address ??

- If you just want to modify the operator I would create another operator and alter the job to use that new one.



USE [msdb]

GO

EXEC msdb.dbo.sp_add_operator @name=N'TheNewOne',

		@enabled=1,

.....

		@email_address=N'theaddress@the.rest',

		@category_name=N'[Uncategorized]'

GO

execute sp_update_job @job_name=N'yourjob', @notify_email_operator_name=N'TheNewOne';

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

anitadba2010 Old Hand Points: 366 More actions · Answer 2

anitadba2010

Old Hand

Points: 366

January 28, 2011 at 1:22 pm

#1279927

Thank you sir :). it worked perfectly.

I was successfully able to add the notification operator. but mistakenly i added the email operator twice with 2 differnt names. how can i remove one of them so that it doesnt show up in the drop down list of notfication operators 🙁

Anita

Johan Bijnens SSC Guru Points: 134873 More actions · Answer 3



/* check no jobs are using your operator */

IF EXISTS ( Select  J.name as JobName

                  , O.name as OperatorName

                  , O.email_address

            from    msdb..sysjobs J

            inner join msdb..sysoperators O

                    on O.id = J.notify_email_operator_id

            where   O.name = N'TheOLDMe' )

    begin

		Print 'Operator still in Use !!!!!!!'

    end

else

    begin

        EXEC msdb.dbo.sp_delete_operator @name = N'TheOLDMe'

    end

Also doublecheck the operator isn't being used for netsend / pageremail