May 7, 2012

sp_MSadd_subscriber_info (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_subscriber_info(nvarchar @publisher
, nvarchar @subscriber
, tinyint @type
, nvarchar @login
, nvarchar @password
, int @commit_batch_size
, int @status_batch_size
, int @flush_frequency
, 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
, int @retryattempts
, int @retrydelay
, nvarchar @description
, int @security_mode
, bit @encrypted_password
, nvarchar @internal)

MetaData:

 CREATE PROCEDURE sys.sp_MSadd_subscriber_info  
(
@publisher sysname,
@subscriber sysname,
@type tinyint = 0,
@login sysname = NULL,
@password nvarchar(524) = NULL,
@commit_batch_size int = 100,
@status_batch_size int = 100,
@flush_frequency int = 0,
@frequency_type int = 4,
@frequency_interval int = 1,
@frequency_relative_interval int = 1,
@frequency_recurrence_factor int = 0,
@frequency_subday int = 4,
@frequency_subday_interval int = 5,
@active_start_time_of_day int = 0,
@active_end_time_of_day int = 235959,
@active_start_date int = 0,
@active_end_date int = 99991231,
@retryattempts int = 0,
@retrydelay int = 0,
@description nvarchar (255) = NULL,
@security_mode int = 1, -- 0 standard; 1 integrated --
@encrypted_password bit = 0,
@internal sysname = N'PRE-YUKON' -- Can be: 'PRE-YUKON', 'YUKON', 'BOTH'
)
AS
BEGIN
set nocount on

declare @retcode int
declare @oledbprovider nvarchar(256)
declare @platform_nt binary
declare @original_type int
declare @message nvarchar(1000)

select @platform_nt = 0x1

-- Security Check
IF IS_SRVROLEMEMBER ('sysadmin') != 1
BEGIN
-- "You do not have sufficient permission to run this command."
RAISERROR(14260, 16, -1)
RETURN 1
END

IF (UPPER(@subscriber) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
BEGIN
RAISERROR(21038, 16, -1)
RETURN (1)
END

-- Check to ensure a login is provided if security mode is SQL Server authentication.
select @login = rtrim(ltrim(isnull(@login, '')))

-- Security Mode = 1
if @security_mode = 1
begin
select @login = N'',
@password = newid()
end
-- Security Mode = 0
else if @login = ''
begin
-- '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
raiserror(21694, 16, -1, '@login', '@security_mode')
return 1
end

-- Verify subscriber is not a HREPL publisher.
-- Only allow if publisher is not HREPL
IF EXISTS
(
SELECT srvname
FROM master.dbo.sysservers ss LEFT OUTER JOIN msdb.dbo.MSdistpublishers msdp
ON ss.srvname = msdp.name
WHERE UPPER(ss.srvname) = UPPER(@subscriber) collate database_default
AND ss.pub = 1
AND msdp.publisher_type != N'MSSQLSERVER'
)
BEGIN
RAISERROR (21677, 16, -1, @subscriber)
RETURN(1)
END

-- Add the subscriber to dbo.sysservers as a RPC server, if it does not
-- already exist.
--
if not exists (select * from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber))
begin
DECLARE @upper_subscriber sysname

SELECT @upper_subscriber = UPPER(@subscriber collate database_default)

exec @retcode = dbo.sp_addserver @upper_subscriber
if @retcode <> 0
return 1
end

-- Encrypt the password
-- We no longer supported passing in encrypted passwords
IF @encrypted_password = 1
BEGIN
-- Parameter '@encrypted_password' is no longer supported.
RAISERROR(21698, 16, -1, '@encrypted_password')
RETURN (1)
END

if (@type = 3)
begin
select @oledbprovider = providername from master.dbo.sysservers where UPPER(srvname) = UPPER(@subscriber)
if (@oledbprovider = 'sqloledb')
select @security_mode = 1
else
select @security_mode = 0
end

-- retrieve the stored type if a subscriber entry exists so
-- that we can verify if we need to do any extra processing
-- basically we never want to add the entry if it already exists
SELECT @original_type = type
FROM MSsubscriber_info
WHERE UPPER(subscriber) = UPPER(@subscriber)
AND UPPER(publisher) = UPPER(@publisher)
IF @original_type IS NOT NULL
BEGIN
-- if the types match or we are an internal
-- call then do not fail, just exit w/o err
IF @original_type = @type
OR @internal = N'YUKON'
BEGIN
RETURN 0
END

SELECT @message = @subscriber + ''', type = ''' + CAST(@original_type as nvarchar)

-- The server '@server', type = '1' already exists.
RAISERROR(15028, 16, -1, @message)
RETURN 1
END

EXEC @retcode = sys.sp_MSreplencrypt @password OUTPUT
IF @@error <> 0 OR @retcode <> 0
return 1

begin tran
save TRAN addsub_info

insert MSsubscriber_info (publisher, subscriber, type, login, password, description, security_mode)
values (@publisher, @subscriber, @type, @login, @password, @description, @security_mode)
if @@error <> 0
goto UNDO

--
-- Schedule information is added for backward compartibility reason, agent_type = 0
--
insert MSsubscriber_schedule values(@publisher, @subscriber, 0, @frequency_type,
@frequency_interval,
@frequency_relative_interval,
@frequency_recurrence_factor ,
@frequency_subday ,
@frequency_subday_interval,
@active_start_time_of_day,
@active_end_time_of_day ,
@active_start_date ,
@active_end_date )
if @@error <> 0
goto UNDO
COMMIT TRAN

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

No comments:

Post a Comment

Total Pageviews