Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I want to rename SQL Server Agent operator, that is change it's name.

Is this a "valid" thing to do? I noticed that trying to change name in SSMS via Operator/Properties dialog doesn't actually do anything. However, clicking on selected operator in Object Explorer also allows you to rename it and produces desired result.

My other concern is existing jobs that were setup to send notifications using old operator's name. I wouldn't want them to fail to send notifications but on the other hand would like to avoid changing all of them manually. I dug around a bit and found that table dbo.sysjobs in msdb uses operator ID, so it seems it shouldn't be a problem. I also tried it on a test job and after renaming operator everything still worked as expected plus job properties displayed new operator name without me having to do anything. So it looks like it should not cause any problems at all but I would like some confirmation on this.

share|improve this question
add comment

1 Answer

up vote 4 down vote accepted

In order to change the name (or any other property of an operator), you'd make a call to msdb.dbo.sp_update_operator. For instance:

exec msdb.dbo.sp_update_operator 
    @name = 'OldOperatorName', 
    @new_name = 'NewOperatorName';
go

As for SSMS: most things you do in SSMS you have to option to script out the changes you are making in the GUI. So, without hitting Ok, you can select the Script button at the top to see what T-SQL SSMS will be executing. It seems as though even with a changed name (i.e. altered text in the Name text box), the generated T-SQL doesn't contain a specified @new_name parameter for the stored procedure call.

Moral of the story? Don't rely on a UI. If you want to have full control over what's happening then you should be using T-SQL. There will be no question in your mind if you're writing out the code to do what you want. No assumptions, nothing.

Regarding changing the name, SQL Server stores the ID of the operator to create the correlation between notification -> operator, and not the operator name. So no, you are not bound to the operator name and I don't believe changing the name of the operator will break anything.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.