May 7, 2012

sp_MSadd_subscription (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_subscription(int @dts_package_location
, nvarchar @distribution_job_name
, nvarchar @internal
, int @publisher_engine_edition
, tinyint @nosync_type
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @subscriber
, int @article_id
, nvarchar @subscriber_db
, tinyint @status
, varbinary @subscription_seqno
, nvarchar @publication
, nvarchar @article
, tinyint @subscription_type
, tinyint @sync_type
, bit @snapshot_seqno_flag
, int @frequency_type
, int @frequency_interval
, int @frequency_relative_interval
, int @frequency_recurrence_factor
, int @frequency_subday
, int @frequency_subday_interval
, int @active_start_time_of_day
, int @active_end_time_of_day
, int @active_start_date
, int @active_end_date
, nvarchar @optional_command_line
, tinyint @update_mode
, bit @loopback_detection
, bit @offloadagent
, nvarchar @offloadserver
, nvarchar @dts_package_name
, nvarchar @dts_package_password)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_subscription  
(
@publisher sysname,
@publisher_db sysname,
@subscriber sysname,
@article_id int = NULL,
@subscriber_db sysname = NULL,
@status tinyint, -- 0 = inactive, 1 = subscribed, 2 = active
@subscription_seqno varbinary(16), -- publisher's database sequence number

-- Post 6.5 parameters
@publication sysname = NULL, -- 6.x publishers will not provide this
@article sysname = NULL,
@subscription_type tinyint = 0, -- 0 = push, 1 = pull, 2 = anonymous
@sync_type tinyint = 0, -- 0 = none 1 = automatic snaphot 2 = no intial snapshot
@snapshot_seqno_flag bit = 0, -- 1 = subscription seqno is the snapshot seqno

@frequency_type int = NULL,
@frequency_interval int = NULL,
@frequency_relative_interval int = NULL,
@frequency_recurrence_factor int = NULL,
@frequency_subday int = NULL,
@frequency_subday_interval int = NULL,
@active_start_time_of_day int = NULL,
@active_end_time_of_day int = NULL,
@active_start_date int = NULL,
@active_end_date int = NULL,
@optional_command_line nvarchar(4000) = '',

-- synctran
@update_mode tinyint = 0, -- 0=read only,1=sync tran,2=queued tran,3=failover,
-- 4=sqlqueued tran,5=sqlqueued failover,6=sqlqueued qfailover,7=qfailover
@loopback_detection bit = 0,
@distribution_jobid binary(16) = NULL OUTPUT,

-- agent offload
@offloadagent bit = 0,
@offloadserver sysname = NULL,

-- If agent is already created, the package name will be ignored.
@dts_package_name sysname = NULL,
@dts_package_password nvarchar(524) = NULL,
@dts_package_location int = 0,
@distribution_job_name sysname = NULL,
@internal sysname = N'PRE-YUKON' , -- Can be: 'PRE-YUKON', 'YUKON ADD SUB', 'YUKON ADD AGENT'
@publisher_engine_edition int = NULL,
@nosync_type tinyint = 0 -- 0(none), 1(replication support only), 2(initialize with backup), 3(initialize from lsn)
)
as
begin
set nocount on
declare @publisher_id smallint
,@subscriber_id smallint
,@command nvarchar (4000)
,@type tinyint
,@database sysname
,@long_name nvarchar (255)
,@retcode int
,@login sysname
,@password nvarchar(524)
,@retryattempts int
,@retrydelay int
,@virtual smallint -- const: virtual subscriber id
,@virtual_anonymous smallint -- const: virtual anonymous subscriber id
,@publication_str nvarchar (32)
,@agent_id int
,@publication_id int
,@publication_type int
,@independent_agent bit
,@allow_pull bit
,@active tinyint

,@flushfrequency int
,@frequencytype int
,@frequencyinterval int
,@frequencyrelativeinterval int
,@frequencyrecurrencefactor int
,@frequencysubday int
,@frequencysubdayinterval int
,@activestarttimeofday int
,@activeendtimeofday int
,@activestartdate int
,@activeenddate int
,@dsn_subscriber tinyint
,@jet_subscriber tinyint
,@oledb_subscriber tinyint
,@thirdparty_flag bit
,@subscribersecuritymode smallint
,@subscriberlogin sysname
,@subscriberpassword nvarchar(524)

,@distributor_security_mode int -- 0 standard, 1 integrated
,@distributor_login sysname
,@distributor_password nvarchar(524)
,@publisher_database_id int
,@platform_nt binary
,@anonymous_agent_id int
,@agent_name nvarchar(100)
,@publication_name sysname
,@subscriber_provider sysname

--
-- 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
--
if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
begin
raiserror(21482, 16, -1, 'sp_MSadd_subscription', 'distribution')
return (1)
end

IF @offloadagent IS NOT NULL
AND @offloadagent != 0
BEGIN
-- "Parameter '@offloadagent' is no longer supported."
RAISERROR(21698, 16, -1, '@offloadagent')
RETURN 1
END

IF ISNULL(@offloadserver, N'') != N''
BEGIN
-- "Parameter '@offloadserver' is no longer supported."
RAISERROR(21698, 16, -1, '@offloadserver')
RETURN 1
END

-- Store off publication name for dummy monitor row
select @publication_name = @publication
-- Defined in sqlrepl.h
-- Set null @optional_command_line to empty string to avoid string concat problem
,@optional_command_line = ISNULL(LTRIM(RTRIM(@optional_command_line)), N'')
,@dsn_subscriber = 1 -- Const: subscriber type 'dsn'
,@jet_subscriber = 2
,@oledb_subscriber = 3
,@virtual = -1
,@virtual_anonymous = -2
,@active = 2
,@platform_nt = 0x1

-- 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 or @@error <> 0
begin
return(1)
end

-- Check if subscriber exists
if @subscriber is null
begin
select @subscriber_id = @virtual
-- The following 2 variables are hardcoded in sp_MSget_repl_cmds_anonymous
,@subscriber_db = N'virtual'
,@subscription_type = 0
end
else
select @subscriber_id = srvid,
@subscriber_provider = providername,
@type = type
from master.dbo.sysservers, MSsubscriber_info where
UPPER(srvname) = UPPER(@subscriber) and
UPPER(subscriber) = UPPER(@subscriber) and
UPPER(publisher) = UPPER(@publisher)
if @subscriber_id is NULL
begin
raiserror (20032, 16, -1, @subscriber, @publisher)
return (1)
end

-- Special logic for 6.5 publisher.
-- 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 @publication is null
begin
if not exists (select * from MSpublisher_databases where publisher_id = @publisher_id and
publisher_db = @publisher_db)
begin
insert into MSpublisher_databases (publisher_id, publisher_db, publisher_engine_edition)
values (@publisher_id, @publisher_db, @publisher_engine_edition)
if @@error <> 0
goto UNDO
end
end

-- Get publisher_database_id
select @publisher_database_id = id from MSpublisher_databases where publisher_id = @publisher_id and
publisher_db = @publisher_db
if @@error <> 0
return 1

-- If publication exists this is a post 6.x publisher
if @publication is not NULL
begin
select @publication_id = publication_id, @publication_type = publication_type,
@independent_agent = independent_agent, @allow_pull = allow_pull,
@thirdparty_flag = thirdparty_flag from
dbo.MSpublications where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication = @publication
if @publication_id is NULL
begin
raiserror (20026, 11, -1, @publication)
return (1)
end

-- Check if article_id exists
if @article_id is not NULL
begin
if not exists (select * from MSarticles where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
article_id = @article_id)
begin
raiserror (20027, 11, -1, @article)
return (1)
end
end

-- Check if article exists
if @article is not NULL and @article_id is NULL
begin
select @article_id = article_id from MSarticles where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
article = @article
if @article_id is NULL
begin
raiserror (20027, 11, -1, @article)
return (1)
end
end
end
else
begin -- Set 6.x publishing values
select @publication_id = 0
,@independent_agent = 0
,@allow_pull = 0
,@thirdparty_flag = 0
,@publication_type = NULL
end

-- If the subscriber is an Oracle subscriber and loopback detection has been enabled
-- verify that the subscriber is also a publisher
if (UPPER(@subscriber_provider) = UPPER('OraOLEDB.Oracle') OR
UPPER(@subscriber_provider) = UPPER('MSDAORA')) AND
@type = @oledb_subscriber AND
@loopback_detection = 1
begin
-- All of the following settings are required for Oracle bi-directional publishing
if NOT @sync_type = 2
begin
raiserror (21744, 16, -1, 'sync_type', 'none')
return 1
end

if NOT @subscription_type = 0
begin
raiserror (21744, 16, -1, 'subscription_type', 'push')
return 1
end

if NOT @status = 2
begin
raiserror (21744, 16, -1, 'status', 'active')
return 1
end

if NOT @independent_agent = 1
begin
raiserror (21744, 16, -1, 'independent_agent', 'true')
return 1
end

-- exec @retcode = sys.sp_ORACheckLoopbackSupport @subscriber
-- if @retcode <> 0 or @@error <> 0
-- return 1
end

-- Make sure subscription does not already exist
if exists (select * from dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
publication_id = @publication_id and
article_id = @article_id and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db)
begin
if @thirdparty_flag = 1
begin
raiserror (14058, 16, -1)
return(1)
end
else
begin
exec @retcode = sys.sp_MSdrop_subscription
@publisher = @publisher,
@publisher_db = @publisher_db,
@subscriber = @subscriber,
@article_id = @article_id,
@subscriber_db = @subscriber_db,
@publication = @publication,
@article = @article
if @retcode <> 0 or @@error <> 0
begin
return(1)
end
end
end
-- Check to see if we need to add a new distribution agent for the subscription.
-- It is database wide for non independent agent publications, and publication wide otherwise.
-- Check to see if the distribution agent for this subscription is already added.

select @agent_id = NULL
select @agent_id = agent_id from
dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscription_type = @subscription_type and
(publication_id = @publication_id or @independent_agent = 0) and
independent_agent = @independent_agent and
subscriber_id = @subscriber_id and
subscriber_db = @subscriber_db

if @subscriber_id = @virtual
begin
select @anonymous_agent_id = agent_id from
dbo.MSsubscriptions where
publisher_id = @publisher_id and
publisher_db = @publisher_db and
subscription_type = @subscription_type and
(publication_id = @publication_id or @independent_agent = 0) and
independent_agent = @independent_agent and
subscriber_id = @virtual_anonymous and
subscriber_db = @subscriber_db
end

begin tran
save transaction MSadd_subscription

--
-- SKU based subscription count check
-- This should be done just before creating distribution agent
-- and adding entry in MSsubscriptions
--
-- Skip this check for snapshot publications
--
if (@publication_type != 1)
begin
--
-- Since subscription addition is happening at an article level
-- do the subscription count check once per publication
--
if not exists (select * from dbo.MSsubscriptions
where publisher_id = @publisher_id
and publisher_db = @publisher_db
and publication_id = @publication_id
and subscriber_id = @subscriber_id
and subscriber_db = @subscriber_db
and subscription_type = @subscription_type)
begin
--
-- we are adding the first article subscription to this publication
--
exec @retcode = sys.sp_MScheck_subscription_count_internal @mode=0
,@publisher = @publisher
,@publisher_engine_edition = @publisher_engine_edition
,@about_to_insert_new_subscription = 1
if (@retcode != 0 or @@error != 0)
goto UNDO
end
end
--
-- process the distribution agent
--
if @agent_id is NOT NULL
begin
select @distribution_jobid = job_id from MSdistribution_agents
where id = @agent_id
end
else
begin
-- Create distribution agent
-- Do not create local job if
-- 1. virtual subscription
-- 2. no subscriber information, return (6.x legacy)
-- 3. pull (this sp will not be called for anonymous subscription)

declare @local_job bit

if @subscriber_id = @virtual or
not exists (select * from MSsubscriber_info where
UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber)) OR
@subscription_type = 1

