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 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy