Reprinted with permission from owner. Original at http://nlsimmons.com/SQLScraps/

Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.

Recently I ran into this error on an instance of SQL Server 2005. There are several articles and blog posts concerning this error on SQL Server 2000 systems, but next to none that discuss the error in SQL Server 2005. For a list of all of the procedures in MSDB that can throw this error, execute the following query:

SELECT name 
FROM sys.procedures
WHERE
OBJECT_DEFINITION(OBJECT_ID(name)) LIKE '%RAISERROR(14274%'

In my case, the error came from msdb.dbo.sp_add_job. To figure out what was causing the error, take a look at the procedure code:

USE [MSDB]
GO

EXEC sp_helptext sp_add_job;

Do a search for ‘RAISERROR(14274’ and you’ll see the logic that is causing the error. In the case of sp_add_job, the logic is:

IF (@job_id IS NULL)
  BEGIN
    -- Assign the GUID
    SELECT @job_id = NEWID()
  END
  ELSE
  BEGIN
    -- A job ID has been provided, so check that the caller is SQLServerAgent (inserting an MSX job)
    IF (PROGRAM_NAME() NOT LIKE N'SQLAgent%')
    BEGIN
      RAISERROR(14274, -1, -1)
      RETURN(1) -- Failure
    END
  END

Here we see that if a non-NULL value for @job_id is passed into sp_add_job from somewhere other than a SQL Agent Job step (e.g. SSMS), you’ll get the 14274 error.

I hope this helps someone out there!

Leave a Reply

Your email address will not be published. Required fields are marked *