select @local_job = 0
else
select @local_job = 1

-- 'ALL' is reserved for indication all publications
-- Hardcoded in sp_MSenum*...
-- Note! @publication is overwritten

if @independent_agent = 0
select @publication = 'ALL'

if @local_job = 1
begin
select
@frequencytype = frequency_type,
@frequencyinterval = frequency_interval,
@frequencyrelativeinterval = frequency_relative_interval,
@frequencyrecurrencefactor = frequency_recurrence_factor,
@frequencysubday = frequency_subday,
@frequencysubdayinterval = frequency_subday_interval,
@activestarttimeofday = active_start_time_of_day,
@activeendtimeofday = active_end_time_of_day,
@activestartdate = active_start_date,
@activeenddate = active_end_date
from MSsubscriber_schedule
where UPPER(publisher) = UPPER(@publisher) and UPPER(subscriber) = UPPER(@subscriber) and agent_type = 0

if @frequency_type is null
select @frequency_type = @frequencytype

if @frequency_interval is null
select @frequency_interval = @frequencyinterval

if @frequency_relative_interval is null
select @frequency_relative_interval = @frequencyrelativeinterval

if @frequency_recurrence_factor is null
select @frequency_recurrence_factor = @frequencyrecurrencefactor

if @frequency_subday is null
select @frequency_subday = @frequencysubday

