1

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 152
An 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?

  • where you inserted into #UndistributedCmds table – wiretext Oct 8 '15 at 16:00
  • 2
    More accurately, the dbo.sp_replmonitorsubscriptionpendingcmds procedure cannot be calling a procedure ( or one that calls a procedure, etc. ) which does an INSERT EXEC as well. – Avarkx Oct 8 '15 at 16:00
7

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...

|improve this answer|||||

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.