Inserting stored procedure into temp table error

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Inserting stored procedure into temp table error

Author

Topic

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2011-06-14 : 17:24:19

I have transactional replication set up. I would like to capture the amount of commands stored in the distribution database that are waiting to be applied to the subscriber.

This can be done using the sp_replmonitorsubscriptionpendingcmds stored procedure.

So I created a temp table and tried to enter the results of the stored procedure into this temp table.

I get the error message:
Msg 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds, Line 139
An INSERT EXEC statement cannot be nested.

I don't know why I'm getting this error message as I'm only executing one stored procedure, not using nested stored procedures.

Here is my code:


create table #pendingcmds
(pendingcmdcount int,
estimatedprocesstime int)

insert into #pendingcmds (pendingcmdcount, estimatedprocesstime)
exec distribution..sp_replmonitorsubscriptionpendingcmds @publisher = 'RANVIR\SQL200801',
@publisher_db = 'Ranvir',
@publication = 'Ranvir_all',
@subscriber = 'RANVIR\SQL200802',
@subscriber_db = 'Ranvir',
@subscription_type = 0

Any ideas?

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 17:27:33

Because sp_replmonitorsubscriptionpendingcmds uses INSERT...EXEC in its own code.

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2011-06-14 : 17:32:02

Is there any way around this? I really need to capture this information (the number of commands stored in the distribution database that are waiting to be applied to the subscriber)

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-06-14 : 17:43:23

My suggestion is to copy the code from that procedure and tweak it to insert the data into your own history table. DO NOT modify the original, and DO NOT name it with an sp_ prefix.

ranvir_2k
Posting Yak Master

180 Posts

Posted - 2011-06-14 : 17:54:22

I found the solution:

create table #pendingcmds
(pendingcmdcount int,
estimatedprocesstime int)

Insert INTO #pendingcmds (pendingcmdcount, estimatedprocesstime)
select * FROM OPENROWSET('SQLOLEDB',
'Server=RANVIR\SQL200801;Trusted_Connection=yes;',
'set fmtonly off;
exec distribution..sp_replmonitorsubscriptionpendingcmds
@publisher=''RANVIR\SQL200801'',
@publisher_db=''Ranvir'',
@publication=''Ranvir_all'',
@subscriber=''RANVIR\SQL200802'',
@subscriber_db=''Ranvir'',
@subscription_type=0')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources