5

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.

4

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)
6

Because Microsoft hasn't learned to create unique object names. If you look at the definition for sp_delete_job, you will find this around line 115, a slightly different version of #temp_jobs_to_delete:

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

Now, consider that SQL Server may take a slightly different path when generating estimated execution plans for a set of nested procedure calls than if it simply ran the code. It likely does some kind of object-level collection that recognizes the same #table is used and assumes that it must adhere to the same schema (even if logically the two #temp tables would never cross paths).

Now, it is possible that this code will actually break when you try to delete a job and the properties of that job lead to sp_delete_all_msx_jobs to be called, which I suspect in your case did not happen on actual execution (nor in mine). I did not have the energy to set up MSX to evaluate. But remember some of the other things that happen during parsing that couldn't possibly be a problem on actual execution:

IF (1 = 1)
  CREATE TABLE #x(i INT);
ELSE
  CREATE TABLE #x(d DATETIME);

Illogical parsing error:

Msg 2714, Level 16, State 1
There is already an object named '#x' in the database.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.