April 13, 2012

sp_addpullsubscription (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_addpullsubscription(nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @independent_agent
, nvarchar @subscription_type
, nvarchar @description
, nvarchar @update_mode
, bit @immediate_sync)

MetaData:

 create procedure sys.sp_addpullsubscription  
(
@publisher sysname,
@publisher_db sysname = NULL,
@publication sysname, -- publication name --
@independent_agent nvarchar(5) = 'true', -- true or false --
@subscription_type nvarchar(9) = 'anonymous', -- subscription_type, pull or anonymous --
@description nvarchar(100) = NULL,
@update_mode nvarchar(30) = 'read only', -- Can be 'read only', 'sync tran', 'queued tran', 'failover', 'queued failover'
@immediate_sync bit = 1
)
AS
BEGIN

SET NOCOUNT ON

--
-- Declarations.
--

DECLARE @retcode int
DECLARE @subscription_type_id int -- 1 = pull, 2 = anonymous --
DECLARE @independent_agent_id bit
DECLARE @update_mode_id tinyint

-- For attach
if exists (select * from sys.objects where name = 'MSrepl_restore_stage')
begin
raiserror(21211, 16, -1)
return 1
end
--
-- Check if replication components are installed on this server
--
exec @retcode = sys.sp_MS_replication_installed
if (@retcode <> 1)
begin
return (1)
end
--
-- Security Check
--
EXEC @retcode = sys.sp_MSreplcheck_subscribe
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)
--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is define
--
IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_addpullsubscription')
RETURN (1)
END

IF @publisher = 'all'
BEGIN
RAISERROR (14136, 16, -1)
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @publisher
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)

--
-- Parameter Check: @publisher_db
--
IF @publisher_db = 'all'
BEGIN
RAISERROR (14136, 16, -1)
RETURN (1)
END

IF @publisher_db IS NOT NULL
BEGIN
EXECUTE @retcode = sys.sp_validname @publisher_db
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
END
ELSE
BEGIN
-- @publisher_db is NULL for Oracle publishers only
SET @publisher_db = @publisher
END

--
-- Parameter Check: @publication
--
IF @publication IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publication', 'sp_addpullsubscription')
RETURN (1)
END

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

EXECUTE @retcode = sys.sp_validname @publication
IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)
--
-- Current database (subscribing database cannot be master)
--
if (lower(db_name()) = 'master')
begin
RAISERROR (21481, 16, 1)
RETURN (1)
end

IF @independent_agent IS NULL OR LOWER(@independent_agent collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@independent_agent')
RETURN (1)
END

IF LOWER(@independent_agent collate SQL_Latin1_General_CP1_CS_AS) = 'true'
SELECT @independent_agent_id = 1
ELSE
SELECT @independent_agent_id = 0
--
-- Parameter Check: @subscription_type
-- The @status value can be:
--
-- type_id type
-- ====== ========
-- 0 push
-- 1 pull
-- 2 anonymous
--
-- Note: @subscription_type = push is only used by distribution agents
--
IF @subscription_type IS NULL OR LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('push', 'pull','anonymous')
BEGIN
RAISERROR (20016, 16, -1)
RETURN (1)
END

IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'pull'
SELECT @subscription_type_id = 1
ELSE IF LOWER(@subscription_type collate SQL_Latin1_General_CP1_CS_AS) = 'anonymous'
SELECT @subscription_type_id = 2
ELSE
SELECT @subscription_type_id = 0

IF @independent_agent_id = 0 AND @subscription_type_id = 2
BEGIN
RAISERROR (21026, 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
-- Anonymous subscriptions should not be allowed to
-- subscribe with 'synctran option'
IF LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) in ('sync tran', 'queued tran', 'failover') and
@subscription_type_id = 2
begin
RAISERROR (21057, 16, -1)
RETURN (1)
end
--
-- set the @update_mode_id
-- NOTE when the distribution agent does the subscription initialization
-- the queued failover case will be update to a valie of (3 or 5)based on
-- the queue type - as the failover state in MSsubscription_agents will then
-- correctly identify the current state of update_mode on the subscriber
--
select @update_mode_id = case
when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'sync tran' then 1
when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued tran' then 4
when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'failover' then 5
when LOWER(@update_mode collate SQL_Latin1_General_CP1_CS_AS) = 'queued failover' then 6
else 0
end

begin tran
save TRAN addpullsubscription

--
-- Check to see if MSreplication_subscriptions and MSsubscription_properties
-- tables exists. If not, create it.
--
exec @retcode = sys.sp_MScreate_sub_tables_internal
@tran_sub_table = 1,
@property_table = 1
IF @@ERROR <> 0 or @retcode <> 0
goto UNDO
--
-- Check to make sure that the subscription does not already exist
--
declare @sub_type int
select @sub_type = subscription_type
from MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher) AND
publisher_db = @publisher_db AND
publication = @publication
--
-- It is not allowed to subscribe to the same publication twice even with
-- differnet subscription type. Raise special error for push
--
if @sub_type is not null
begin
if @sub_type = 0
begin
raiserror (20594, 16, -1)
GOTO UNDO
end
else
begin
RAISERROR (14058, 16, -1)
GOTO UNDO
end
end
--
-- Add the subscription
--
INSERT MSreplication_subscriptions (publisher,
publisher_db,
publication,
independent_agent,
subscription_type,
distribution_agent,
description,
time,
transaction_timestamp,
update_mode,
immediate_sync)
VALUES (@publisher,
@publisher_db,
@publication,
@independent_agent_id,
@subscription_type_id,
NULL,
@description,
getdate(),
0,
@update_mode_id,
@immediate_sync
)
IF @@ERROR <> 0
BEGIN
RAISERROR (14057, 16, -1)
GOTO UNDO
END

COMMIT TRAN
RETURN (0)

UNDO:
IF @@TRANCOUNT > 0
begin
ROLLBACK TRAN addpullsubscription
COMMIT TRAN
end
return 1
END

No comments:

Post a Comment

Total Pageviews