question

Fatherjack avatar image
Fatherjack asked

Inside replication

OK, we use replication here and once in a while it trips up and we have to re-initialise a subscription. We think we know what is happening - a row in the subscriber is being deleted and then the replicationis trying to delete it and cant find it. I figured I could use `sp_browsereplcmds` ([ http://msdn.microsoft.com/en-us/library/aa239422(SQL.80).aspx][1]) to investigate the pending commands and maybe resolve the issue without the need to re-initialise but when I try to run it I get this error: > Msg 6512, Level 16, State 27, > Procedure sp_browsereplcmds, Line 110 > Failed to initialize the Common > Language Runtime (CLR) v2.0.50727 with > HRESULT 0x80004005. You need to > restart SQL server to use CLR > integration features. Does it actually need CLR enabled? BoL doesn't mention it. [1]: http://msdn.microsoft.com/en-us/library/aa239422(SQL.80).aspx
sql-server-2005replicationclr
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Matt Whitfield avatar image
Matt Whitfield answered
Well, scripting out sp_browsereplcmds gives the following... I can't see any CLR reference in there, but it may well be that one of the magic internal functions that it uses requires it? Line 110 is: exec sys.sp_printstatement @query And the whole script is: create procedure [sp_browsereplcmds] @xact_seqno_start nchar(22) = NULL, --lower boundry of the query @xact_seqno_end nchar(22) = NULL, -- upper boundry of the query @originator_id int = NULL, -- limit query to the specified originator source @publisher_database_id int = NULL, -- limit query to the specified publication database @article_id int = NULL, -- limit query to the specified article @command_id int = NULL, -- limit query to the specified command id --the following apply to agent specific cmds (per sp_MSget_repl_commands called by agent) @agent_id int = NULL, -- when present all other input parameters except @xact_seqno_start are ignored @compatibility_level int = 9000000 -- use 7000000 if subscriber is SQL70 as declare @query nvarchar( 4000 ) ,@retcode int ,@dbname sysname select @dbname = db_name() if (sys.fn_MSrepl_isdistdb (@dbname ) != 1) begin raiserror(21482, 16, -1, 'sp_browsereplcmds', 'distribution') return (1) end -- security check. -- User must be member of 'replmonitor' role in distribution database -- exec @retcode = sys.sp_MSrepl_DistributorReplMonitorAccess @distribution_db = @dbname if @retcode != 0 or @@error != 0 return (1) --first let's find out if we are returning cmds specific to an agent if(@agent_id is not NULL) begin if @xact_seqno_start is null begin select @xact_seqno_start = N'0x00000000000000000000' end select @query = N'exec sys.sp_MSget_repl_commands ' + cast (@agent_id as nvarchar(12)) + N', ' + sys.fn_replreplacesinglequote(@xact_seqno_start ) + N', 0, ' + cast (@compatibility_level as nvarchar(12)) exec sys.sp_printagentstatement @query return 0 end --we know this is not specific to an agent, now go against the entire table. if( @command_id is not null ) begin if( @xact_seqno_start is null or @publisher_database_id is null ) begin raiserror( 21110, 16, -1 ) return 1 end else if @xact_seqno_start != @xact_seqno_end begin raiserror( 21109, 16, -1 ) return 1 end end if @xact_seqno_start is null begin select @xact_seqno_start = N'0x00000000000000000000' end if @xact_seqno_end is null begin select @xact_seqno_end = N'0xFFFFFFFFFFFFFFFFFFFF' end select @query = N'select cmds.article_id, cast (cmds.partial_command as tinyint), cmds.command, cmds.xact_seqno, ' select @query = @query + 'cmds.xact_seqno, 0, cmds.command_id, cmds.type, orgs.srvname, orgs.dbname, ' select @query = @query + 'cast (cmds.hashkey as smallint), orgs.publication_id, orgs.dbversion, cmds.originator_lsn ' select @query = @query + 'from MSrepl_commands cmds left join MSrepl_originators orgs on cmds.originator_id = orgs.id ' if @command_id is not null begin select @query = @query + N'where cmds.xact_seqno = ' + sys.fn_replreplacesinglequote(@xact_seqno_start ) end else begin select @query = @query + N'where cmds.xact_seqno >= ' + sys.fn_replreplacesinglequote(@xact_seqno_start) + N' and cmds.xact_seqno <= ' + sys.fn_replreplacesinglequote(@xact_seqno_end) end if @originator_id is not null begin select @query = @query + N' and cmds.originator_id = ' + convert( nvarchar, @originator_id ) end if @publisher_database_id is not null begin select @query = @query + N' and cmds.publisher_database_id = ' + convert( nvarchar, @publisher_database_id ) end if @article_id is not null begin select @query = @query + N' and cmds.article_id = ' + convert( nvarchar, @article_id ) end if @command_id is not null begin -- No need to use article_id and originator_id select @query = @query + N' and cmds.command_id >= ' + convert( nvarchar, @command_id ) select @query = @query + N' and cmds.command_id <= ( select min( command_id ) from MSrepl_commands c ' select @query = @query + N' where c.xact_seqno = ' + sys.fn_replreplacesinglequote(@xact_seqno_start ) select @query = @query + N' and c.publisher_database_id = ' + convert( nvarchar, @publisher_database_id ) select @query = @query + N' and c.command_id >= ' + convert( nvarchar, @command_id ) select @query = @query + N' and c.partial_command = 0 )' end select @query = @query + N' order by cmds.originator_id, cmds.publisher_database_id, cmds.xact_seqno, cmds.article_id, cmds.command_id asc' exec sys.sp_printstatement @query GO
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.