How to use TSQL to monitor replication status

To monitor the status of replication in SQL 2000 we insert the resuts of sp_MShelp_replication_status into a temp table and review the status. In SQL 2005 this stored procedure has changed and now does it's own insert into execute command. Since insert execute commands can not be nested we must find another way to programically monitor replication using TSQL. We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database. It must be hidden somehow. Any ideas?

Thanks,

Danny

>> We could call sys.sp_replmonitorhelppublisherhelper but we can't seem to find it in the distribution database.

What do you mean? Do you mean you can call this stored proc but can't find it in management stuidio. This is a system stored procedure

The general topic on monitering replication in T-SQL can be found at http://msdn2.microsoft.com/fr-fr/library/ms147874.aspx

BTW, to work around nested INSERT EXEC issue, you can take a look at OPENROWSET (Note: server option "Ad Hoc Distributed Queries" is necessary, which is off by default).

Peng

|||

Please see Books Online topic "How to: Programmatically Monitor Replication (Replication Transact-SQL Programming)" - http://msdn2.microsoft.com/en-us/library/ms147874.aspx. There's also a similar article to do this via RMO.

|||

Look at topics:

How to: Programmatically Monitor Replication (Replication Transact-SQL Programming): http://msdn2.microsoft.com/zh-cn/library/ms147874.aspx

How to: Programmatically Monitor Replication (RMO Programming): http://msdn2.microsoft.com/fr-fr/library/ms147926.aspx

And yes, sp_replmonitorhelppublisherhelper resides in the resource database and not in distribution database. Hence you are not able to see it. You will need to start the server in single user mode to view its definition.

Also look at: sp_replmonitorhelpmergesession and sp_replmonitorhelpmergesessiondetail and see if they are heplful for you.

|||Thanks. I'll look at using openrowset. sys.sp_replmonitorhelppublisher checks for access and database name. The it calls sys.sp_replmonitorhelppublisherhelper as shown below. This system stored procedure or extended stored procedure doesn't appear to be exposed. I've checked all user and system databases for it's name and don't see it anywhere. Anyone know how that's possible or am I missing it?

select @.cmd = quotename(@.distdb) + '.sys.sp_replmonitorhelppublisherhelper'

exec @.retcode = @.cmd @.publisher = @.publisher, @.refreshpolicy = @.refreshpolicy

sql