May 15, 2012

sp_MSgetpeertopeercommands (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_MSgetpeertopeercommands(nvarchar @publication
, nvarchar @article
, varbinary @snapshot_lsn)

MetaData:

 create procedure sys.sp_MSgetpeertopeercommands  
(
@publication sysname,
@article sysname = N'all',
@snapshot_lsn varbinary(16) = NULL,
@script_txt nvarchar(MAX) = NULL OUTPUT
)
as
begin
declare @retcode int,
@publisher sysname,
@publisher_db sysname,
@publisher_db_version int,
@is_p2p bit,
@pubid int,
@artid int,
@current_article sysname,
@dest_table sysname,
@dest_owner sysname,
@min_artid int,
@command nvarchar(4000),
@originator sysname,
@originator_db sysname,
@originator_publication sysname,
@originator_publication_id int,
@originator_db_version int,
@originator_lsn varbinary(16),
@originator_lsn_str varchar(2000),
@originator_version int,
@originator_id int

declare @OPT_ENABLED_FOR_P2P int

select @OPT_ENABLED_FOR_P2P = 0x1

declare @art_commands table (artid int NOT NULL, commands nvarchar(4000) collate database_default null, id int identity NOT NULL)

-- Security Check
exec @retcode = sys.sp_MSreplcheck_publish
if @@error <> 0 or @retcode <> 0
begin
return 1
end

-- set the publishd and database name
select @publisher = publishingservername(),
@publisher_db = db_name()

-- Parameter Check: @publication
if @publication IS NULL
begin
raiserror(14043, 16, -1, '@publication', 'sp_MSgetpeertopeercommands')
return 1
end

-- validate @publication
exec @retcode = sys.sp_validname @publication
if @@error <> 0 OR @retcode <> 0
return 1

-- get publication metadata
select @pubid = pubid,
@is_p2p = (options & @OPT_ENABLED_FOR_P2P)
from syspublications
where name = @publication
if @pubid is null
begin
raiserror (20026, 11, -1, @publication)
return 1
end

-- exit with no error if we are not a PeerToPeer publication
if @is_p2p = 0
begin
select @script_txt = NULL
return 0
end

-- retrieve the database version
exec sys.sp_MSgetdbversion @current_version = @publisher_db_version output
if @@error <> 0 OR @retcode <> 0
return 1

--
* This section creates commands to be posted to the distribution database
* that will insert all necessary MSsubscription_articles entries at the
* subscriber database. These entries are only used to validate topology,
* publication and subscription configurations. The commands should only
* be added for the non-database-restore case. Also, keep in mind that
* these commands will not be forwarded throughout the topology...
* Remember that we must include extended articles as well (SP, FN, V)
--
declare publication_art_cursor cursor local fast_forward for
select sa.artid,
sa.name,
sa.dest_table,
isnull(sa.dest_owner, N'dbo')
from sysextendedarticlesview sa
where sa.pubid = @pubid
and (@article = N'all'
or name = @article)
for read only

open publication_art_cursor

fetch publication_art_cursor into @artid, @current_article, @dest_table, @dest_owner

while (@@fetch_status <> -1)
begin
select @command = N'if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSaddsubscriptionarticles @publisher=N' + quotename(@publisher, N'''') +
N',@publisher_db=N' + quotename(@publisher_db, N'''') +
N',@publication=N' + quotename(@publication, N'''') +
N',@artid=' + cast(@artid as nvarchar) +
N',@article=N' + quotename(@current_article, N'''') +
N',@dest_table=N' + quotename(@dest_table, N'''') +
N',@dest_owner=N' + quotename(@dest_owner, N'''') +
N' end'

-- if this is NOT the first article for this subscription
-- (in other words this is an incremental add article) then
-- we will validate the individual article immediately after
-- adding it's sub_article meta-data. if this is not the case
-- (and this is the first article for the sub) then we will
-- perform one validation that will verify all articles instead
if @article != N'all'
begin
select @command = @command + N' ' +
N'if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSdetectinvalidpeersubscription @publisher=N' + quotename(@publisher, N'''') +
N',@publisher_db=N' + quotename(@publisher_db, N'''') +
N',@publication=N' + quotename(@publication, N'''') +
N',@article=N' + quotename(@current_article, N'''') +
N',@dest_table=N' + quotename(@dest_table, N'''') +
N',@dest_owner=N' + quotename(@dest_owner, N'''') +
N',@type=''ADD''' +
N' end'
end

if @command is not null
begin
if @script_txt is NULL
begin
insert into @art_commands values (@artid, @command)
end
else
begin
select @script_txt = @script_txt + @command + N' '
end
end

fetch publication_art_cursor into @artid, @current_article, @dest_table, @dest_owner
end

close publication_art_cursor
deallocate publication_art_cursor

-- if this is the subscriptions first article then
-- we will perform the extensive article checks that
-- validates the entire publication and it's articles.
if @article = N'all'
begin
-- now insert a command to verify that after the commands above are applied there
-- are no invalid publication/subscription combinations in the subscriber db
select @command = N'if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSdetectinvalidpeerconfiguration @publisher=N' + quotename(@publisher, N'''') +
N',@publisher_db=N' + quotename(@publisher_db, N'''') +
N',@publication=N' + quotename(@publication, N'''') +
N' end'

if @command is not null
begin
if @script_txt is NULL
begin
insert into @art_commands values (@artid, @command)
end
else
begin
select @script_txt = @script_txt + @command + N' '
end
end
end

--
* This section creates commands to be posted to the distribution database
* that will insert all necessary init records into the LSN table.
--

-- retrieve the min artid so that we can use it when posting PeerToPeer
-- commands that do not originate from this server/publisherdb/publication
select @min_artid = min(artid) from sysextendedarticlesview where pubid = @pubid and (@article = N'all' or name = @article)

-- collect peer to peer lsns for a specific publication meta-data on this
-- machine. note that here we use @publication instead of @pubid because the
-- @pubid could possibly have a different value at each originator database.
declare peer_art_cursor cursor local fast_forward for
select mspl.originator,
mspl.originator_db,
mspl.originator_publication,
mspl.originator_publication_id,
mspl.originator_db_version,
mspl.originator_lsn,
mspl.originator_version,
mspl.originator_id
from MSpeer_lsns mspl
where mspl.originator_publication = @publication
for read only

open peer_art_cursor

fetch peer_art_cursor into @originator, @originator_db, @originator_publication,
@originator_publication_id, @originator_db_version, @originator_lsn, @originator_version, @originator_id

while (@@fetch_status <> -1)
begin
select @originator_lsn_str = sys.fn_varbintohexstr (@originator_lsn)

if @originator_id is NULL
select @originator_id = 0

select @command = N'if (@@microsoftversion >= 0x0A000000)' +
N' begin' +
N' exec sys.sp_MSaddpeerlsn @originator=N' + quotename(@originator, N'''') +
N',@originator_db=N' + quotename(@originator_db, N'''') +
N',@originator_publication=N' + quotename(@originator_publication, N'''') +
N',@originator_publication_id=' + cast(@originator_publication_id as nvarchar) +
N',@originator_db_version=' + cast(@originator_db_version as nvarchar) +
N',@originator_lsn=0x0' +
N',@originator_version=' + cast(@originator_version as nvarchar) +
N',@originator_id=' + cast(@originator_id as nvarchar) +
N' end' +
N' else if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSaddpeerlsn @originator=N' + quotename(@originator, N'''') +
N',@originator_db=N' + quotename(@originator_db, N'''') +
N',@originator_publication=N' + quotename(@originator_publication, N'''') +
N',@originator_publication_id=' + cast(@originator_publication_id as nvarchar) +
N',@originator_db_version=' + cast(@originator_db_version as nvarchar) +
N',@originator_lsn=0x0' +
N' end'

select @command = N' if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSpeertopeerfwdingexec @command=N''' + replace(@command, N'''', N'''''') + N'''' +
N',@publication=N' + quotename(@originator_publication, N'''') +
N' end'

if @command is not null
begin
if @script_txt is NULL
begin
insert into @art_commands values (@min_artid, @command)
end
else
begin
select @script_txt = @script_txt + @command + N' '
end
end

-- when adding the lsn for the publisher we want to update it
-- to the current snapshot lsn. this is because the snapshot may
-- contain bcp files with data that we will not want to reapply.
-- keep in mind that this should only be done for regular snapshot
-- and not the auto-nosync case (there are no bcp files for autonosync)
-- also do not forward this command since it is only meant to adjust
-- the lsn for the case where a bcp file was applied via snapshot.
if @script_txt is NULL
and UPPER(@originator) = UPPER(@publisher)
and @originator_db = @publisher_db
and @originator_publication_id = @pubid
and @originator_db_version = @publisher_db_version
begin
select @originator_lsn_str = sys.fn_varbintohexstr (@snapshot_lsn)

select @command = N'if (@@microsoftversion >= 0x09000000)' +
N' begin' +
N' exec sys.sp_MSupdatepeerlsn @originator=N' + quotename(@originator, N'''') +
N',@originator_db=N' + quotename(@originator_db, N'''') +
N',@originator_publication_id=' + cast(@originator_publication_id as nvarchar) +
N',@originator_db_version=' + cast(@originator_db_version as nvarchar) +
N',@originator_lsn=' + @originator_lsn_str +
N' end'

insert into @art_commands values (@min_artid, @command)
end

fetch peer_art_cursor into @originator, @originator_db, @originator_publication,
@originator_publication_id, @originator_db_version, @originator_lsn, @originator_version, @originator_id
end

close peer_art_cursor
deallocate peer_art_cursor

-- return all commands to the client
if @script_txt is NULL
begin
select *
from @art_commands
order by id
end

return 0
end

No comments:

Post a Comment

Total Pageviews