locked
Scheduled Jobs & emails ( Sql 2k) RRS feed

  • 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

    Tuesday, April 18, 2006 1:09 PM

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

     

     

     

     

     

    Wednesday, April 19, 2006 4:41 PM
  • I did restart the SQL Server Agant and the notifications stoppped coming.

    Thank you for your help.

     

    Thursday, April 20, 2006 1:45 PM

All replies

  • 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

     

     

     

     

     

    Wednesday, April 19, 2006 4:41 PM
  • Thank you for your response.

    The sysnotification table has been cleared.

    I did restart that SQL Server Agent ( hoping this will fix the issue; but I guess I have to wait till tonight when the emails get generated ).

    I did use TSQL to delete the jobs ( my bad).

    Is there any other way to confirm that I will not be receiving those emails any more.

    Thank you

    Wednesday, April 19, 2006 4:50 PM
  • That is really surprising!

    Did you try to recreate jobs that you deleted ? If not you can try recreating dummy jobs with same name and with no notification this time.

    Other option I can think of is to disable the mail profile and restart SQL Agent . Now enable the profile back and restart SQL Agent and check for the behavior.

    Gops Dwarak

     

     

    Thursday, April 20, 2006 3:09 AM
  • I did restart the SQL Server Agant and the notifications stoppped coming.

    Thank you for your help.

     

    Thursday, April 20, 2006 1:45 PM
  • Gops,

    I picked up this useful tip as am experiencing similar problems when server was cloned and renamed.

    However, cannot find the sysoriginatingservers table.

    Please tell me where this table resides.

    Thanks,

    Dickin

     

    Thursday, November 9, 2006 5:17 PM