"The specified '@notify_email_operator_name' is invalid" error

Question

I have a scripted SQL Server Agent job I'm trying to transfer over to my local database from a server, but I receive this error message:

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).

Double clicking the error message won't even take me to the line that it fails on. Has anybody else gotten this before?

Cassidycassie

Search the script for all references to @notify_email_operator_name' and check which ones you haven't set up on your local instance and adjust as necessary. – Rest

Answer 1

Use Ctrl+F to find where @notify_email_operator_name appears. It probably is set to an email profile that you haven't set up on your local machine.

For SQL Server 2005 and above, then you can set up database mail using an SMTP server.

In your case, you might just be doing some testing on your machine, and you might not care about the email notifications so you can safely set @notify_email_operator_name = NULL and then re-run the script.

Excrete

I just commented the line in the migrated script – Ichabod

Answer 2

Under your SQL Server Agent Tree on the left hand side of SSMS you'll find a node called operators. Create one with the same name as what your missing, or generate a create script from the same place as you generated your job create script.

Haynie

Answer 3

Your server probably has an operator set up for email notifications that you don't have. If you do want e-mail notifications, or if you just want to match your server's environment for testing purposes, run the following after filling in the blanks:

USE [msdb]
GO

EXEC msdb.dbo.sp_add_operator @name=N'youroperatorname',
        @enabled=1,
        @email_address=N'[email protected]',
        @category_name=N'[Uncategorized]'
GO

Obsidian

Answer 4

I ran into this while deploy/upgrade my local testing DB and it took me the whole afternoon to figure how to work it out even we have three answers as clues.

Davos's answer should be correct but in my case, the sp_add_job I found with @notify_email_operator_name had a default value assigned already so I can't just change it to null.

So it comes to Tod's answer, I add an operator named notify_email_operator_name but it doesn't work and deployment shows the same error.

Then I think if the deployment works on the production server, there has to have the correct operator. So I connect to the production server and found out the correct name of the operator is actually the assigned default value.

Solution in short

1. Go to the MS SQL that deployment works as expected.
2. Under SQL Server Agent > Operators > Find the operator you need.
3. Right-click > Script Operator as > Create To > ClipBoard.
4. Run the script in clipboard on the server that has the issue.

Tamaratamarack