if @frequency_subday_interval is null
select @frequency_subday_interval = @frequencysubdayinterval

if @active_start_time_of_day is null
select @active_start_time_of_day = @activestarttimeofday

if @active_end_time_of_day is null
select @active_end_time_of_day = @activeendtimeofday

if @active_start_date is null
select @active_start_date = @activestartdate

if @active_end_date is null
select @active_end_date = @activeenddate

execute @retcode = sys.sp_MSadd_distribution_agent
@publisher_id = @publisher_id,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber_id = @subscriber_id,
@subscriber_db = @subscriber_db,
@subscription_type = @subscription_type,
@local_job = @local_job,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@command = @optional_command_line,
@agent_id = @agent_id OUTPUT,
@distribution_jobid = @distribution_jobid OUTPUT,
@update_mode = @update_mode,
@dts_package_name = @dts_package_name,
@dts_package_password = @dts_package_password,
@dts_package_location = @dts_package_location,
@name = @distribution_job_name,
@internal = @internal
if @@error <> 0 or @retcode <> 0
goto UNDO
end
else
begin
execute @retcode = sys.sp_MSadd_distribution_agent
@publisher_id = @publisher_id,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber_id = @subscriber_id,
@subscriber_db = @subscriber_db,
@subscription_type = @subscription_type,
@local_job = @local_job,
@agent_id = @agent_id OUTPUT,
@distribution_jobid = @distribution_jobid OUTPUT,
@update_mode = @update_mode
-- Only push has distributor side package.

