Simply inserting to temp table and failing Can anyone tell me why this won't work for me?

CREATE TABLE #UndistributedCmds
(
pendingcmdcount int,
estimatedprocesstime INT
)
INSERT INTO #UndistributedCmds

EXEC sp_replmonitorsubscriptionpendingcmds ...


The error:


  Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 152An INSERT EXEC statement cannot be nested.


The error seems to imply that line 152 of the stored procedure definition is the issue, but I'm having trouble understanding this. Is this happening because the stored procedure itself has multiple stored procedures within it?
The error is happening because the stored procedure also has a #temp table or table variable and inserts into it using insert...exec. You can see it by looking at its OBJECT_DEFINITION():

insert into @countab (pendingcmdcount)  
  exec @retcode = sys.sp_MSget_repl_commands   


So basically you asking for this (pseudo-code):

INSERT INTO #outer EXEC
  (SELECT * FROM (INSERT INTO #inner EXEC ... ));


This nesting of insert...exec calls is prohibited in SQL Server, for reasons that are unknown/undocumented. The typical workaround is:

SELECT * INTO #UndistributedCmds
  FROM OPENQUERY([LoopbackLinkedServer], N'EXEC procedure...');


You'll need a linked server that references the local server machine, and it will need the attribute "data access" set to true...