create procedure sys.sp_MSrepl_addsubscription
(
@publication sysname,
@article sysname = 'all',
@subscriber sysname = NULL,
@destination_db sysname = NULL,
@sync_type nvarchar (255) = 'automatic',
@status sysname = NULL,
@subscription_type nvarchar(4) = 'push',
-- SyncTran
@update_mode nvarchar(30) = 'read only', -- Can be 'read only', 'sync tran', 'queued tran', 'failover', 'queued failover'
@loopback_detection nvarchar(5) = NULL, -- 'true' or 'false'
-- end SyncTran
@frequency_type int = NULL, -- defaults to 64
@frequency_interval int = NULL, -- defaults to 1
@frequency_relative_interval int = NULL, -- defaults to 1
@frequency_recurrence_factor int = NULL, -- defaults to 0
@frequency_subday int = NULL, -- defaults to 4
@frequency_subday_interval int = NULL, -- defaults to 5
@active_start_time_of_day int = NULL, -- defaults to 0
@active_end_time_of_day int = NULL, -- defaults to 235959
@active_start_date int = NULL, -- defaults to 0
@active_end_date int = NULL, -- defaults to 99991231
@optional_command_line nvarchar(4000) = NULL,
@reserved nvarchar(10) = NULL, -- reserved, used when calling from other system
-- stored procedures, it will be set to 'internal'.
-- It should never be used directly
@enabled_for_syncmgr nvarchar(5) = NULL, -- Enabled for SYNCMGR: true or false
-- Agent offload
@offloadagent bit = 0,
@offloadserver sysname = NULL,
-- End of agent offload
-- DTS package name
@dts_package_name sysname = NULL, -- value will be sent and validated at distributor
@dts_package_password sysname = NULL,
@dts_package_location nvarchar(12) = NULL,
@distribution_job_name sysname = NULL,
@publisher sysname = NULL,
-- Backup device specification for @sync_method = 'autonosync with backup'
@backupdevicetype nvarchar(20) = 'logical',
@backupdevicename nvarchar(1000) = null,
@mediapassword sysname = null,
@password sysname = null,
@fileidhint int = null,
@unload bit = 1,
-- No-sync subscription LSN for @sync_method = 'autonosync with lsn'
@subscriptionlsn binary(10) = null
,@publisher_type sysname
-- expose -SubscriptionStreams functionality
,@subscriptionstreams tinyint = null -- value provided should fall between 1 and 64
,@subscriber_type tinyint = 0 -- defaults of SQL Server
)
AS
BEGIN
SET NOCOUNT ON
/*
** Declarations.
*/
DECLARE @retcode int
DECLARE @pubid int
DECLARE @artid int
DECLARE @pre_creation_cmd tinyint
DECLARE @none tinyint
DECLARE @automatic tinyint
DECLARE @inactive tinyint
DECLARE @active tinyint
DECLARE @subscribed tinyint
DECLARE @manual tinyint
DECLARE @srvid smallint
DECLARE @subscriber_bit smallint
DECLARE @sync_typeid tinyint
DECLARE @non_sql_flag bit
DECLARE @truncate tinyint
DECLARE @sync_method tinyint
DECLARE @char_bcp tinyint
DECLARE @concurrent tinyint
DECLARE @concurrent_char tinyint
DECLARE @database_snapshot tinyint
DECLARE @database_snapshot_character tinyint
DECLARE @internal nvarchar(10)
DECLARE @nosync nvarchar(10)
DECLARE @status_id tinyint
DECLARE @virtual_id smallint
DECLARE @subscription_type_id int /* 0 push, 1 pull */
DECLARE @immediate_sync bit /* publication type */
DECLARE @count_subs int
DECLARE @count_arts int
DECLARE @distribution_jobid binary(16)
DECLARE @pubstatus tinyint
DECLARE @allow_anonymous bit
DECLARE @immediate_sync_ready bit
declare @loopback_detection_id bit
declare @independent_agent_id bit
DECLARE @platform_nt binary
,@artsrctabid int
,@distributor sysname
,@distribdb sysname
DECLARE @dsn_dbname sysname
DECLARE @dts_package_enc_password nvarchar(524)
-- SyncTran
DECLARE @allow_sync_tran_id bit
DECLARE @allow_queued_tran_id bit
DECLARE @update_mode_id tinyint -- 0 = read only, 1 = sync tran, 2 = queued tran, 3 = failover
-- 4 = sqlqueued tran, 5 = sqlqueued failover, 6 = sqlqueued qfailover, 7 = qfailover
DECLARE @publication_queue_type int
-- end SyncTran
-- Heterogeneous subscriptions
DECLARE @enabled_for_het_sub bit
DECLARE @OPT_ENABLED_FOR_HET_SUB int
SELECT @OPT_ENABLED_FOR_HET_SUB = 0x4
DECLARE @OPT_ENABLED_FOR_P2P int
SELECT @OPT_ENABLED_FOR_P2P = 0x1
-- Heterogeneous publishers
DECLARE @publisher_local sysname
,@publisher_db sysname
DECLARE @distproc nvarchar(1000)
set @publisher_db = DB_NAME()
-- Parameter check: @update_mode
-- HREPL: Reject non-read-only
IF NOT @publisher_type = N'MSSQLSERVER' AND NOT @update_mode = 'read only'
BEGIN
RAISERROR (21634, 16, -1, '@update_mode', @update_mode, '''read only'' or NULL')
RETURN (1)
END
/*
** Parameter Check: @subscription_type
** Valid values:
** push
** pull
**
*/
IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull')
BEGIN
RAISERROR (14128, 16, -1)
RETURN (1)
END
IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'push'
BEGIN
SELECT @subscription_type_id = 0
END
ELSE
BEGIN
SELECT @subscription_type_id = 1
END
/*
** Security Check.
*/
IF @subscription_type_id = 0
BEGIN
exec @retcode = sys.sp_MSreplcheck_publish
if @@ERROR <> 0 or @retcode <> 0
return(1)
END
ELSE
BEGIN
exec @retcode = sys.sp_MSreplcheck_pull @publication = @publication,
@publisher = @publisher
if @@ERROR <> 0 or @retcode <> 0
return(1)
END
-- With the new security model a number of parameters are invalid
IF sys.fn_yukonsecuritymodelrequired(NULL) = 1
BEGIN
IF @frequency_type IS NOT NULL
OR @frequency_interval IS NOT NULL
OR @frequency_relative_interval IS NOT NULL
OR @frequency_recurrence_factor IS NOT NULL
OR @frequency_subday IS NOT NULL
OR @frequency_subday_interval IS NOT NULL
OR @active_start_time_of_day IS NOT NULL
OR @active_end_time_of_day IS NOT NULL
OR @active_start_date IS NOT NULL
OR @active_end_date IS NOT NULL
OR @optional_command_line IS NOT NULL
OR @enabled_for_syncmgr IS NOT NULL
OR @dts_package_name IS NOT NULL
OR @dts_package_password IS NOT NULL
OR @dts_package_location IS NOT NULL
OR @distribution_job_name IS NOT NULL
BEGIN
-- "The %s parameter(s) have been deprecated from this procedure. The value(s) should now be specified when calling '%s'."
RAISERROR(21838, 10, -1, 'scheduling, optional command line, sync manager, dts and distribution job name', 'sp_addpushsubscription_agent'' or ''sp_addpullsubscription_agent')
SELECT @frequency_type = NULL,
@frequency_interval = NULL,
@frequency_relative_interval = NULL,
@frequency_recurrence_factor = NULL,
@frequency_subday = NULL,
@frequency_subday_interval = NULL,
@active_start_time_of_day = NULL,
@active_end_time_of_day = NULL,
@active_start_date = NULL,
@active_end_date = NULL,
@optional_command_line = NULL,
@enabled_for_syncmgr = NULL,
@dts_package_name = NULL,
@dts_package_password = NULL,
@dts_package_location = NULL,
@distribution_job_name = NULL
END
END
ELSE
BEGIN
-- Set the original default values for the 8.0 model
SELECT @enabled_for_syncmgr = ISNULL(@enabled_for_syncmgr , 'false'),
@dts_package_location = ISNULL(@dts_package_location, N'distributor')
-- Parameter check: @enabled_for_syncmgr
-- HREPL: not supported
select @enabled_for_syncmgr = isnull(lower(@enabled_for_syncmgr), 'false')
IF @publisher_type = N'MSSQLSERVER'
BEGIN
IF @enabled_for_syncmgr not in ('true', 'false')
BEGIN
-- Invalid '@enabled_for_syncmgr' value. Valid values are 'true' or 'false'.
RAISERROR (14148, 16, -1, '@enabled_for_syncmgr')
RETURN (1)
END
END
ELSE
BEGIN
IF @enabled_for_syncmgr = 'true'
BEGIN
RAISERROR (21632, 16, -1, '@enabled_for_syncmgr', '''false'' or NULL')
RETURN (1)
END
END
END
-- Check to see if database is activated for publication
IF sys.fn_MSrepl_istranpublished(DB_NAME(),1) <> 1
BEGIN
RAISERROR (14013, 16, -1)
RETURN (1)
END
/*
** Initializations.
*/
SELECT @none = 2 /* Const: synchronization type 'none' */
SELECT @automatic = 1 /* Const: synchronization type 'automatic' */
SELECT @manual = 0 /* Const: synchronization type 'manual' */
SELECT @inactive = 0 /* Const: subscription status 'inactive' */
SELECT @subscribed = 1 /* Const: subscription status 'subscribed' */
SELECT @active = 2 /* Const: subscription status 'arctive' */
SELECT @subscriber_bit = 4 /* Const: subscription server status */
SELECT @truncate = 3 /* Const: truncate pre-creation command */
SELECT @char_bcp = 1 /* Const: character bcp sync method */
SELECT @concurrent = 3 /* Const: concurrent sync method */
SELECT @concurrent_char = 4 /* Const: concurrent char mode sync method */
SELECT @database_snapshot = 5 /* Const: database snapshot native sync method */
SELECT @database_snapshot_character = 6 /* Const: database snapshot character sync method */
SELECT @virtual_id = -1 /* Const: virtual subscriber id */
SELECT @internal = 'internal' /* Const: Flag of calling internally from system */
SELECT @nosync = N'nosync'
/* stored procedures */
-- Change it in 7.5 to avoid confusion, expecially in ole db case
-- SELECT @dsn_dbname = 'DSN'
SELECT @dsn_dbname = formatmessage(20586)
SELECT @platform_nt = 0x1
-- If sync_method is one of the new autonosync types, re-route the call
-- immediately to sp_MSaddautonosyncsubscription. Since sp_addsubscription
-- will be called again inside sp_MSaddautonosyncsubscription with
-- @sync_method = 'none', there is no need to waste any time doing
-- parameter validation here.
select @sync_type = lower(@sync_type collate SQL_Latin1_General_CP1_CS_AS)
-- HREPL only supports automatic, replication support only, or none for sync_type at this time
IF @publisher_type != N'MSSQLSERVER' AND
@sync_type IN ( N'initialize with backup',
N'initialize from lsn')
BEGIN
DECLARE @err nvarchar(4000)
SELECT @err = '@sync_type value of ''' + @sync_type + ''''
RAISERROR (21632, 16, -1, @err, '''automatic'', ''replication support only'', or ''none''')
RETURN (1)
END
-- Heterogeneous subscribers only support automatic, replication support only, or none for sync_type at this time
IF @subscriber_type != 0 AND
@sync_type IN ( N'initialize with backup',
N'initialize from lsn')
BEGIN
RAISERROR (21644, 16, -1, '@sync_type', @sync_type, '''automatic'', ''replication support only'', or ''none''')
RETURN (1)
END
/*
** Parameter Check: @offloadagent.
*/
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
/*
** If @publisher is NULL, set @publisher_local to publishingservername()
** otherwise, set @publisher_local to @publisher
*/
IF @publisher IS NULL
BEGIN
select @publisher_local = publishingservername()
END
ELSE
BEGIN
select @publisher_local = @publisher
END
/*
** Parameter Check: @publication.
** Check to make sure that the publication exists and that it conforms
** to the rules for identifiers.
** set subscription_type for the publication
*/
-- Get publication id
SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)
IF (@pubid IS NULL)
BEGIN
RAISERROR (20026, 11, -1, @publication)
RETURN (1)
END
declare @allow_dts bit,
@options int
SELECT @sync_method = sync_method,
@immediate_sync = immediate_sync,
@pubstatus = status,
@allow_anonymous = allow_anonymous,
@immediate_sync_ready = immediate_sync_ready,
@allow_sync_tran_id = allow_sync_tran,
@allow_queued_tran_id = allow_queued_tran,
@independent_agent_id = independent_agent,
@allow_dts = allow_dts,
@publication_queue_type = queue_type,
@options = options
FROM syspublications
WHERE pubid = @pubid
select @srvid = srvid from master.dbo.sysservers where UPPER(srvname collate database_default)=UPPER(@subscriber) collate database_default
-- Only perform the following when not called internally
-- Add Subscriber
-- AND
-- PeerToPeer specific checks
IF @reserved IS NULL
BEGIN
-- the first call to sp_addsubscription we will
-- will attempt to add the subscriber first ...
IF @@TRANCOUNT != 0
BEGIN
-- The procedure 'sp_addsubscription' cannot be executed within a transaction.
RAISERROR(15002, 16, -1, 'sp_addsubscription')
RETURN 1
END
EXEC @retcode = sys.sp_MSrepl_addsubscriber @subscriber = @subscriber,
@type = @subscriber_type,
@login = NULL,
@password = NULL,
@commit_batch_size = NULL,
@status_batch_size = NULL,
@flush_frequency = NULL,
@frequency_type = NULL,
@frequency_interval = NULL,
@frequency_relative_interval = NULL,
@frequency_recurrence_factor = NULL,
@frequency_subday = NULL,
@frequency_subday_interval = NULL,
@active_start_time_of_day = NULL,
@active_end_time_of_day = NULL,
@active_start_date = NULL,
@active_end_date = NULL,
@description = NULL,
@security_mode = NULL,
@encrypted_password = NULL,
@publisher = @publisher_local,
@publisher_type = @publisher_type,
@internal = N'YUKON'
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1
-- PeerToPeer specific checks
IF (@options & @OPT_ENABLED_FOR_P2P) = @OPT_ENABLED_FOR_P2P
BEGIN
-- only allow full subscriptions
IF ISNULL(LOWER(@article), N'') != N'all'
BEGIN
-- Peer-To-Peer publications only support an @article parameter value of 'all'.
RAISERROR (21679, 16, -1, '@article', '''all''')
RETURN (1)
END
-- verify it's a valid sync_type for P2P
IF @sync_type NOT IN (N'replication support only',
N'initialize with backup',
N'initialize from lsn')
BEGIN
-- Peer-To-Peer publications only support an @sync_type parameter value of 'replication support only', 'initialize with backup' or 'initialize from lsn'.
RAISERROR (21679, 16, -1, '@sync_type', '''replication support only'', ''initialize with backup'' or ''initialize from lsn''')
RETURN (1)
END
END
END
IF @sync_type in (N'replication support only',
N'initialize with backup',
N'initialize from lsn')
BEGIN
exec @retcode = sys.sp_MSaddautonosyncsubscription @publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@sync_type = @sync_type,
@status = @status,
@subscription_type = @subscription_type,
@update_mode = @update_mode,
@loopback_detection = @loopback_detection,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@optional_command_line = @optional_command_line,
@reserved = @reserved,
@enabled_for_syncmgr = @enabled_for_syncmgr,
@dts_package_name = @dts_package_name,
@dts_package_password = @dts_package_password,
@dts_package_location = @dts_package_location,
@distribution_job_name = @distribution_job_name,
-- Backup lsn extraction parameters
@backupdevicetype = @backupdevicetype,
@backupdevicename = @backupdevicename,
@mediapassword = @mediapassword,
@password = @password,
@fileidhint = @fileidhint,
@unload = @unload,
-- Explicitly specified lsn of the subscription
@subscriptionlsn = @subscriptionlsn,
@publisher = @publisher,
@publisher_type = @publisher_type
,@subscriptionstreams = @subscriptionstreams
,@subscriber_type = @subscriber_type
IF @@error <> 0
BEGIN
SELECT @retcode = 1
END
RETURN @retcode
END
IF EXISTS (select name from sys.objects where name='sysmergesubscriptions')
BEGIN
IF EXISTS
(
select name
from sysextendedarticlesview
where pubid=@pubid
and objid in
(
select objid
from sysmergeextendedarticlesview
where pubid in
(
select pubid
from sysmergesubscriptions
where db_name = @destination_db
and UPPER(subscriber_server) collate database_default = UPPER(@subscriber) collate database_default
)
)
)
BEGIN
RAISERROR(21281, 16, -1, @publication, @destination_db)
RETURN (1)
END
END
IF @pubid IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@pubid', 'sp_MSrepl_addsubscription')
RETURN (1)
END
/*
** If publication is of concurrent sync, then all articles must
** be subscribed to
*/
IF @sync_method IN( @concurrent, @concurrent_char) AND
LOWER(@article) != 'all' AND
@reserved NOT IN (@internal, @nosync)
BEGIN
RAISERROR( 14100, 16, -1 )
RETURN (1)
END
/*
** Check to see if the desired subscription type is allowed
*/
/*
** push
** Virtual subscriptions are always push type
*/
IF @subscription_type_id = 0 AND @subscriber IS NOT NULL
BEGIN
IF NOT EXISTS (SELECT * from syspublications where
allow_push = 1 AND
pubid = @pubid)
BEGIN
RAISERROR (20012, 16, -1, @subscription_type, @publication)
RETURN (1)
END
END
/* pull */
IF @subscription_type_id = 1 AND @subscriber IS NOT NULL
BEGIN
IF NOT EXISTS (SELECT * from syspublications where
allow_pull = 1 AND
pubid = @pubid)
BEGIN
RAISERROR (20012, 16, -1, @subscription_type, @publication)
RETURN (1)
END
END
/*
** Parameter Check: @subscriber.
**
** Check if the server exists and that it is a subscription server.
**
** @subscriber is NULL represent virtual subscription, which is not allowed
** in following case:
** 1. Non-immediate-sync publication
** 2. the stored procedure is not in the internal usage mode
** (called by system stored procedures)
** 3. non push mode
**
*/
IF @subscriber IS NULL AND (
@immediate_sync = 0 OR
@subscription_type_id <> 0 OR
@reserved NOT IN (@internal, @nosync))
BEGIN
RAISERROR (14043, 16, -1, '@subscriber', 'sp_MSrepl_addsubscription')
RETURN (1)
END
--initialize this to 0, for virtual servers, it will stay 0, for non-hetero ones if there isn't
--server entry in master..sysservers, this flag should stay 0 as well,
select @non_sql_flag = 0
IF @subscriber IS NULL
BEGIN
/* set virtual subscriber ID */
SELECT @srvid = @virtual_id
END
ELSE
BEGIN
/* validate name and get subscriber ID and server status */
EXECUTE @retcode = sys.sp_validname @subscriber
IF @retcode <> 0
RETURN (1)
select @srvid = null
SELECT @srvid = srvid, @non_sql_flag = nonsqlsub
FROM master.dbo.sysservers
WHERE UPPER(srvname collate database_default) = UPPER(@subscriber) collate database_default
IF @srvid IS NULL
BEGIN
--we used to raise 14010 if subscriber is not found
--removed it now since we try to depend on the subscriber entry in master.dbo.syservers less
--set @non_sql_flag here so syssubscriptions will have the right value
IF @subscriber_type > 0
BEGIN
select @non_sql_flag = 1
END
END
END
/*
** Parameter Check: @destination_db.
*/
IF @destination_db IS not NULL
begin
-- @destination_db cannot be all.
-- @destination_db cannot be master.
-- Make sure that the @destination_db conforms to the rules for identifiers.
if LOWER(@destination_db) = 'all'
BEGIN
RAISERROR (14032, 16, -1, '@destination_db')
RETURN (1)
END
if LOWER(@destination_db) = 'master'
BEGIN
RAISERROR (21481, 16, 1)
RETURN (1)
END
EXECUTE @retcode = sys.sp_validname @destination_db
IF @retcode <> 0
RETURN (1)
end
-- Parameter check: @destination_db
-- HREPL: required for SQL subscribers
IF NOT @publisher_type = N'MSSQLSERVER' AND @destination_db IS NULL AND @non_sql_flag = 0
BEGIN
RAISERROR (21637, 16, -1, '@destination_db')
RETURN (1)
END
/*
** Parameter Check: @article
*/
/* @article can not be null */
IF @article IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@article', 'sp_MSrepl_addsubscription')
RETURN (1)
END
-- If the subscriber type is not SQL Server, the publication must be
-- enabled for heterogeneous subscriptions
IF @subscriber_type <> 0
AND (NOT ((@options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB))
BEGIN
-- Unable to add a heterogeneous subscription to the publication. The publication is not enabled for heterogeneous subscriptions.
RAISERROR (20615, 16, -1)
RETURN (1)
END
-- Parameter check: @update_mode
IF @update_mode IS NULL OR LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) NOT IN
('read only', 'sync tran', 'queued tran', 'failover', 'queued failover')
BEGIN
RAISERROR (20502, 16, -1, '@update_mode')
RETURN (1)
END
-- set @update_mode_id
IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'sync tran')
BEGIN
-- Immediate updating subscription
-- 1 = synctran
SELECT @update_mode_id = 1
-- Check if publication allows this option
IF @allow_sync_tran_id <> 1
BEGIN
RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','sync tran')
RETURN (1)
END
END
ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued tran')
BEGIN
-- Queued updating subscription
-- 2 = queued, 4 = sqlqueued
SELECT @update_mode_id = case
when (@publication_queue_type = 2) then 4
else 2 end
-- Check if publication allows this option
-- If the publication allow synctran, it allows queued tran.
IF @allow_queued_tran_id <> 1
BEGIN
RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'queued tran')
RETURN (1)
END
END
ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'failover')
BEGIN
-- Failover subscription with initial state as Immediate
-- 3 = failover, 5 = sqlqueued failover
SELECT @update_mode_id = case
when (@publication_queue_type = 2) then 5
else 3 end
-- Check if publication allows this option
IF @allow_sync_tran_id <> 1
BEGIN
RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'sync tran')
RETURN (1)
END
-- Check if publication allows this option
IF @allow_queued_tran_id <> 1
BEGIN
RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','queued tran')
RETURN (1)
END
END
ELSE IF (LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued failover')
BEGIN
-- Failover subscription with initial state as Queued
-- 6 = sqlqueued qfailover, 7 = qfailover
SELECT @update_mode_id = case
when (@publication_queue_type = 2) then 6
else 7 end
-- Check if publication allows this option
IF @allow_sync_tran_id <> 1
BEGIN
RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription', 'sync tran')
RETURN (1)
END
-- Check if publication allows this option
IF @allow_queued_tran_id <> 1
BEGIN
RAISERROR (20503, 16, -1, '@update_mode', 'sp_addsubscription','queued tran')
RETURN (1)
END
END
ELSE
BEGIN
-- Read only subscription
SELECT @update_mode_id = 0
END
IF sys.fn_yukonsecuritymodelrequired(NULL) = 0
BEGIN
-- Parameter Check: @dts_package_location
-- Valid values:
-- distributor
-- subscriber
IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('distributor', 'subscriber')
BEGIN
RAISERROR(21179, 16, -1)
RETURN (1)
END
declare @dts_package_location_id int
IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = 'distributor'
SELECT @dts_package_location_id = 0
ELSE
SELECT @dts_package_location_id = 1
-- @dts_package_password cannot be non-null if @dts_package_name is not set
if (@dts_package_name is null or rtrim(@dts_package_name) = N'') and
@dts_package_password is not null
begin
raiserror(18780, 16, -1)
return (1)
end
-- Have to be a push, non updatable subscription to set DTS package name
if @dts_package_name is not null
begin
if @subscription_type_id != 0
begin
RAISERROR(21181, 16, -1)
RETURN (1)
end
if @allow_dts = 0
begin
RAISERROR(21178, 16, -1)
RETURN (1)
end
end
END
ELSE
BEGIN
SELECT @dts_package_location_id = 0
END
/** For immediate_sync publication, @article has to be 'all' */
IF @reserved NOT IN (@internal, @nosync)
AND @immediate_sync = 1
AND NOT LOWER(@article) = 'all'
BEGIN
RAISERROR (14122, 16, -1)
RETURN (1)
END
/*
** For full subscription, check to see if subscriptions
** to ALL the articles exist before expanding parameter @article.
**
*/
IF LOWER(@article) = 'all'
AND @reserved NOT IN (@internal, @nosync)
AND EXISTS (SELECT * FROM syspublications WHERE pubid = @pubid)
BEGIN
SELECT @count_arts = count(*) FROM sysextendedarticlesview art
WHERE art.pubid = @pubid
if @count_arts = 0
BEGIN
RAISERROR (14124, 16, -1)
RETURN(1)
END
SELECT @count_subs = count(*) FROM syssubscriptions sub,
sysextendedarticlesview art
WHERE sub.srvname = UPPER(@subscriber)
AND sub.srvid >= 0
AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
AND sub.artid = art.artid
AND art.pubid = @pubid
IF @count_arts = @count_subs
BEGIN
RAISERROR (14058, 16, -1)
RETURN (1)
END
END
/*
** Real subscription to inactive publicaton is not allowed
** Note, subscriptions to the new article will be added automatically
** for immediate_sync publications. At that time, the publication may not
** be active.
*/
IF @srvid <> @virtual_id
AND @pubstatus = 0
AND @reserved NOT IN (@internal, @nosync)
BEGIN
RAISERROR (21000, 16, -1)
RETURN (1)
END
IF sys.fn_yukonsecuritymodelrequired(NULL) = 0
BEGIN
-- If the publication is 'allow_dts', push subscription has to specify a DTS package.
-- Error check that disallow ODBC subscriber to subscriber with DTS package
-- is at the distributor.
-- Show dts error first, otherwise user will get 21060 below which is confusing
IF @allow_dts <> 0
and @dts_package_name is null
and @subscriber IS not NULL
and @reserved NOT IN (@internal, @nosync)
and @subscription_type_id = 0
begin
raiserror(21213, 16, -1)
return(1)
end
END
/*
** Do special things for DSN subscribers.
*/
IF @subscriber IS NOT NULL AND @non_sql_flag <> 0
BEGIN
-- DSN or oledb subscriber not using DTS
-- cannot subscribe to native mode or concurrent snapshot publication
IF @sync_method NOT IN (@char_bcp, @concurrent_char, @database_snapshot_character) and @dts_package_name is null and @publisher_type = N'MSSQLSERVER'
BEGIN
RAISERROR (14095, 16, -1, @publication, @subscriber)
RETURN (1)
END
IF @sync_method NOT IN (@char_bcp, @concurrent_char, @database_snapshot_character) AND @publisher_type != N'MSSQLSERVER'
BEGIN
RAISERROR (21676, 16, -1, @subscriber, @publication)
RETURN (1)
END
-- DSN subscriber cannot subscribe with 'Sync Update'
IF @update_mode_id <> 0
BEGIN
RAISERROR (21032, 16, -1, @subscriber)
RETURN (1)
END
END
-- DNS may define db. If no db given, specify that DSN default should be used.
-- use internal values
if @subscriber IS NOT NULL and @destination_db is NULL
begin
IF @non_sql_flag <> 0
SELECT @destination_db = @dsn_dbname
else
SELECT @destination_db = DB_NAME()
-- @destination_db cannot be master
if LOWER(@destination_db) = 'master'
BEGIN
RAISERROR (21481, 16, 1)
RETURN (1)
END
end
-- if we're subscribing to a dump type publication, error
-- out if this subscriber has any other subscriptions to publications
-- other than this one
IF @sync_method = 2
BEGIN
IF EXISTS( SELECT * FROM syssubscriptions sub, sysextendedarticlesview art
WHERE sub.srvname = UPPER(@subscriber)
AND sub.srvid >= 0
AND ((@destination_db = N'%') OR (sub.dest_db = @destination_db))
AND sub.artid = art.artid
AND art.pubid != @pubid )
BEGIN
RAISERROR(21144, 16, -1)
RETURN 1
END
END
-- else if we're subscribing to a char or native mode publication,
-- error out if the subscriber is subscribed to any dump type publications
ELSE
BEGIN
IF EXISTS
(
SELECT *
FROM syssubscriptions sub,
sysextendedarticlesview art,
syspublications pub
WHERE sub.srvname = UPPER(@subscriber)
AND sub.srvid >= 0
AND sub.dest_db = @destination_db
AND sub.artid = art.artid
AND art.pubid != @pubid
AND pub.pubid = art.pubid
AND pub.sync_method = 2
)
BEGIN
RAISERROR(21145, 16, -1, @publication )
RETURN 1
END
END
IF LOWER(@article) = 'all'
/*
** Get all articles in the publication that are not subscribed to
** by the @subscriber
*/
BEGIN
/*
** Make the operation atomic. This is to prevent multiple subscription_type
** from one subscriber on an immediate_sync publication
*/
BEGIN TRAN
IF @publisher IS NULL
BEGIN
DECLARE hCx CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT a.name
FROM sysextendedarticlesview a,
syspublications b
WHERE a.pubid = b.pubid
AND b.pubid = @pubid
AND NOT EXISTS
(
SELECT *
FROM syssubscriptions s
WHERE s.artid = a.artid
AND s.status <> 0
AND (@subscriber IS NULL and s.srvid = @srvid or (@subscriber IS not NULL) and s.srvname = UPPER(@subscriber))
AND s.dest_db = @destination_db
)
FOR READ ONLY
END
ELSE -- is this hpub? otherwise how can we have syspublications and MSpublications in the same db
BEGIN
DECLARE hCx CURSOR LOCAL FAST_FORWARD FOR
SELECT DISTINCT a.name
FROM sysextendedarticlesview a,
syspublications b,
MSpublications m,
master.dbo.sysservers n
WHERE a.pubid = b.pubid
AND m.publication_id = a.pubid
AND m.publisher_id = n.srvid
AND UPPER(n.srvname collate database_default) = UPPER(@publisher) collate database_default
AND b.pubid = @pubid
AND NOT EXISTS
(
SELECT *
FROM syssubscriptions s,
MSarticles m,
master.dbo.sysservers n
WHERE s.artid = a.artid
AND s.status <> 0
AND s.srvid = @srvid
AND s.dest_db = @destination_db
AND m.article_id = s.artid
AND m.publisher_id = n.srvid
AND UPPER(n.srvname collate database_default) = UPPER(@publisher) collate database_default
)
FOR READ ONLY
END
OPEN hCx
FETCH hCx INTO @article
WHILE (@@fetch_status <> -1)
BEGIN
EXECUTE @retcode = sys.sp_MSrepl_addsubscription_article
@publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@sync_type = @sync_type,
@status = @status,
@subscription_type = @subscription_type,
@reserved = @internal,
@update_mode = @update_mode,
@loopback_detection = @loopback_detection,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@optional_command_line = @optional_command_line,
@enabled_for_syncmgr = @enabled_for_syncmgr,
@dts_package_name = @dts_package_name,
@dts_package_password = @dts_package_password,
@dts_package_location = @dts_package_location,
@distribution_job_name = @distribution_job_name,
@publisher = @publisher,
@publisher_type = @publisher_type
,@subscriptionstreams = @subscriptionstreams
,@subscriber_type = @subscriber_type
,@pubid = @pubid
,@non_sql_flag = @non_sql_flag
,@publisher_local = @publisher_local
,@immediate_sync = @immediate_sync
,@srvid = @srvid
,@independent_agent_id = @independent_agent_id
,@dts_package_location_id = @dts_package_location_id
,@immediate_sync_ready = @immediate_sync_ready
,@sync_method = @sync_method
,@subscription_type_id = @subscription_type_id
,@pubstatus = @pubstatus
,@allow_anonymous = @allow_anonymous
,@update_mode_id = @update_mode_id
IF @@error <> 0 OR @retcode <> 0
BEGIN
CLOSE hCx
DEALLOCATE hCx
IF @@trancount > 0
ROLLBACK TRAN
RETURN (1)
END
FETCH hCx INTO @article
END
CLOSE hCx
DEALLOCATE hCx
COMMIT TRAN
RETURN (0)
END
-- We are adding a single subscription.
EXECUTE @retcode = sys.sp_MSrepl_addsubscription_article
@publication = @publication,
@article = @article,
@subscriber = @subscriber,
@destination_db = @destination_db,
@sync_type = @sync_type,
@status = @status,
@subscription_type = @subscription_type,
@reserved = @internal,
@update_mode = @update_mode,
@loopback_detection = @loopback_detection,
@frequency_type = @frequency_type,
@frequency_interval = @frequency_interval,
@frequency_relative_interval = @frequency_relative_interval,
@frequency_recurrence_factor = @frequency_recurrence_factor,
@frequency_subday = @frequency_subday,
@frequency_subday_interval = @frequency_subday_interval,
@active_start_time_of_day = @active_start_time_of_day,
@active_end_time_of_day = @active_end_time_of_day,
@active_start_date = @active_start_date,
@active_end_date = @active_end_date,
@optional_command_line = @optional_command_line,
@enabled_for_syncmgr = @enabled_for_syncmgr,
@dts_package_name = @dts_package_name,
@dts_package_password = @dts_package_password,
@dts_package_location = @dts_package_location,
@distribution_job_name = @distribution_job_name,
@publisher = @publisher,
@publisher_type = @publisher_type
,@subscriptionstreams = @subscriptionstreams
,@subscriber_type = @subscriber_type
,@pubid = @pubid
,@non_sql_flag = @non_sql_flag
,@publisher_local = @publisher_local
,@immediate_sync = @immediate_sync
,@srvid = @srvid
,@independent_agent_id = @independent_agent_id
,@dts_package_location_id = @dts_package_location_id
,@immediate_sync_ready = @immediate_sync_ready
,@sync_method = @sync_method
,@subscription_type_id = @subscription_type_id
,@pubstatus = @pubstatus
,@allow_anonymous = @allow_anonymous
,@update_mode_id = @update_mode_id
IF @@error <> 0 OR @retcode <> 0
BEGIN
RETURN (1)
END
RETURN (0)
END
