2

I am trying to change a SQL Agent Operator in my system and it will not let me.

I have sa but I think it's partially due to how I'm doing it.

I'm trying to modify the operator with the name NIT and Arts to NIT and ARTS and when I do that it comes back with the error of "The specified @name (NIT and ARTS) already exists even though it doesn't currently.

I'm running on SQL Server 2008R2. Any help with this would be great. Thanks.

  • What does select name, [enabled] from msdb.dbo.sysoperators give you ? Does it give you NIT and ARTS ? Is your server collation case sensitive or insensitive ? For case insitive - NIT and ARTS and NIT and Arts means the same thing, so it wont allow you to create dupe entries. – Kin Shah Aug 17 '15 at 17:41
3

On a case insensitive server, those two names are identical. I am not sure if you are using the UI for this, but I get the same error when I run the following code:

USE msdb;
GO
EXEC dbo.sp_add_operator @name = N'NIT and Arts';
GO
EXEC dbo.sp_update_operator @name = N'NIT and Arts', @new_name = N'NIT and ARTS';

Yields:

Msg 14261, Level 16, State 1, Procedure sp_verify_operator, Line 38
The specified @name ('NIT and ARTS') already exists.

If we look at the code for sp_verify_operator, we see that there is a weakness here as a penalty for not using a surrogate key: it checks all existing rows for a match on name, including the row you are trying to change:

  -- The name must be unique  
  IF (EXISTS (SELECT *  
              FROM msdb.dbo.sysoperators  
              WHERE (name = @name)))  
  BEGIN  
    RAISERROR(14261, 16, 1, '@name', @name)  

Ideally, it should ignore the row you are trying to change, but it can't because there is no way to filter except by the name. And in a case sensitive collation, the above check fails because that row already has that name.

How do we work around this? Well you could rebuild msdb or something in a case sensitive or binary collation, but I think this is easier, just run this query manually:

UPDATE msdb.dbo.sysoperators 
  SET name = N'NIT and ARTS'
  WHERE name = N'NIT and Arts';

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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