May 2, 2012

sp_MSadd_replcmds_mcit (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

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

MetaData:

 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,
@originalXactSeqNo varbinary(10)

--
-- 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)

select @originalXactSeqNo = @xactSeqNo

-- 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
-- handle nonsync subscription setup when command type is
-- REPL_NOSYNC_SUBSCRIPTION_SETUP_LOG_CMD (54)
if ((@cmdType & 0xFFFFFFF) = 54)
begin
-- When logreader gets a log record with this type,
-- the MSnosyncsubsetup table should already exist,
-- report the failure if it does not exist.
if (object_id(N'dbo.MSnosyncsubsetup', 'U')) is NULL
begin
goto Failure
end
else
begin
declare @nosyncCommandStr nvarchar(max),
@publisher sysname,
@publication sysname,
@article sysname,
@subscriber sysname,
@destination_db sysname,
@subscriptionlsn binary(10),
@lsnsource tinyint,
@originator_publication_id int,
@originator_db_version int,
@originator_meta_data nvarchar(max),
@nosync_setup_script nvarchar(max),
@next_valid_lsn binary(10),
@next_valid_lsn_from_log binary(10)

select @originator_publication_id = CAST(CAST(substring(@cmdText, 1, 4) AS nvarchar) AS int),
@next_valid_lsn_from_log = CAST(substring(@cmdText, 5, 10) AS binary(10))
if @@error <> 0 goto Failure

-- Verify that the number of parameters is correct before using
-- these parameters in sp_MSsetupnosyncsubwithlsnatdist
if ((select count(*) from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log) <> 13) goto Failure
if @@error <> 0 goto Failure

select @publisher = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'publisher') as sysname),
@publisher_db = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'publisher_db') as sysname),
@publication = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'publication') as sysname),
@article = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'article') as sysname),
@subscriber = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'subscriber') as sysname),
@destination_db = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'destination_db') as sysname),
@subscriptionlsn = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'subscriptionlsn') as binary(10)),
@lsnsource = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'lsnsource') as tinyint),
@originator_publication_id = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'originator_publication_id') as int),
@originator_db_version = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'originator_db_version') as int),
@originator_meta_data = (select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'originator_meta_data'),
@nosync_setup_script = (select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'nosync_setup_script'),
@next_valid_lsn = cast((select parameterValue
from dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log
and parameterName = N'next_valid_lsn') as binary(10))
if @@error <> 0 goto Failure

select @nosyncCommandStr = N'exec sp_MSsetupnosyncsubwithlsnatdist
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@subscriptionlsn = @subscriptionlsn,
@lsnsource = @lsnsource,
@originator_publication_id = @originator_publication_id,
@originator_db_version = @originator_db_version,
@originator_meta_data = @originator_meta_data,
@nosync_setup_script = @nosync_setup_script,
@next_valid_lsn = @next_valid_lsn'


exec sp_executesql
@stmt = @nosyncCommandStr,
@params = N'@publisher sysname,
@publisher_db sysname,
@publication sysname,
@article sysname,
@subscriber sysname,
@destination_db sysname,
@subscriptionlsn binary(10),
@lsnsource tinyint,
@originator_publication_id int,
@originator_db_version int,
@originator_meta_data nvarchar(max),
@nosync_setup_script nvarchar(max),
@next_valid_lsn binary(10)'
,
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@subscriptionlsn = @subscriptionlsn,
@lsnsource = @lsnsource,
@originator_publication_id = @originator_publication_id,
@originator_db_version = @originator_db_version,
@originator_meta_data = @originator_meta_data,
@nosync_setup_script = @nosync_setup_script,
@next_valid_lsn = @next_valid_lsn

if @@error <> 0 goto Failure

end -- end of if (object_id(N'dbo.MSnosyncsubsetup, 'U')) is NOT NULL

-- Upon success of the execution of sp_MSsetupnosyncsubwithlsnatdist,
-- clean up the MSnosyncsubsetup table by deleting the parameters
-- regarding this specified nonsync subscription
delete dbo.MSnosyncsubsetup
where publisher_database_id = @publisher_database_id
and publication_id = @originator_publication_id
and artid = @artId
and next_valid_lsn = @next_valid_lsn_from_log

if @@error <> 0 goto Failure

goto Continue_next_command
Failure:
return 1
Continue_next_command:
end
else -- i.e., when (@cmdType & 0xFFFFFFF) is NOT 54
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 -- end of i.e., when (@cmdType & 0xFFFFFFF) in (54, 55, 56)
end -- end of if @cmdLen > 0

select @x = @x + 1

end

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

No comments:

Post a Comment

Total Pageviews