SQL Server – Why Does Requesting Estimated Execution Plan for sp_delete_job Throw an Error?

sql serversql-server-agent

I'm attempting to look at the estimated execution plan for the following T-SQL statement:

EXEC msdb.dbo.sp_delete_job @job_id = '3A015189-F4EB-439B-9CA0-27AFB74719D8'
    , @originating_server = '(LOCAL)'
    , @delete_history = 0
    , @delete_unused_schedule = 0;

When I do that, SQL Server throws the following error:

Msg 213, Level 16, State 1, Procedure sp_delete_all_msx_jobs, Line 25

Column name or number of supplied values does not match table definition.

The definition of sp_delete_all_msx_jobs contains the following pertinent lines starting at line 25 (wrapped for readability):

INSERT INTO #temp_jobs_to_delete
SELECT sjv.job_id, 
    CASE sjs.server_id WHEN 0 THEN 1 ELSE 0 END,
    sjv.owner_sid
FROM msdb.dbo.sysjobs_view sjv
    LEFT OUTER JOIN msdb.dbo.sysjobservers sjs 
        ON (sjv.job_id = sjs.job_id)
WHERE (ISNULL(sjs.server_id, 0) = 0)
    AND (sjv.originating_server = @msx_server)

Line 14 of that stored proc shows the definition of #temp_jobs_to_delete is:

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

That looks valid to me. Why is "Display Estimated Execution Plan" returning an error?

I can run this on SQL Server 2008R2, and 2012; both return the error. I'm using SSMS 2012.

Best Answer

It looks like there are two conflicting definitions of #temp_jobs_to_delete.

One comes from sp_delete_job:

-- Note: This temp table is referenced by msdb.dbo.sp_delete_job_references,
-- so it cannot be declared as a local table.
CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
                                   job_is_cached INT NOT NULL)

And the other comes from sp_delete_all_msx_jobs:

-- Delete all the jobs that originated from the MSX
-- 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, owner_sid VARBINARY(85) NOT NULL)

I can reproduce the error with the following simplified script:

CREATE PROC #test1 AS
BEGIN
    CREATE TABLE #temp_jobs_to_delete (
        job_id UNIQUEIDENTIFIER NOT NULL PRIMARY KEY CLUSTERED,
        job_is_cached INT NOT NULL
    )

    IF (GETDATE() < '1/1/2015') -- Always false
        EXEC #test2

    DROP TABLE #temp_jobs_to_delete
END
GO

CREATE PROC #test2 AS
BEGIN
    CREATE TABLE #temp_jobs_to_delete (job_id UNIQUEIDENTIFIER NOT NULL,
        job_is_cached INT NOT NULL, owner_sid VARBINARY(85) NOT NULL)

    INSERT INTO #temp_jobs_to_delete
    SELECT NEWID() AS job_id, 0 AS job_is_cached, 0x1234 AS owner_sid
END
GO

-- This statement runs successfully, but fails to compile when viewing an estimated plan
EXEC #test1
GO

I think that what is happening is that SQL Server uses the definition of the first creation of #temp_jobs_to_delete to try to compile the remainder of the plan. In fact, I can't see anywhere in sp_delete_job (or the procs it calls) that would drop #temp_jobs_to_delete prior to invoking sp_delete_all_msx_jobs. I could be missing something, but it seems like you would also get a run-time error if you invoke the procedure with a value for the @originating_server parameter, thereby causing the sp_delete_all_msx_jobs procedure to have to be invoked.

-- 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 that the @originating_server parameter has the following comment and Books Online specifies that it is for internal use only.

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