October 13, 2010 at 6:18 pm
Requirement: To check if Replication is pending / how many rows pending to be distributed to the subscriber
I would need to write the status / output of 'sp_replmonitorsubscriptionpendingcmds' command to a table to check the # of rows pending to be applied at the subscriber.
I am doing:
USE distribution
GO
INSERT INTO distribution.dbo.dba_replication_status EXEC('sp_replmonitorsubscriptionpendingcmds @publisher=''PUBSRVR'', @publisher_db=''PUBDB'', @publication=''pub1'', @subscriber=''SUBSRVR'', @subscriber_db=''SUBDB'', @subscription_type=1');
GO
The row in the dba_replication_status table will be queried to see if > 0 rows are present in the first column.
It is throwing the message
mSG 8164, Level 16, State 1, Procedure sp_replmonitorsubscriptionpendingcmds,
Line 139
An INSERT EXEC statement cannot be nested
(0 row(s) affected)
I looked into the code of sp_replmonitorsubscriptionpendingcmds but that does not seem to be providing me with any other ideas. May be i am missing something.
I am doing select * from MSdistribution_status ; Even though the REPLMON shows pending commands
to be distributed to be zero, the UndelivCmdsInDistDB column gets me a value > 0;
Is there any table / view which will tell me the correct number of rows pending to be
distributed to the subscriber? I am trying to avoid INSERT INTO .. EXEC .. since it might result in NESTING error.
TIA
gk
May 3, 2011 at 5:07 pm
Any solution to this problem. We're trying to do the same thing?
Nick
May 4, 2011 at 12:47 pm
This worked for me locally.
SELECT *
INTO #Temp2
FROM OPENROWSET('SQLOLEDB', 'Server=MyPublisher;Trusted_Connection=yes;', 'set fmtonly off; exec distribution..sp_replmonitorsubscriptionpendingcmds @publisher=''MyPublisher'', @publisher_db=''MyPublishedDB'', @publication=''MyPublication'', @subscriber=''MySubscriber'', @subscriber_db=''MySubscriberDB'', @subscription_type=0')
May 4, 2011 at 1:17 pm
I ended up using OpenRowSet for sp_replmonitorhelpsubscription.
For the other repl cmd sp I ended up reengineering the proc pulling out just the pieces of code that I needed.
Thanks,
Nick
November 16, 2011 at 3:56 pm
Hi, Nick.
Did you ever find another option to the openrowset? I have run into the same issue.
Thanks,
Steve
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply