Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_browsereplcmds

  No additional text.


Syntax

create procedure sys.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

 
Last revision 2008RTM
See also

  sp_instdist (Procedure)
sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash