SQL 2005 replication history

One of the shortcomings of the new replication monitor is it's lack of history. SQL 2000 was a lot better at this IMO. So, to get this information you need to create a report etc using SQL along these lines:

@subscriber NVARCHAR(256) = NULL
, @dateFrom DATETIME
, @dateTo DATETIME

SELECT ms.publisher
, ms.publisher_db
, ms.subscriber
, ms.subscriber_db
, mh.comments
, mh.[time]
FROM dbo.MSmerge_subscriptions ms
INNER JOIN dbo.MSmerge_agents ma
ON ms.subscriber = ma.subscriber_name
AND ms.subscriber_db = ma.subscriber_db
INNER JOIN dbo.MSmerge_sessions mse
ON ma.id = mse.agent_id
INNER JOIN dbo.MSmerge_history mh
ON mse.session_id = mh.session_id
AND mse.agent_id = mh.agent_id
WHERE ms.subscription_type = 0
AND (ms.subscriber + ':' + ms.subscriber_db = @subscriber OR @subscriber IS NULL)
AND mh.[time] BETWEEN @dateFrom AND @dateTo


To get the subscribers :

SELECT DISTINCT subscriber + ':' + subscriber_db AS subscriber
FROM dbo.MSmerge_subscriptions
WHERE subscription_type = 0

Skip SQL Statement when user does not have permission or linked server missing

Had an interesting problem with a stored procedure that checked replication history. This SP included a local and remote server using linked server. Occasionally the link would drop, and also whenever replication was rebuilt the user would forget to set up the permissions to the re-generated distribution database. Either of these situations throw errors in the SQL, so that it doesn't complete. To save redoing the existing reports, I needed a solution whereby the remote sql is skipped if it doesn't work. The solution seemed to be to use TRY..CATCH, however there is an issue with this. TRY..CATCH does not work with errors produced over a linked server. After some head scratching it was found that this could be overcome using EXEC sp_executesql. Since the SQL will not be in a plan (it is, afterall, on a different server) then there is no performance issue here either. So, the SQL read along the lines of :

BEGIN TRY
SET @sqlstmt = 'SELECT xxx FROM remoteserver.database.dbo.yyy'
EXEC sp_executesql
END TRY
BEGIN CATCH
--ignore error from remote system
END CATCH