May 2, 2012

sp_MSadd_publication (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_publication(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, int @publication_id
, int @publication_type
, bit @independent_agent
, bit @immediate_sync
, bit @allow_push
, bit @allow_pull
, bit @allow_anonymous
, nvarchar @snapshot_agent
, nvarchar @logreader_agent
, nvarchar @description
, int @retention
, nvarchar @vendor_name
, int @sync_method
, bit @allow_subscription_copy
, int @thirdparty_options
, bit @allow_queued_tran
, int @queue_type
, nvarchar @publisher_type
, int @options
, tinyint @retention_period_unit
, int @publisher_engine_edition
, bit @allow_initialize_from_backup)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_publication   
(
@publisher sysname,
@publisher_db sysname,
@publication sysname,
@publication_id int = 0,
@publication_type int = 1, -- 0 = Transactional 1 = Snapshot 2 = Merge
@independent_agent bit = 0,
@immediate_sync bit = 0,
@allow_push bit = 1,
@allow_pull bit = 0,
@allow_anonymous bit = 0,
@snapshot_agent nvarchar(100) = NULL, -- IGNORED : NO LONGER VALID
@logreader_agent nvarchar (100) = NULL, -- IGNORED : NO LONGER VALID
@description nvarchar(255) = NULL,
@retention int =60,
@vendor_name nvarchar(100) = 'Microsoft SQL Server',
@sync_method int = 0,
@allow_subscription_copy bit = 0,
@thirdparty_options int = NULL,
@allow_queued_tran bit = 0,
@queue_type int = NULL,
@publisher_type sysname = N'MSSQLSERVER',
@options int = 0,
@retention_period_unit tinyint = 0,
@publisher_engine_edition int = null,
@allow_initialize_from_backup bit = 0
)
as
BEGIN
set nocount on

DECLARE @thirdparty_flag bit -- 0 = SQL Server 1 = Third Party
,@publisher_id smallint
,@retcode int
,@platform int
,@platform_nt int
,@platform_desktop int
,@agentname nvarchar(100)
,@dbname sysname

select @platform = platform()
,@platform_nt = 0x1
,@platform_desktop = 0x100
,@dbname = db_name()

--
-- security check
-- only db_owner can execute this
--
if (is_member ('db_owner') != 1)
begin
raiserror(14260, 16, -1)
return (1)
end
--
-- security check
-- Has to be executed from distribution database (sp_MSvalidate_distpublisher
-- does this check implicitly)
--
--
if (sys.fn_MSrepl_isdistdb (@dbname) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSadd_publication', 'distribution')
return (1)
end
--
-- Check if publisher is a defined as a distribution publisher in the current database
exec @retcode = sys.sp_MSvalidate_distpublisher @publisher, @publisher_id OUTPUT
if @retcode <> 0
return(1)

-- Get third party flag
select @thirdparty_flag = thirdparty_flag from msdb.dbo.MSdistpublishers
where name = UPPER(@publisher)

--
-- Parameter Check: @publication. (For 3rd party publications.)
-- The @publication name must conform to the rules for identifiers,
-- and must not be the keyword 'all'.
--

exec @retcode = sys.sp_MSreplcheck_name @publication, '@publication', 'sp_MSadd_publication'
if @@ERROR <> 0 or @retcode <> 0
return(1)

IF LOWER (@publication) = N'all'
BEGIN
RAISERROR (14034, 16, -1)
RETURN (1)
END

-- Parameter Check: @publication_type
-- Make sure that the publication type is one of the following:
-- 0 transactional
-- 1 snapshot
-- 2 merge
if @publication_type not in (0,1,2)
begin
raiserror(20033, 16, -1)
return (1)
end

-- disable tran/queued publishing on Win9x
if (@publication_type = 0 or @allow_queued_tran = 1) and (@platform & @platform_nt != @platform_nt)
begin
raiserror(21764, 16, 1)
return (1)
end

if (@publication_type = 0) and (@platform & @platform_desktop = @platform_desktop)
begin
raiserror(21108, 16, -1)
return (1)
end

-- Parameter Check: @immediate_sync
-- The publication must support independent_agent to support immediate_sync
if @immediate_sync = 1 and @independent_agent != 1
begin
raiserror(21022, 16, -1)
return (1)
end

-- Parameter Check: @allow_anonymous
-- The publication must support immediate_sync to support anonymous.
if @allow_anonymous = 1 and @immediate_sync != 1
begin
raiserror(20011, 16, -1)
return (1)
end

-- Make sure publication does not already exist
if exists (select * from dbo.MSpublications where publication = @publication and
publisher_id = @publisher_id and publisher_db = @publisher_db)
begin
if @thirdparty_flag = 1
begin
raiserror(14016, 16, -1, @publication)
return (1)
end
else
begin
--
-- clean up orphans if necessary
--
exec @retcode = sys.sp_MSdrop_publication
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@cleanup_orphans = 1
if @@error <> 0 or @retcode <> 0
return (1)
end
end

--
-- For MSMQ queue type - Distributor needs to support MSMQ 2.0
-- in case we support a pre-Yukon publisher.
-- Post Yukon - this block can be removed
--
if (@queue_type = 1 and @allow_queued_tran = 1)
begin
--
-- Now we use xp_MSver to detect NT OS version
-- MSMQ subscription only allowed for platforms that support MSMQ 2.0
-- version 5.0.2195 or higher
--
create table #tosversion ( propid int, propname sysname collate database_default, value int, charvalue nvarchar(255) collate database_default)
insert into #tosversion (propid, propname, value, charvalue)
exec master.dbo.xp_msver N'WindowsVersion'

declare @vervalue int
,@lobyte tinyint
,@hibyte tinyint
,@loword smallint
,@hiword smallint

--
-- low order byte of low order word = OSmajor, high order byte of low order word = OSminor
-- high order word = OSbuild
--
select @vervalue = value from #tosversion where propname = N'WindowsVersion'
select @loword = (@vervalue & 0xffff)
,@hiword = (@vervalue / 0x10000) & 0xffff
select @lobyte = @loword & 0xff
,@hibyte = (@loword / 100) & 0xff
drop table #tosversion

--
-- check for OS major version
--
if (@lobyte < 5)
begin
raiserror(21334, 16, 4, '2.0')
return (1)
end

--
-- check for OS build version
--
if (@lobyte = 5 and @hiword < 2195)
begin
raiserror(21334, 16, 5, '2.0')
return (1)
end
end

begin tran
save tran MSadd_publication

insert into dbo.MSpublications (publisher_id, publisher_db, publication, publication_type, thirdparty_flag,
independent_agent, immediate_sync, allow_push, allow_pull, allow_anonymous, description,
vendor_name, retention, sync_method, allow_subscription_copy, thirdparty_options,
allow_queued_tran, options, retention_period_unit, allow_initialize_from_backup)
values (@publisher_id, @publisher_db, @publication, @publication_type, @thirdparty_flag,
@independent_agent, @immediate_sync, @allow_push, @allow_pull, @allow_anonymous, @description,
@vendor_name, @retention, @sync_method, @allow_subscription_copy, @thirdparty_options,
@allow_queued_tran, @options, @retention_period_unit, @allow_initialize_from_backup)
if @@error <> 0
goto UNDO
select @publication_id = @@identity

-- Enable the distribution cleanup agent if transactional or snapshot publicational
if @publication_type = 0 or @publication_type = 1
begin
select @agentname = name
from msdb.dbo.sysjobs as j
join msdb.dbo.sysjobsteps as s
on j.job_id = s.job_id
where j.category_id = 11
and s.database_name = @dbname

if @agentname is not NULL
begin
exec @retcode = msdb.dbo.sp_update_job @job_name=@agentname, @enabled=1
if @@error <> 0 or @retcode <> 0
goto UNDO
end
end

-- Add snapshot and logreader agent

-- Always add a non local snapshot agent. This is to cover the case
-- when there's no SQLServerAgent job for the snapshot agent (For example, in Access).
-- The agent entry is needed for initance check.
-- sp_addpublication_snapshot will drop the entry and recreat it.
exec @retcode = sys.sp_MSadd_snapshot_agent
@name = NULL,
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@publication_type = @publication_type,
@publisher_type = @publisher_type,
@local_job = 0,
@internal = N'YUKON'
if @@error <> 0 or @retcode <> 0
goto UNDO

-- If publisher_id, publisher_db pair is not in MSpublisher_databases then add it. This will be used
-- to store a publisher_database_id in the MSrepl_transactions and MSrepl_commands table.
if not exists (select * from dbo.MSpublisher_databases where publisher_id = @publisher_id and
publisher_db = @publisher_db)
begin
insert into dbo.MSpublisher_databases (publisher_id, publisher_db, publisher_engine_edition)
values (@publisher_id, @publisher_db, @publisher_engine_edition)
if @@error <> 0
goto UNDO
insert into dbo.MSrepl_backup_lsns (publisher_database_id) values (@@identity)
if @@error <> 0
goto UNDO
end
--
-- Add entries in publication threshold for this publication
-- @publication_type int -- 0 = Transactional 1 = Snapshot 2 = Merge
--
if (@publication_type in (0,1))
begin
-- Snapshot or transactional publication
-- warnings are enabled by default
insert into dbo.MSpublicationthresholds (publication_id,metric_id,value, isenabled)
select @publication_id, metric_id, default_value, 1
from msdb.dbo.MSreplmonthresholdmetrics
where metric_id = 1
if (@publication_type = 0)
begin
insert into dbo.MSpublicationthresholds (publication_id,metric_id,value, isenabled)
select @publication_id, metric_id, default_value, 1
from msdb.dbo.MSreplmonthresholdmetrics
where metric_id = 2
end
end
else
begin
-- Merge publication
insert into dbo.MSpublicationthresholds (publication_id,metric_id,value)
select @publication_id, metric_id, default_value
from msdb.dbo.MSreplmonthresholdmetrics
where metric_id in (4,5,6,7,8)
end
-- Check for error in insert
if @@error <> 0
goto UNDO
--
-- Commit the transaction
--
commit tran
--
-- All done
--
return(0)

UNDO:
if @@TRANCOUNT > 0
begin
ROLLBACK TRAN MSadd_publication
COMMIT TRAN
end
return(1)
END

No comments:

Post a Comment

Total Pageviews