Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadd_replcmds_mcit

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSadd_replcmds_mcit
@publisher_database_id int,
@publisher_id smallint,
@publisher_db sysname,
@data varbinary( 1595 ),
@1data varbinary(1595) = NULL,
@2data varbinary(1595) = NULL,
@3data varbinary(1595) = NULL,
@4data varbinary(1595) = NULL,
@5data varbinary(1595) = NULL,
@6data varbinary(1595) = NULL,
@7data varbinary(1595) = NULL,
@8data varbinary(1595) = NULL,
@9data varbinary(1595) = NULL,
@10data varbinary(1595) = NULL,
@11data varbinary(1595) = NULL,
@12data varbinary(1595) = NULL,
@13data varbinary(1595) = NULL,
@14data varbinary(1595) = NULL,
@15data varbinary(1595) = NULL,
@16data varbinary(1595) = NULL,
@17data varbinary(1595) = NULL,
@18data varbinary(1595) = NULL,
@19data varbinary(1595) = NULL,
@20data varbinary(1595) = NULL,
@21data varbinary(1595) = NULL,
@22data varbinary(1595) = NULL,
@23data varbinary(1595) = NULL,
@24data varbinary(1595) = NULL,
@25data varbinary(1595) = NULL,
@26data varbinary(1595) = NULL
AS

    SET NOCOUNT ON

    DECLARE @maxOffset  binary(4)
    		,@maxSeqNo  varbinary(16)
    		,@date datetime
    		,@x int
    		,@tempdata varbinary(1595)
    		,@prevdata varbinary(1595)

	DECLARE @xactId			varbinary(16),
			@xactSeqNo		varbinary(16),
			@artId			int,
			@cmdId			int,
			@cmdType		int,
			@fIncomplete	bit,
			@cmdLen			int,
			@originator_id	int,
			@origSrvLen		int,
			@origDbLen		int,
			@hashKey		int,
			-- @origPublId		int,
			-- @origDbVersion	int,
			-- @origLSN		varbinary(10),
			@cmdText		varbinary(1595),
			@originator		sysname,
			@originatorDb	sysname

	
    -- security check
    -- only db_owner can execute this
    
    if (is_member ('db_owner') != 1)
    begin
        raiserror(14260, 16, -1)
        return (1)
    end

	select @date 		= GETDATE(),
			@maxOffset 	= 0,
			@xactSeqNo 	= substring( @data, 11, 10 ),
			@cmdId		= substring( @data, 25, 4)

    -- Look for the first insert into MS_repl_transactions
   	select @maxSeqNo = max(xact_seqno)
   		from MSrepl_transactions
		where publisher_database_id = @publisher_database_id

	 -- Check if it is the same transaction
	if @xactSeqNo = substring( @maxSeqNo, 1, 10 )
	begin
		IF @cmdId = 1
			select @maxOffset = substring(@maxSeqNo, 11, 4) + 1
		else
			select @maxOffset = substring(@maxSeqNo, 11, 4)
			
		if (@maxOffset > 0)
			select @xactSeqNo = @xactSeqNo + @maxOffset
	end

	if @maxOffset is NULL
		select @maxOffset = 0
		
	select @maxOffset = @maxOffset + 1

	select @x = 0
	select @tempdata = @data
	while @x <= 26
	begin
		select @prevdata = @tempdata	
		select @tempdata = CASE @x
			when 0 then @data
			when 1 then @1data
			when 2 then @2data
			when 3 then @3data
			when 4 then @4data
			when 5 then @5data
			when 6 then @6data
			when 7 then @7data
			when 8 then @8data
			when 9 then @9data
			when 10 then @10data
			when 11 then @11data
			when 12 then @12data
			when 13 then @13data
			when 14 then @14data
			when 15 then @15data
			when 16 then @16data
			when 17 then @17data
			when 18 then @18data
			when 19 then @19data
			when 20 then @20data
			when 21 then @21data
			when 22 then @22data
			when 23 then @23data
			when 24 then @24data
			when 25 then @25data
			when 26 then @26data
		end
		
		IF @tempdata is null
      		goto END_CMDS

		-- We will now breakup the binary data. Check HP_FIXED_DATA
		-- in publish.cpp for all of the offsets listed below...
		select @xactId 			= substring( @tempdata, 1, 10),
				-- @xactSeqNo   = see directly below for the setting of this value : usually = substring( @tempdata, 11, 10),
				@artId			= substring( @tempdata, 21, 4),
				@cmdId			= substring( @tempdata, 25, 4),
				@cmdType		= substring( @tempdata, 29, 4),
				@fIncomplete	= convert(bit, substring( @tempdata, 33, 1)),
				@cmdLen			= substring( @tempdata, 34, 2),
				@origSrvLen		= substring( @tempdata, 36, 2),
				@origDbLen		= substring( @tempdata, 38, 2),
				@hashKey		= substring( @tempdata, 40, 2),
				-- @origPublId		= Not used since MaxCMDsInTran is not supported in PeerToPeer. Usually would be : substring( @tempdata, 42, 4),
				-- @origDbVersion	= Not used since MaxCMDsInTran is not supported in PeerToPeer. Usually would be : substring( @tempdata, 46, 4),
				-- @origLSN			= Not used since MaxCMDsInTran is not supported in PeerToPeer. Usually would be : substring( @tempdata, 50, 10),
				@cmdText		= substring( @tempdata, 60, @cmdLen)
				-- @originator  = only done below if an originator len is detected : usually = substring( @tempdata, 60 + @cmdLen, @origSrvLen)
				-- @originatorDb= only done below if an originator len is detected : usually = substring( @tempdata, 60 + @cmdLen + @origSrvLen, @origDbLen)
			
		if @x != 0
		begin
			if(substring( @tempdata, 11, 10 ) = substring( @prevdata, 11, 10 )) --same tran
			begin
	    		IF @cmdId = 1
				begin
					select @xactSeqNo = substring( @tempdata, 11, 10 ) + @maxOffset
					select @maxOffset  = @maxOffset + 1
				end
			end
			else
				select @xactSeqNo = substring( @tempdata, 11, 10 )
		end

		-- first command in tran
	    IF @cmdId = 1
	    begin
			INSERT INTO MSrepl_transactions
				VALUES (@publisher_database_id, @xactId, @xactSeqNo, @date)
		end

		-- Now insert into MSrepl_commands
		if( @cmdType in( 37,38 ) )
		begin
			select @cmdType = 38 - @cmdType
			exec sp_MSset_syncstate @publisher_id, @publisher_db, @artId, @cmdType, @xactSeqNo
			select @cmdType = (38 - @cmdType) | 0x80000000
		end
		-- Check all posted cmds of SQLCMD type to see if they are tracer records
		-- sql cmd type is (47 | 0x40000000 ) or 1073741871
		else if @cmdType = 1073741871
		begin
			declare @tracer_id 	int,
					@retcode	int
			
			select @tracer_id = cast(cast(@cmdText as nvarchar) as int)

			exec @retcode = sys.sp_MSupdate_tracer_history @tracer_id = @tracer_id
			if @retcode <> 0 or @@error <> 0
				return 1
		end
		
		-- only add it if the command is not empty
	   	if @cmdLen > 0
		begin
	        -- Get the originator_id for the first command
	        if @origSrvLen <> 0 and @origDbLen <> 0
	        begin
	        	select @originator_id 	= null,
           				@originator		= substring( @tempdata, 60 + @cmdLen, @origSrvLen),
						@originatorDb	= substring( @tempdata, 60 + @cmdLen + @origSrvLen, @origDbLen)
	
	            select @originator_id = id
	            	from MSrepl_originators
	            	where publisher_database_id = @publisher_database_id
	                	and UPPER(srvname) = UPPER(@originator)
	                	and dbname = @originatorDb
	                	and publication_id is NULL		-- @origPublId
	                	and dbversion is NULL			-- @origDbVersion

	            if @originator_id is null
	            begin
	                insert into MSrepl_originators (publisher_database_id, srvname, dbname, publication_id, dbversion)
	                	values (@publisher_database_id, UPPER(@originator), @originatorDb, NULL, NULL)

	                select @originator_id = @@identity
	            end
	        end
	        else
	            select @originator_id = 0
		
			INSERT INTO MSrepl_commands
			(
				publisher_database_id,
				xact_seqno,
				type,
				article_id,
				originator_id,
				command_id,
				partial_command,
				hashkey,
				originator_lsn,
				command
			)
			VALUES
			(
				@publisher_database_id,
				@xactSeqNo,
				@cmdType,
				@artId, 					
				@originator_id,
				@cmdId,
				@fIncomplete,
				@hashKey,					
				NULL,		-- @origLSN
				@cmdText
			)
	    end

		select @x = @x + 1

	end

END_CMDS:
    IF @@ERROR <> 0
		return (1)

 
Last revision 2008RTM
See also

  sp_instdist (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