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)
