Error 14274 cannot add update or delete a job ошибка

: July 15 2010, 10:49

Category:

IT
Cancel

Хотя я самоидентифицируюсь как копирайтер, но помимо копирайтинга и рерайтинга вечерами, приходится заниматься и работой в офисе. А в офисе я ведь одминко :).
И вот в силу своих должностных обязанностей работаю я со стареньким, но надёжным MS SQL Server 2000. А у старичка этого иногда возникает проблема: после переноса или переименования сервера возникают затыки с job’ами. При попытке изменить или удалить job возникает ошибка

Fix : Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

Звучит страшно, но на самом деле дело пустяковое. Ошибка возникает по причине ошибки в имени сервера, которое хранится в самом MS SQL Server. Вариантов решения этой задачи два:
I. Рекомендации Microsoft
1) Задайте первоначальное имя сервера (до переезда/переименования)
2) Сохраните job’ы в скриптах.
3) Переименуйте сервер в новое имя.
4) Выполните сохранённые скрипты для восстановления job’ов.

Этот вариант простой, но мне он не помог. Поэтому я ещё немного погуглил и нашёл другие рекомендации на одном из многочисленных форумов.
II. Рекомендации опытного индуса.
1) В Query Analyzer запустите следующий скрипт для проверки:
SELECT @@servername
Для того, чтобы убедиться в актуальности имени сервера в базе MS SQL
2) Если значение не верное, то выполняем следующие действия:

sp_dropserver <'неверное_имя_сервера'>

а потом:
sp_addserver <'правильное_имя_сервера'>, 'local'

для того, чтобы заменить имя сервера на актуальное.
3) Потом нужно рестартануть SQL и проверить всё ли получилось.
4) Если всё получилось, то проверим корректность джобов путём проверки поля check the originating_server в таблице msdb..sysjobs путём запуска следующего скрипта:
SELECT * FROM msdb..sysjobs

после отработки скрипта вы получите таблицу со списком job’ов и их параметрами, проверьте в этой таблице значения имени сервера на предмет их корректного обновления.
Если какие-то из них не обновились, то запустите следующий скрипт:

USE msdb GO DECLARE @server sysname SET @server = CAST(SERVERPROPERTY('правильное_имя_сервера')AS sysname) UPDATE sysjobs SET originating_server = @server WHERE originating_server = '<неверное_имя_сервера>'

Выполнение одной из этих двух комбинаций действий обязательно должно вам помочь 🙂

Источник

We use SSIS packages to import files. These SSIS packages are run with sqljobs. During deployment the sqljobs are created with a script. That is the plan.

While running the script to create the sqljobs we got an error:

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

Image courtesy of jesadaphorn / FreeDigitalPhotos.net

Most solutions found on the internet talk about the name of the server. When you’ve renamed your Sql Server (yeah right) then you need to update the originating_server column in the sysjobs. This was not the case for us.

Over at msdn someone suggested to look at the variables. The first create sqljob would set the @jobid variable and the second create statement would use that value as input, where it is intended as output. The suggested SET @jobid = NULL before each call to sp_add_job did the trick.

About erictummers

Working in a DevOps team is the best thing that happened to me. I like challenges and sharing the solutions with others.

On my blog I’ll mostly post about my work, but expect an occasional home project, productivity tip and tooling review.

This entry was posted in Development and tagged error, sqljob, sqlserver, ssis. Bookmark the permalink.

Источник

I’m trying to create a rather simple script for dealing with SQL Server Agent jobs. It performs 2 tasks:

1) If a given job exists, delete it
2) Create the job

(Due to business requirements I can’t modify an existing job, the script must delete & re-create it.)

Running the script the first time works fine (creates the job). Running any times after that produces error 14274 «Cannot add, update, or delete a job that originated from an MSX server.»

I’ve done lots of searching on this, and most solutions center around the server name being changed. My server name has not changed, nor has it ever.

Here’s what I have:

USE [msdb];

SET NOCOUNT ON;

DECLARE @JobName NVARCHAR(128);
DECLARE @ReturnCode INT;
declare @errCode INT;

SET @JobName = 'AJob';