if @@error <> 0 or @retcode <> 0
goto UNDO
end

if @subscriber_id = @virtual
begin
execute @retcode = sys.sp_MSadd_distribution_agent
@publisher_id = @publisher_id,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber_id = @virtual_anonymous,
@subscriber_db = @subscriber_db,
@subscription_type = @subscription_type,
@local_job = @local_job,
@agent_id = @anonymous_agent_id OUTPUT,
@distribution_jobid = @distribution_jobid OUTPUT,
@update_mode = @update_mode
-- No need to specify offload parameters for virtual agents
-- No need to specify package name for virtual agents
end
end

insert into dbo.MSsubscriptions values (@publisher_database_id, @publisher_id, @publisher_db, @publication_id,
@article_id, @subscriber_id, @subscriber_db, @subscription_type, @sync_type, @status,
@subscription_seqno, @snapshot_seqno_flag, @independent_agent, getdate(),
@loopback_detection, @agent_id, @update_mode, @subscription_seqno, @subscription_seqno, @nosync_type)
if @@error <> 0
goto UNDO

-- For shiloh, always add virtual anonymous entry for attach logic
-- If anonymous publication, add "virtual anonymous" subscription
-- when adding the virtual subscription
if @subscriber_id = @virtual
begin
insert into dbo.MSsubscriptions values (@publisher_database_id, @publisher_id, @publisher_db, @publication_id,
@article_id, @virtual_anonymous, @subscriber_db, @subscription_type, @sync_type, @status,
@subscription_seqno, @snapshot_seqno_flag, @independent_agent, getdate(),
@loopback_detection, @anonymous_agent_id, @update_mode, @subscription_seqno, @subscription_seqno, @nosync_type)
if @@error <> 0
goto UNDO
end

-- Check to see if we need to add a new qreader agent
if (@update_mode in (2,3,4,5,6,7))
begin
--
-- we can have only one agent for the distribution database
--
if not exists (select * from dbo.MSqreader_agents)
begin
-- if at this point the @internal IS 'PRE-YUKON' we know that
-- the caller (publisher side) is yukonplus. In this case we
-- must let the user know how to create the qreader with a strict
-- security policy else we allow the creation without login/pwd
if @internal != 'PRE-YUKON'
begin
-- The 'qreader' agent job must be added via 'sp_addqreader_agent' before continuing. Please see the documentation for 'sp_addqreader_agent'.
RAISERROR(21798, 16, -1, 'qreader', 'sp_addqreader_agent', 'sp_addqreader_agent')
goto UNDO
end

execute @retcode = sys.sp_MSadd_qreader_agent
if (@retcode != 0 or @@error != 0)
goto UNDO
end
end

commit transaction

return(0)

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

No comments:

Post a Comment

Total Pageviews