SQL Server Agent

terrymriley61

The DELETE statement conflicted with the REFERENCE constraint “FK_subplan_job_id”

I ran into this message when attempting to delete a SQL Job.  Somehow a  subplan was orphaned and not allowing me to delete the job.

First I got the Job_id by right clicking on the job and scripting the drop.

1-30-2017-8-43-58-am

The script will contain the job_id

1-30-2017-8-47-01-am

Now I searched for the Job_id in the System Maintenance sub plans.

1-30-2017-8-51-56-am

I then used the delete statement above to remove the sub plan.  Then I was able to go back to the jobs and delete the job.

Thanks to Moshin’s DBA Blog for help on this.   Moshin’s DBA BLog

Advertisement
terrymriley61

How can I move SQL Server Agent Jobs from one Server to Another?

You can move SQL Server Agent jobs from one server to another by scripting the jobs.  The best way is by opening up SQL Server Management Studio.

Select View Object Explorer from the  VIew Menu in SQL Server Management Studio.

Object Explorer Details

From the Object Explorer Details window select all the jobs that you want to copy. Right click and select Script Job As then Create To ,  New Query Editor Window.

Select Jobs

This will create one script for all the jobs.  This script can be saved then run on the new server.  This will recreate all of the jobs on the new server.