BEGIN TRANSACTION;

DECLARE @jobId uniqueidentifier;

SET @jobId = (SELECT job_id from msdb.dbo.sysjobs where name = @JobName);

IF(@jobId IS NOT NULL) -- delete if it already exists
begin
   EXEC @ReturnCode = msdb.dbo.sp_delete_job @job_id=@jobId
   IF(@@ERROR <> 0 OR @ReturnCode <> 0)
    begin
        set @errCode = @@ERROR;
        GOTO QuitWithRollback;
    end
    print 'deleted job';   
end


-- create the job
EXEC @ReturnCode = msdb.dbo.sp_add_job @job_name=@JobName,
        @enabled=1,
        @notify_level_eventlog=0, -- on failure
        @notify_level_email=0,
        @notify_level_netsend=0, -- never
        @notify_level_page=0,
        @delete_level=0,
        @description=NULL,
        @owner_login_name=N'sa',
        @notify_email_operator_name=NULL,
      @job_id = @jobId OUTPUT
IF(@@ERROR <> 0 OR @ReturnCode <> 0)
begin
    set @errCode = @@ERROR;
   GOTO QuitWithRollback;
end
print 'added job';

-- Server
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver
   @job_id = @jobId
IF(@@ERROR <> 0 OR @ReturnCode <> 0)
   GOTO QuitWithRollback;

COMMIT TRANSACTION;
RETURN;

QuitWithRollback:
   IF(@@TRANCOUNT > 0)
      ROLLBACK TRANSACTION;
   print 'Err: ' + CAST(@errCode AS varchar(10)) + ' ret: ' + cast(@ReturnCode as varchar(10));

I’m running it on SQL 2008 SP1. Any help would be very much appreciated!

Источник

Remove From My Forums

Question
Hi All,
The name of the Server was changed which in turn gave me the following error when I tried to delete the jobs

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server.
The job was not saved.

Knowing that it has to do with the Originator server in the sysjobs table; I hacked the table ( now I think not a good idea ) and deleted all the jobs that I wanted to delete and performed the same task on sysjobservers.

Now although I have accomplished the deletion of the jobs.
But I still get emails ( job failure notifications) for the deleted jobs exactly at the time they were scheduled.
This is driving me crazy as I do not how to stop these emails .

Any help is appreciated.

Thank you

Answers

1. Check sysnotifications table, if it still has notifications for those delted jobs, clean them as well.

2. If sysnotifications table does not have notifications then restarting SQL Agent service should defintely work.

How did you delete the job ? If you had used sp_delete_job SP then it would have taken care of cleaning all the dependent objects. Did you delete them usign TSQL delete statement ?

In case when server name was changed, instead of deleting the jobs, better option would have been to updating sysoriginatingservers table with the new server name.

Thanks,

Gops Dwarak, MSFT
I did restart the SQL Server Agant and the notifications stoppped coming.

Thank you for your help.

Источник

Hit very weird situation when tried to remove jobs running on remote-target servers in multi-server managed environment.

First what I’ve tried is to unassign target servers from multi-server job on MSX server, however, it did not delete jobs on some targets.

When I’ve tried to delete them manually via SSMS I’ve got following error:

TITLE: Microsoft SQL Server Management Studio
——————————
Drop failed for Job ‘CPU_Usage_By_Database’. (Microsoft.SqlServer.Smo)
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=15.0.18131.0+((SSMS_Rel).190606-1032)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Job&LinkId=20476
——————————
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
——————————
Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. (Microsoft SQL Server, Error: 14274)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&ProdVer=12.00.5223&EvtSrc=MSSQLServer&EvtID=14274&LinkId=20476
——————————

When I tried to follow help links, provided by newest SSMS, they led me to Microsoft’s advertisement portal.

The second I’ve tried was manual deletion of that job by a script:

EXEC msdb.dbo.sp_delete_job @job_name=N’My_MSX_Created_Job’,
@delete_unused_schedule=1;

It was not successful and gave me following short error without Microsoft adds:

Msg 14274, Level 16, State 1, Procedure
sp_delete_job, Line 91 [Batch Start Line 59]

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

I’ve looked in «sysjobs» table and saw there a column «originating_server_id», which is referencing to «sysoriginatingservers_view» view, which has «originating_server_id» value always to be zero for the local server.
So the fixing script is as easy as this:

UPDATE msdb..sysjobs

SET originating_server_id = 0

WHERE name=N'<Your Job Name>’;

EXEC msdb.dbo.sp_delete_job

@job_name=N'<Your Job Name>’,

@delete_unused_schedule=1;

Источник

December 20, 2006 by pinaldave

To fix the error which occurs after the Windows server name been changed, when trying to update or delete the jobs previously created in a SQL Server 2000 instance, or attaching msdb database.

Error 14274: Cannot add, update, or delete a job (or its steps or schedules) that originated from an MSX server. The job was not saved.

Reason:
SQL Server 2000 supports multi-instances, the originating_server field contains the instance name in the format ‘serverinstance’. Even for the default instance of the server, the actual server name is used instead of ‘(local)’. Therefore, after the Windows server is renamed, these jobs still reference the original server name and may not be updated or deleted by the process from the new server name. It’s a known problem with SQL2000 SP3.

Fix/Workaround/Solution:

In order to solve the problem you should perform the following steps:

From the Query Analyzer run following steps in order:
SELECT @@servername

and verify if it shows the correct SQL server name.

a) If not, run:
sp_dropserver

and then:
sp_addserver , ‘local’

to change the SQL server name.

Please restart SQL server service to let the new configuration takes effect.

b) If yes,

Please check the originating_server column in msdb..sysjobs by running:
SELECT *
FROM msdb..sysjobs

and verify if all jobs have the correct server name for originating_server.

If not, update this value with the correct server name by running following script
USE msdb
GO
DECLARE @server sysname
SET @server = CAST(SERVERPROPERTY(‘ServerName’)AS sysname)
UPDATE sysjobs
SET originating_server = @server
WHERE originating_server = ”

Reference: Pinal Dave (http://www.SQLAuthority.com),
http://support.microsoft.com/default.aspx?scid=kb;en-us;281642

Источник

Error 14274: Cannot add, update, or delete a job (or it steps or schedules) that originated from an

Discussion in ‘microsoft.public.sqlserver.programming’ started by loraras, Apr 11, 2007.

Dear Sir / Madam,

I have a scenario where one of our production servers name have been
changed a long time ago
and I needed to diable some jobs that were failing. When I attempted
to diable the job I got the
following error:

Error 14274: Cannot add, update, or delete a job (or it steps or
schedules) that originated from an MSX Server

I was then refered to the kb referrenced below:

http://support.microsoft.com/kb/281642

According to the article the workaround to the issue was:
WORKAROUND
The best way to handle this problem after the rename process is to
follow these steps: 1. Rename the server back to the original name.
2. Script out all of the jobs and then delete them.
3. Rename the server to the new name.
4. Add back the jobs by running the script generated from step 2.

Unfortunately this is not an option in our environment.

I then did my own workaround. All I did was to:

1) locate the job name in msdb..sysjobs
2) determine if the originating_server column for the job still shows
the «old» server name
3) Updated msdb..sysjobs originating_server column to the «new» server
name
4) Went to Enterprise Manager and diabled the job without any issues
or error messages

Hope this helps folks having the same issue as I did.

Advertisements

Advertisements

That worked for me. thanks.
Helped me out of a hole, thanks for the post.
WOW! This appears to be a record: just one day short of three
years and ten months.

[snip]

Sincerely,

Gene Wirchenko
You never know when eggheadcafe comes back and bites you!
That is a bad egg.

Actually, it appears that was the date of the original posting,
but there was a reply later. Still, badly out of date, but not really
what we should be calling a record. <delicate sniff>

«Badeggcafe: spewing on USENET.»

Oh, no, someone might steal my slogan idea.

Sincerely,

Gene Wirchenko

Advertisements

Ask a Question

Want to reply to this thread or ask your own question?

You’ll need to choose a username for the site, which only take a couple of moments (here). After that, you can post your question and our members will help you out.