Why does requesting the "estimated execution plan" for sp_delete_job throw an error?

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.

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.

StackExchange Database Administrators Q#118492, answer score: 6