SQL SERVER 2005删除维护作业报错：The DELETE statement conflicted with the REFERENCE constraint "FK_subplan_job_id"

GO

EXEC msdb.dbo.sp_delete_job @job_id=N876ab683-6d81-47c4-bba2-0dfa58156110, 

@delete_unused_schedule=1

GO

消息 547，级别 16，状态 0，过程 sp_delete_job，第 178 行

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.The statement has been terminated.

ALTER PROCEDURE [dbo].[sp_delete_job]

 @job_id UNIQUEIDENTIFIER = NULL, -- If provided should NOT also provide job_name

 @job_name sysname = NULL, -- If provided should NOT also provide job_id

 @originating_server sysname = NULL, -- Reserved (used by SQLAgent)

 @delete_history BIT = 1, -- Reserved (used by SQLAgent)

 @delete_unused_schedule BIT = 1 -- For backward compatibility schedules are deleted by default if they are not

 -- being used by another job. With the introduction of reusable schedules in V9

 -- callers should set this to 0 so the schedule will be preserved for reuse.

AS

BEGIN

 DECLARE @current_msx_server sysname

 DECLARE @bMSX_job BIT

 DECLARE @retval INT

 DECLARE @local_machine_name sysname

 DECLARE @category_id INT

 DECLARE @job_owner_sid VARBINARY(85)

 SET NOCOUNT ON

 -- Remove any leading/trailing spaces from parameters

 SELECT @originating_server = UPPER(LTRIM(RTRIM(@originating_server)))

 -- Turn [nullable] empty string parameters into NULLs

 IF (@originating_server = N) SELECT @originating_server = NULL

 -- Change server name to always reflect real servername or servername\instancename

 IF (@originating_server IS NOT NULL AND @originating_server = (LOCAL))

 SELECT @originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY(ServerName)))

 IF ((@job_id IS NOT NULL) OR (@job_name IS NOT NULL))

 BEGIN

 EXECUTE @retval = sp_verify_job_identifiers @job_name,

 @job_id,

 @job_name OUTPUT,

 @job_id OUTPUT,

 @owner_sid = @job_owner_sid OUTPUT

 IF (@retval 0)

 RETURN(1) -- Failure

 END

 -- We need either a job name or a server name, not both

 IF ((@job_name IS NULL) AND (@originating_server IS NULL)) OR

 ((@job_name IS NOT NULL) AND (@originating_server IS NOT NULL))

 BEGIN

 RAISERROR(14279, -1, -1)

 RETURN(1) -- Failure

 END

 -- Get category to see if it is a misc. replication agent. @category_id will be

 -- NULL if there is no @job_id.

 select @category_id = category_id from msdb.dbo.sysjobs where job_id = @job_id

 -- If job name was given, determine if the job is from an MSX

 IF (@job_id IS NOT NULL)

 BEGIN

 SELECT @bMSX_job = CASE UPPER(originating_server)

 WHEN UPPER(CONVERT(sysname, SERVERPROPERTY(ServerName))) THEN 0

 ELSE 1

 END

 FROM msdb.dbo.sysjobs_view

 WHERE (job_id = @job_id)

 END

 -- If server name was given, warn user if different from current MSX

 IF (@originating_server IS NOT NULL)

 BEGIN

 EXECUTE @retval = master.dbo.xp_getnetname @local_machine_name OUTPUT

 IF (@retval 0)

 RETURN(1) -- Failure

 IF ((@originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY(ServerName)))) OR (@originating_server = UPPER(@local_machine_name)))

 SELECT @originating_server = UPPER(CONVERT(sysname, SERVERPROPERTY(ServerName)))

 EXECUTE master.dbo.xp_instance_regread NHKEY_LOCAL_MACHINE,

 NSOFTWARE\Microsoft\MSSQLServer\SQLServerAgent,

 NMSXServerName,

 @current_msx_server OUTPUT,

 Nno_output

 SELECT @current_msx_server = UPPER(@current_msx_server)

 -- If server name was given but its not the current MSX, print a warning

 SELECT @current_msx_server = LTRIM(RTRIM(@current_msx_server))

 IF ((@current_msx_server IS NOT NULL) AND (@current_msx_server N) AND (@originating_server @current_msx_server))

 RAISERROR(14224, 0, 1, @current_msx_server)

 END

 -- Check authority (only SQLServerAgent can delete a non-local job)

 IF (((@originating_server IS NOT NULL) AND (@originating_server UPPER(CONVERT(sysname, SERVERPROPERTY(ServerName))))) OR (@bMSX_job = 1)) AND

 (PROGRAM_NAME() NOT LIKE NSQLAgent%)

 BEGIN

 RAISERROR(14274, -1, -1)

 RETURN(1) -- Failure

 END

 -- Check permissions beyond whats checked by the sysjobs_view

 -- SQLAgentReader and SQLAgentOperator roles that can see all jobs

 -- cannot delete jobs they do not own

 IF (@job_id IS NOT NULL)

 BEGIN

 IF (@job_owner_sid SUSER_SID() -- does not own the job

 AND (ISNULL(IS_SRVROLEMEMBER(Nsysadmin), 0) 1)) -- is not sysadmin

 BEGIN

 RAISERROR(14525, -1, -1);

 RETURN(1) -- Failure

 END

 END

 -- Do the delete (for a specific job)

 IF (@job_id IS NOT NULL)

 BEGIN

 -- Note: This temp table is referenced by msdb.dbo.sp_delete_job_references

 CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL, job_is_cached INT NOT NULL)

 DECLARE @temp_schedules_to_delete TABLE (schedule_id INT NOT NULL)

 INSERT INTO #temp_jobs_to_delete

 SELECT job_id, (SELECT COUNT(*)

 FROM msdb.dbo.sysjobservers

 WHERE (job_id = @job_id)

 AND (server_id = 0))

 FROM msdb.dbo.sysjobs_view

 WHERE (job_id = @job_id)

 -- Check if we have any work to do

 IF (NOT EXISTS (SELECT *

 FROM #temp_jobs_to_delete))

 BEGIN

 DROP TABLE #temp_jobs_to_delete

 RETURN(0) -- Success

 END

 -- Post the delete to any target servers (need to do this BEFORE

 -- deleting the job itself, but AFTER clearing all all pending

 -- download instructions). Note that if the job is NOT a

 -- multi-server job then sp_post_msx_operation will catch this and

 -- will do nothing. Since it will do nothing that is why we need

 -- to NOT delete any pending delete requests, because that delete

 -- request might have been for the last target server and thus

 -- this job isnt a multi-server job anymore so posting the global

 -- delete would do nothing.

 DELETE FROM msdb.dbo.sysdownloadlist

 WHERE (object_id = @job_id)

 and (operation_code != 3) -- Delete

 EXECUTE msdb.dbo.sp_post_msx_operation DELETE, JOB, @job_id

 -- Must do this before deleting the job itself since sp_sqlagent_notify does a lookup on sysjobs_view

 -- Note: Dont notify agent in this call. It is done after the transaction is committed

 -- just in case this job is in the process of deleting itself

 EXECUTE msdb.dbo.sp_delete_job_references @notify_sqlagent = 0

 -- Delete all traces of the job

 BEGIN TRANSACTION

 --Get the schedules to delete before deleting records from sysjobschedules

 IF(@delete_unused_schedule = 1)

 BEGIN

 --Get the list of schedules to delete

 INSERT INTO @temp_schedules_to_delete

 SELECT DISTINCT schedule_id

 FROM msdb.dbo.sysschedules

 WHERE (schedule_id IN

 (SELECT schedule_id

 FROM msdb.dbo.sysjobschedules

 WHERE (job_id = @job_id)))

 END

 DELETE FROM msdb.dbo.sysjobschedules

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 DELETE FROM msdb.dbo.sysjobservers

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 DELETE FROM msdb.dbo.sysjobsteps

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 DELETE FROM msdb.dbo.sysjobs

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 --Delete the schedule(s) if requested to and it isnt being used by other jobs

 IF(@delete_unused_schedule = 1)

 BEGIN

 --Now OK to delete the schedule

 DELETE FROM msdb.dbo.sysschedules

 WHERE schedule_id IN

 (SELECT schedule_id

 FROM @temp_schedules_to_delete as sdel

 WHERE NOT EXISTS(SELECT *

 FROM msdb.dbo.sysjobschedules AS js

 WHERE (js.schedule_id = sdel.schedule_id)))

 END

 -- Delete the job history if requested

 IF (@delete_history = 1)

 BEGIN

 DELETE FROM msdb.dbo.sysjobhistory

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 END

 -- All done

 COMMIT TRANSACTION

 -- Now notify agent to delete the job.

 IF(EXISTS(SELECT * FROM #temp_jobs_to_delete WHERE job_is_cached 0))

 BEGIN

 DECLARE @nt_user_name NVARCHAR(100)

 SELECT @nt_user_name = ISNULL(NT_CLIENT(), ISNULL(SUSER_SNAME(), FORMATMESSAGE(14205)))

 --Call the xp directly. sp_sqlagent_notify checks sysjobs_view and the record has already been deleted

 EXEC master.dbo.xp_sqlagent_notify NJ, @job_id, 0, 0, ND, @nt_user_name, 1, @@trancount, NULL, NULL

 END

 END

 ELSE

 -- Do the delete (for all jobs originating from the specific server)

 IF (@originating_server IS NOT NULL)

 BEGIN

 EXECUTE msdb.dbo.sp_delete_all_msx_jobs @msx_server = @originating_server

 -- NOTE: In this case there is no need to propagate the delete via sp_post_msx_operation

 -- since this type of delete is only ever performed on a TSX.

 END

 IF (OBJECT_ID(Ntempdb.dbo.#temp_jobs_to_delete, U) IS NOT NULL)

 DROP TABLE #temp_jobs_to_delete

 RETURN(0) -- 0 means success

END

从上面SQL脚本中可以看到在删除msdb.dbo.sysjobsteps之前，该存储过程执行了msdb.dbo.sp_delete_job_references

ALTER PROCEDURE [dbo].[sp_delete_job_references]

 @notify_sqlagent BIT = 1

AS

BEGIN

 DECLARE @deleted_job_id UNIQUEIDENTIFIER

 DECLARE @task_id_as_char VARCHAR(10)

 DECLARE @job_is_cached INT

 DECLARE @alert_name sysname

 DECLARE @maintplan_plan_id UNIQUEIDENTIFIER

 DECLARE @maintplan_subplan_id UNIQUEIDENTIFIER

 -- Keep SQLServerAgents cache in-sync and cleanup any webtask cross-references to the deleted job(s)

 -- NOTE: The caller must have created a table called #temp_jobs_to_delete of the format

 -- (job_id UNIQUEIDENTIFIER NOT NULL, job_is_cached INT NOT NULL).

 DECLARE sqlagent_notify CURSOR LOCAL

 FOR

 SELECT job_id, job_is_cached

 FROM #temp_jobs_to_delete

 OPEN sqlagent_notify

 FETCH NEXT FROM sqlagent_notify INTO @deleted_job_id, @job_is_cached

 WHILE (@@fetch_status = 0)

 BEGIN

 -- NOTE: We only notify SQLServerAgent if we know the job has been cached

 IF(@job_is_cached = 1 AND @notify_sqlagent = 1)

 EXECUTE msdb.dbo.sp_sqlagent_notify @op_type = NJ,

 @job_id = @deleted_job_id,

 @action_type = ND

 IF (EXISTS (SELECT *

 FROM master.dbo.sysobjects

 WHERE (name = Nsp_cleanupwebtask)

 AND (type = P)))

 BEGIN

 SELECT @task_id_as_char = CONVERT(VARCHAR(10), task_id)

 FROM msdb.dbo.systaskids

 WHERE (job_id = @deleted_job_id)

 IF (@task_id_as_char IS NOT NULL)

 EXECUTE (master.dbo.sp_cleanupwebtask @taskid =  + @task_id_as_char)

 END

 -- Maintenance plan cleanup for SQL 2005.

 -- If this job came from another server and it runs a subplan of a

 -- maintenance plan, then delete the subplan record. If that was

 -- the last subplan still referencing that plan, delete the plan.

 -- This removes a distributed maintenance plan from a target server

 -- once all of jobs from the master server that used that maintenance

 -- plan are deleted.

 SELECT @maintplan_plan_id = plans.plan_id, @maintplan_subplan_id = plans.subplan_id

 FROM sysmaintplan_subplans plans, sysjobs_view sjv

 WHERE plans.job_id = @deleted_job_id

 AND plans.job_id = sjv.job_id

 AND sjv.master_server = 1 -- This means the job came from the master

 IF (@maintplan_subplan_id is not NULL)

 BEGIN

 EXECUTE sp_maintplan_delete_subplan @subplan_id = @maintplan_subplan_id, @delete_jobs = 0

 IF (NOT EXISTS (SELECT *

 FROM sysmaintplan_subplans

 where plan_id = @maintplan_plan_id))

 BEGIN

 DECLARE @plan_name sysname

 SELECT @plan_name = name

 FROM sysmaintplan_plans

 WHERE id = @maintplan_plan_id

 EXECUTE sp_dts_deletepackage @name = @plan_name, @folderid = 08aa12d5-8f98-4dab-a4fc-980b150a5dc8 -- this is the guid for Maintenance Plans

 END

 END

 FETCH NEXT FROM sqlagent_notify INTO @deleted_job_id, @job_is_cached

 END

 DEALLOCATE sqlagent_notify

 -- Remove systaskid references (must do this AFTER sp_cleanupwebtask stuff)

 DELETE FROM msdb.dbo.systaskids

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 -- Remove sysdbmaintplan_jobs references (legacy maintenance plans prior to SQL 2005)

 DELETE FROM msdb.dbo.sysdbmaintplan_jobs

 WHERE job_id IN (SELECT job_id FROM #temp_jobs_to_delete)

 -- Finally, clean up any dangling references in sysalerts to the deleted job(s)

 DECLARE sysalerts_cleanup CURSOR LOCAL

 FOR

 SELECT name

 FROM msdb.dbo.sysalerts

 WHERE (job_id IN (SELECT job_id FROM #temp_jobs_to_delete))

 OPEN sysalerts_cleanup

 FETCH NEXT FROM sysalerts_cleanup INTO @alert_name

 WHILE (@@fetch_status = 0)

 BEGIN

 EXECUTE msdb.dbo.sp_update_alert @name = @alert_name,

 @job_id = 0x00

 FETCH NEXT FROM sysalerts_cleanup INTO @alert_name

 END

 DEALLOCATE sysalerts_cleanup

END

而msdb.dbo.sp_delete_job_references这个存储过程又接着调用了存储过程sp_maintplan_delete_subplan，

ALTER PROCEDURE [dbo].[sp_maintplan_delete_subplan]

 @subplan_id UNIQUEIDENTIFIER,

 @delete_jobs BIT = 1

AS

BEGIN

 DECLARE @retval INT

 DECLARE @job UNIQUEIDENTIFIER

 DECLARE @jobMsx UNIQUEIDENTIFIER

 SET NOCOUNT ON

 SET @retval = 0

 -- Raise an error if the @subplan_id doesnt exist

 IF( NOT EXISTS(SELECT * FROM sysmaintplan_subplans WHERE subplan_id = @subplan_id))

 BEGIN

 DECLARE @subplan_id_as_char VARCHAR(36)

 SELECT @subplan_id_as_char = CONVERT(VARCHAR(36), @subplan_id)

 RAISERROR(14262, -1, -1, @subplan_id, @subplan_id_as_char)

 RETURN(1)

 END

 BEGIN TRAN

 --Is there an Agent Job/Schedule associated with this subplan?

 SELECT @job = job_id, @jobMsx = msx_job_id

 FROM msdb.dbo.sysmaintplan_subplans 

 WHERE subplan_id = @subplan_id

 EXEC @retval = msdb.dbo.sp_maintplan_delete_log @subplan_id = @subplan_id

 IF (@retval 0)

 BEGIN

 ROLLBACK TRAN

 RETURN @retval

 END

 -- Delete the subplans table entry first since it has a foreign

 -- key constraint on its job_id existing in sysjobs.

 DELETE msdb.dbo.sysmaintplan_subplans 

 WHERE (subplan_id = @subplan_id)

 IF (@delete_jobs = 1)

 BEGIN

 --delete the local job associated with this subplan

 IF (@job IS NOT NULL)

 BEGIN

 EXEC @retval = msdb.dbo.sp_delete_job @job_id = @job, @delete_unused_schedule = 1

 IF (@retval 0)

 BEGIN

 ROLLBACK TRAN

 RETURN @retval

 END

 END

 --delete the multi-server job associated with this subplan.

 IF (@jobMsx IS NOT NULL)

 BEGIN 

 EXEC @retval = msdb.dbo.sp_delete_job @job_id = @jobMsx, @delete_unused_schedule = 1

 IF (@retval 0)

 BEGIN

 ROLLBACK TRAN

 RETURN @retval

 END

 END

 END

 COMMIT TRAN

 RETURN (0)

END

也就是说最终在此存储过程sp_maintplan_delete_subplan中删除msdb.dbo.sysmaintplan_subplans 表中的记录。 过程梳理清楚了，那么逆向推导看看具体原因

如下所示，删除msdb.dbo.sysmaintplan_subplans中对应记录语句如下

此时要看参数@subplan_id的取值，它从msdb.dbo.sp_delete_job_references中传入，如下所示

ALTER PROCEDURE [dbo].[sp_maintplan_delete_subplan]

    @subplan_id UNIQUEIDENTIFIER,

    @delete_jobs BIT = 1

AS

…………………………………………………………………

在[dbo].[sp_delete_job_references]中，它的值来自于 @maintplan_subplan_id变量，最终来自于sysmaintplan_subplans系统表

SELECT @maintplan_plan_id = plans.plan_id, 

 @maintplan_subplan_id = plans.subplan_id

 FROM sysmaintplan_subplans plans, sysjobs_view sjv

 WHERE plans.job_id = @deleted_job_id

 AND plans.job_id = sjv.job_id

 AND sjv.master_server = 1 -- This means the job came from the master

我通过DAC登录数据库（sysmaintplan_subplans是内部对象，此对象在DAC下才可以访问），查询如下所示，你会发现无记录，也就是说@maintplan_subplan_id为NULL值，导致后面执行删除msdb.dbo.sysmaintplan_subplans表中记录时，没有真正的删除记录。

 

最后发现导致查询无记录的原因在于查询条件sjv.master_server = 1

sysjob_view视图代码如下所示：

FROM msdb.dbo.sysjobs as jobs

 JOIN msdb.dbo.sysoriginatingservers_view as svr

 ON jobs.originating_server_id = svr.originating_server_id

 --LEFT JOIN msdb.dbo.sysjobservers js ON jobs.job_id = js.job_id

WHERE (owner_sid = SUSER_SID())

 OR (ISNULL(IS_SRVROLEMEMBER(Nsysadmin), 0) = 1)

 OR (ISNULL(IS_MEMBER(NSQLAgentReaderRole), 0) = 1)

 OR ( (ISNULL(IS_MEMBER(NTargetServersRole), 0) = 1) AND

 (EXISTS(SELECT * FROM msdb.dbo.sysjobservers js

 WHERE js.server_id 0 AND js.job_id = jobs.job_id))) -- filter out local jobs

CREATE VIEW dbo.sysoriginatingservers_view(originating_server_id, originating_server, master_server)

AS

 SELECT

 0 AS originating_server_id,

 UPPER(CONVERT(sysname, SERVERPROPERTY(ServerName))) AS originating_server,

 0 AS master_server

 UNION

 SELECT

 originating_server_id,

 originating_server,

 master_server

 FROM

 dbo.sysoriginatingservers

原来master_server的值是默认的。因为表dbo.sysoriginatingservers无记录。至此，可以看出，这应该是SQL Server 2005的一个BUG来的。

解决方案：

手工删除系统表msdb.dbo.sysmaintplan_subplans中的记录，然后删除该作业。问题搞定。

DELETE FROM msdb.dbo.sysmaintplan_subplans WHERE SUBPLAN_ID=B9A639EB-955D-4AE6-B69E-860145C133E7;

USE [msdb]

GO

EXEC msdb.dbo.sp_delete_job @job_id=Nce8cb4ad-c91f-45bc-9e21-b50947063fba, @delete_unused_schedule=1

GO

SQL注入攻击(SQL注入(SQLi)攻击)-报错注入 页面没有显示位 , 但有数据库的报错信息时 , 可使用报错注入 报错注入是最常用的注入方式 , 也是使用起来最方便(我觉得)的一种注入方式
潇湘隐者 网名潇湘隐者/潇湘剑客、英文名Kerry，兴趣广泛，广泛涉猎，个性随意，不善言辞。执意做一名会写代码的DBA，混迹于IT行业