Post reply

Cannot remove job

  • January 8, 2007 at 4:06 am

    #117052

    Hi, I'm having a little trouble removing an old job that was created along side a maintenance plan. The maintenance plan itself is removed but trying to remove the job yeilds the error:

    The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'job_id'.

    1) Before I go around deleting things from the system tables I was trying to check if this plan was referred to by anything else and I noticed that the column 'plan_id' doesn't seem to relate to anything within the database, if this is the case, what does it relate to?

    2) Is there a stored procedure I should be running instead of removing from the system tables myself?

  • January 11, 2007 at 8:00 am

    This was removed by the editor as SPAM

  • January 11, 2007 at 11:56 am

    #682743

    One thing I have seen is when the job is associated with a Maintenance procedure it will error if you try to delete the job before deleting the Maintenance Plan. 

    New to the DBA world...thank you for your help!,

    IanR

  • January 11, 2007 at 1:34 pm

    #682763

    Check the system table sysmaintplan_subplans and delete if any data related to your job exists in that table...

    SELECT s.subplan_id

    ,s.subplan_name

    ,s.subplan_description

    ,s.plan_id

    ,s.job_id

    ,s.schedule_id

    ,j.name

    FROM msdb.dbo.sysmaintplan_subplans s

    LEFT OUTER JOIN msdb.dbo.sysjobs j

    ON s.job_id = j.job_id

     

    MohammedU
    Microsoft SQL Server MVP

  • January 12, 2007 at 3:30 am

    #682839

    So this is one of those cases where you have to play with the system tables.

    But what does job_id relate to? Is it the package ID in SSIS?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply