Msg 14234, Level 16, State 1, Procedure sp_verify_job, Line 245 The specified ‘@notify_email_operator_name’ is invalid (valid values are returned by sp_help_operator).
Most times, this error is encountered when we script a SQL Server Agent job and we try to execute the script on another server (For example from development server to the production server).
The cause of this usually because the database mail profile from the original server has not been created on the new server.
Solution:
1. Open the script and press “CTRL-F”.
2. Search for the words “notify_email_operator_name”.
3. Replace the value with the new database mail profile you want to use.
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=N'UPDATE_TABLES',
@enabled=1,
@notify_level_eventlog=0,
@notify_level_email=2,
@notify_level_netsend=0,
@notify_level_page=0,
@delete_level=0,
@description=N'No description available.',
@category_name=N'[Uncategorized (Local)]',
@owner_login_name=N'SQLAmericaDevelopment',
@notify_email_operator_name=N'DevAlert', @job_id = @jobId OUTPUT 4. If you are not sure what the available database mail profile is, you can run this query
SELECT [name]
FROM [msdb].[dbo].[sysmail_profile] It will show you the list of available database mail profile.
5.Replace the original one with the available database mail profile
6.Run the script.
You should not encounter this error anymore.
I will love to hear back from you if this worked. Also do not hesitate to ask if you have any question.
Cheers!
