April 13, 2012

sp_addsubscriber_schedule (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_addsubscriber_schedule(nvarchar @subscriber
, smallint @agent_type
, 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 @publisher)

MetaData:

 create procedure sys.sp_addsubscriber_schedule (  
@subscriber sysname,
@agent_type smallint = 0,
@frequency_type int = 64,
@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,
@publisher sysname = NULL
) AS

DECLARE @distributor sysname
DECLARE @distribdb sysname
DECLARE @distproc nvarchar (300)
DECLARE @retcode int
DECLARE @msg nvarchar(255)

-- Heterogeneous publishers
DECLARE @publisher_local sysname

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

--
-- If @publisher is NULL, set @publisher_local to @@SERVERNAME
-- otherwise, set @publisher_local to @publisher
--
IF @publisher IS NULL
BEGIN
select @publisher_local = publishingservername()
END
ELSE
BEGIN
-- Check if publisher exists --
EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher
IF @retcode <> 0
RETURN (@retcode)
-- For heterogeneous publisher, the current database must be the --
-- distribution db for the publisher --

select @publisher_local = @publisher

IF NOT EXISTS ( select * from master.dbo.sysservers s, msdb..MSdistpublishers m
where UPPER(s.srvname collate database_default) = UPPER(m.name collate database_default)
and UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
and UPPER(m.distribution_db collate database_default) = UPPER(DB_NAME()) collate database_default
)
RETURN (1)

IF @@ERROR <> 0
RETURN (1)

END

--
-- Parameter Check: @subscriber.
-- Check to make sure that the subscriber doesn't already exist, and
-- that the name is a valid non-null identifier.
--

IF @subscriber IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@subscriber', 'sp_addsubscriber_schedule')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @subscriber

IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)

if LOWER(@subscriber) = 'all'
BEGIN
RAISERROR (14032, 16, -1, '@subscriber')
RETURN (1)
END

EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT,
@publisher = @publisher

IF @@error <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

IF @retcode <> 0 OR @distribdb IS NULL OR @distributor IS NULL
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END

SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribdb)) + '.dbo.sp_MSadd_subscriber_schedule'
EXEC @retcode = @distproc
@publisher_local,
@subscriber,
@agent_type,
@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 OR @retcode <> 0
BEGIN
RAISERROR (14042, 16, -1)
RETURN (1)
END

RETURN (0)

sp_addsubscriber (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_addsubscriber(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
, nvarchar @description
, int @security_mode
, bit @encrypted_password
, nvarchar @publisher)

MetaData:

   
create procedure sys.sp_addsubscriber
(
@subscriber sysname,
@type tinyint = 0,
@login sysname = NULL,
@password nvarchar(524) = NULL,
@commit_batch_size int = NULL,
@status_batch_size int = NULL,
@flush_frequency int = NULL,
@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,
@description nvarchar (255) = NULL,
@security_mode int = NULL, -- backward compatible -- -- 0 standard; 1 integrated --
@encrypted_password bit = NULL,
@publisher sysname = NULL
)
AS
BEGIN
DECLARE @cmd nvarchar(4000)
DECLARE @retcode int
DECLARE @publisher_type sysname

--
-- Security Check: require sysadmin
--
IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
BEGIN
RAISERROR(21089,16,-1)
RETURN (1)
END

SET @retcode = 0

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@rpcheader = @cmd OUTPUT,
@publisher_type = @publisher_type OUTPUT


IF @retcode <> 0
RETURN (@retcode)

-- when called on mirror server, this adds original publiser name
-- in distribution. (MSsubscriber_info and MSsubscriber_schedule)
IF @publisher IS NULL
SET @publisher = publishingservername()

-- Add sp
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
set @cmd = @cmd + N'sys.sp_MSrepl_addsubscriber'

EXEC @retcode = @cmd
@subscriber,
@type,
@login,
@password,
@commit_batch_size,
@status_batch_size,
@flush_frequency,
@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,
@description,
@security_mode,
@encrypted_password,
@publisher,
@publisher_type,
N'PRE-YUKON'

RETURN (@retcode)
END

sp_addsrvrolemember (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_addsrvrolemember(nvarchar @loginame
, nvarchar @rolename)

MetaData:

 --  FOR BACKWARD COMPATIBILTY ONLY --   
create procedure sys.sp_addsrvrolemember
@loginame sysname, -- login name
@rolename sysname = NULL -- server role name
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int, -- return value of sp call
@stmtR nvarchar(4000)

-- Was a part of check to prevent inside a user Xact. Leave for backward compat.--
set implicit_transactions off

exec @ret = sys.sp_validname @rolename
if @ret <> 0
return(1)

exec @ret = sys.sp_validname @loginame
if @ret <> 0
return(1)

BEGIN TRANSACTION
-- LOCK LOGIN --
EXEC %%LocalLogin(Name = @loginame).Lock(Exclusive = 1)

-- ADD ROW FOR NT LOGIN IF NEEDED --
if @@error <> 0 -- not found
begin
execute @ret = sys.sp_MSaddlogin_implicit_ntlogin @loginame
if (@ret <> 0)
begin
ROLLBACK TRANSACTION
raiserror(15007,-1,-1,@loginame)
return (1)
end
-- login locked
end

set @stmtR = 'alter server role '
set @stmtR = @stmtR + quotename(@rolename, ']')
set @stmtR = @stmtR + ' add member '
set @stmtR = @stmtR + quotename(@loginame, ']')

-- issue DDL to create the role --
exec (@stmtR)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end

-- RETURN SUCCESS --
COMMIT TRANSACTION
return (0) -- sp_addsrvrolemember

sp_addserver (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_addserver(nvarchar @server
, varchar @local
, varchar @duplicate_ok)

MetaData:

 create procedure sys.sp_addserver  
@server sysname, -- server name
@local varchar(10) = NULL, -- NULL or 'local'
@duplicate_ok varchar(13) = NULL -- NULL or 'duplicate_ok'
as
-- VARS
DECLARE @localentry bit,
@dup_ok bit,
@retcode int
select @duplicate_ok = LOWER (@duplicate_ok collate Latin1_General_CI_AS)

-- VALIDATE PARAMETERS/OPTIONS
SELECT @localentry = CASE WHEN @local IS NULL THEN 0
WHEN lower(@local) = 'local' THEN 1
ELSE NULL END,
@dup_ok = CASE WHEN @duplicate_ok IS NULL THEN 0
WHEN @duplicate_ok = 'duplicate_ok' THEN 1
ELSE NULL END
IF @localentry IS NULL OR @dup_ok IS NULL
BEGIN
raiserror(15600,-1,-1,'sys.sp_addserver')
return (1)
END

-- DISALLOW USER TRANSACTION
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_addlinkedserver')
return (1)
end

BEGIN TRANSACTION

-- ADD THE SERVER (CHECKS PERMISSIONS, ETC)
EXEC @retcode = sys.sp_MSaddserver_internal @server,
NULL, NULL, NULL, NULL, NULL, NULL, -- @srvproduct ... @catalog
0, -- @linkedstyle
@localentry

if( @retcode = 0)
begin
if (@localentry = 1)
begin
-- EMDEventType(x_eet_Alter_Instance), EMDUniversalClass(x_eunc_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 214, ID = 100, ID = 0, ID = 0, Value = NULL,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 3, Value = @server, Value = @local, Value = @duplicate_ok, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end
else
begin
-- EMDEventType(x_eet_Create_Remote_Server), EMDUniversalClass(x_eunc_Server), src major id, src minor id, src name
-- -1 means ignore target stuff, target major id, target minor id, target name,
-- # of parameters, 5 parameters
EXEC %%System().FireTrigger(ID = 230, ID = 100, ID = 0, ID = 0, Value = @server,
ID = -1, ID = 0, ID = 0, Value = NULL,
ID = 3, Value = @server, Value = @local, Value = @duplicate_ok, Value = NULL, Value = NULL, Value = NULL, Value = NULL)
end

COMMIT TRANSACTION
-- SUCCESS
return (0) -- sp_addserver
end
else if( @retcode = 2 )
begin
ROLLBACK

if @dup_ok = 1
return (0)
else
begin
raiserror(15028,-1,-1,@server);
return (1);
end;
end
else if( @retcode = 3 )
begin
ROLLBACK

raiserror(28401,16,20,'sp_addserver');
return (1)
end
else
begin
ROLLBACK
return (1)
end

sp_addscriptexec (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_addscriptexec(nvarchar @publication
, nvarchar @scriptfile
, bit @skiperror
, nvarchar @publisher)

MetaData:

 create procedure sys.sp_addscriptexec  
(
@publication sysname,
@scriptfile nvarchar (4000),
@skiperror bit = 0,
@publisher sysname = NULL
)
AS
BEGIN
DECLARE @cmd nvarchar(4000)
DECLARE @retcode int

SET @retcode = 0

EXEC @retcode = sys.sp_MSrepl_getpublisherinfo @publisher = @publisher,
@rpcheader = @cmd OUTPUT

IF @retcode <> 0
RETURN (@retcode)

-- Add sp
SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
set @cmd = @cmd + N'sys.sp_MSrepl_addscriptexec'

EXEC @retcode = @cmd
@publication,
@scriptfile,
@skiperror

RETURN (@retcode)
END

sp_addrolemember (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_addrolemember(nvarchar @rolename
, nvarchar @membername)

MetaData:

 --  FOR BACKWARD COMPATIBILTY ONLY --   
create procedure sys.sp_addrolemember
@rolename sysname,
@membername sysname
AS
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int, -- return value of sp call
@memuid int,
@stmtR nvarchar(4000)

-- Was a part of check to prevent inside a user Xact. Leave for backward compat.--
set implicit_transactions off

exec @ret = sys.sp_validname @rolename
if @ret <> 0
return(1)

exec @ret = sys.sp_validname @membername
if @ret <> 0
return(1)

BEGIN TRANSACTION

-- EXCL LOCK MEMBER --
EXEC %%Owner(Name = @membername).Lock(Exclusive = 1) -- may fail

-- ATTEMPT ADDING IMPLICIT ROW FOR NT NAME --
if @@error <> 0 -- not found
begin
EXEC @ret = sys.sp_MSadduser_implicit_ntlogin @membername
if (@ret <> 0)
begin
ROLLBACK TRANSACTION
raiserror(15410, -1, -1, @membername)
return (1)
end
-- Member locked by sp_MSadduser_implicit_ntlogin
end

set @stmtR = 'alter role '
set @stmtR = @stmtR + quotename(@rolename, ']')
set @stmtR = @stmtR + ' add member '
set @stmtR = @stmtR + quotename(@membername, ']')

-- issue DDL to create the role --
exec (@stmtR)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end

-- RETURN SUCCESS --
COMMIT TRANSACTION
return (0) -- sp_addrolemember

sp_addrole (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_addrole(nvarchar @rolename
, nvarchar @ownername)

MetaData:

 create procedure sys.sp_addrole  
@rolename sysname, -- name of new role
@ownername sysname = NULL -- name of owner of new role
as
-- SETUP RUNTIME OPTIONS / DECLARE VARIABLES --
set nocount on
declare @ret int, -- return value of sp call
@owner int,
@stmtR nvarchar(4000),
@stmtS nvarchar(4000)

-- DISALLOW USER TRANSACTION --
set implicit_transactions off
if (@@trancount > 0)
begin
raiserror(15002,-1,-1,'sys.sp_addrole')
return (1)
end

exec @ret = sys.sp_validname @rolename
if @ret <> 0
return(1)

if @ownername is null
select @ownername = user_name()

set @stmtR = 'create role '
set @stmtR = @stmtR + quotename(@rolename, ']')
set @stmtR = @stmtR + ' authorization '
set @stmtR = @stmtR + quotename(@ownername, ']')

-- Create Schema if not one exists with same name and owner
set @stmtS = 'create schema '
set @stmtS = @stmtS + quotename(@rolename, ']')
set @stmtS = @stmtS + ' authorization '
set @stmtS = @stmtS + quotename(@rolename, ']')

BEGIN TRANSACTION

-- issue DDL to create the role --
exec (@stmtR)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end


-- create the Schema if not one exists with same name and owner
declare @schema_uid int
declare @owner_uid int
select @owner_uid = principal_id from sys.database_principals where name = @rolename
select @schema_uid = principal_id from sys.schemas where name = @rolename
if (@schema_uid is null or -- there is no schema since if there is one, it will have an owner
@schema_uid <> @owner_uid) -- for error message
begin
exec (@stmtS)
if @@error <> 0
begin
ROLLBACK TRANSACTION
return (1)
end
end

COMMIT TRANSACTION

-- RETURN SUCCESS --
return (0) -- sp_addrole

sp_addremotelogin (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_addremotelogin(nvarchar @remoteserver
, nvarchar @loginame
, nvarchar @remotename)

MetaData:

 create procedure sys.sp_addremotelogin  
@remoteserver sysname, -- name of remote server
@loginame sysname = NULL, -- user's local user name
@remotename sysname = NULL -- user's remote name
as
declare @localid int

-- DISALLOW USER XACT --
set implicit_transactions off
if @@trancount > 0
begin
raiserror(15002,-1,-1,'sys.sp_addremotelogin')
return (1)
end

-- CHECK PERMISSIONS
if not (has_perms_by_name(null, null, 'alter any login') = 1)
begin
EXEC %%System().AuditEvent(ID = 1380142162, Success = 0, TargetLoginName = @loginame, TargetUserName = NULL, Role = NULL, Object = @remotename, Provider = NULL, Server = @remoteserver)
raiserror(15247,-1,-1)
return (1)
end
else
EXEC %%System().AuditEvent(ID = 1380142162, Success = 1, TargetLoginName = @loginame, TargetUserName = NULL, Role = NULL, Object = @remotename, Provider = NULL, Server = @remoteserver)

-- CHECK FOR INVALID PARAMETER SYNTAX --
if @remoteserver is null OR (@loginame is null and @remotename is not null)
begin
raiserror(15600,-1,-1,'sys.sp_addremotelogin')
return (1)
end

BEGIN TRAN

-- VALIDATE/LOCK SERVER NAME --
EXEC %%LinkedServer ( Name = @remoteserver ) . Lock ( Exclusive = 1 )
IF @@ERROR <> 0
begin
ROLLBACK TRAN
raiserror(15015,-1,-1,@remoteserver)
return (1)
end

-- VALIDATE/LOCK @loginame --
select @localid = 0
if @loginame is not null
begin
-- share-lock the local login
EXEC %%LocalLogin ( Name = @loginame ) . Lock ( Exclusive = 0 )
IF @@ERROR = 0
select @localid = principal_id from sys.server_principals
where name = @loginame and type = 'S' -- cannot map to NT login
if @localid = 0
begin
ROLLBACK TRAN
raiserror(15007,-1,-1,@loginame)
return (1)
end
end

-- ADD NEW REMOTE-LOGIN (WILL CHECK FOR DUPLICATE <@remoteserver, @remotename> PAIR)
EXEC %%LinkedServer(Name=@remoteserver).NewRemoteLogin( RemoteName = @remotename, LocalID = @localid )
IF @@ERROR <> 0
begin
ROLLBACK TRAN
if @remotename is null
raiserror(15066,-1,-1,@remoteserver)
else
raiserror(15068,-1,-1,@remotename,@remoteserver)
return (1)
end

-- SUCCESS --
COMMIT TRAN
return (0) -- sp_addremotelogin

sp_addpullsubscription_agent (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_agent(nvarchar @job_password
, nvarchar @publisher
, nvarchar @publisher_db
, nvarchar @publication
, nvarchar @subscriber
, nvarchar @subscriber_db
, int @subscriber_security_mode
, nvarchar @subscriber_login
, nvarchar @subscriber_password
, nvarchar @distributor
, nvarchar @distribution_db
, int @distributor_security_mode
, nvarchar @distributor_login
, nvarchar @distributor_password
, nvarchar @optional_command_line
, 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
, bit @encrypted_distributor_password
, nvarchar @enabled_for_syncmgr
, nvarchar @ftp_address
, int @ftp_port
, nvarchar @ftp_login
, nvarchar @ftp_password
, nvarchar @alt_snapshot_folder
, nvarchar @working_directory
, nvarchar @use_ftp
, tinyint @publication_type
, nvarchar @dts_package_name
, nvarchar @dts_package_password
, nvarchar @dts_package_location
, nvarchar @reserved
, nvarchar @offloadagent
, nvarchar @offloadserver
, nvarchar @job_name
, nvarchar @job_login)

MetaData:

   
--
-- Name: sp_addpullsubscription_agent
--
-- Descriptions:
--
-- Parameters: as defined in create statement
--
-- Returns: 0 - success
-- 1 - Otherwise
--
-- Security:
-- Requires Certificate signature for catalog access
--
create procedure sys.sp_addpullsubscription_agent
(
@publisher sysname,
@publisher_db sysname = NULL,
@publication sysname,
@subscriber sysname = NULL,
@subscriber_db sysname = NULL,
@subscriber_security_mode int = NULL, -- 0 standard; 1 integrated
@subscriber_login sysname = NULL,
@subscriber_password sysname = NULL,
@distributor sysname = @publisher,
@distribution_db sysname = NULL,
@distributor_security_mode int = 1,
@distributor_login sysname = NULL,
@distributor_password sysname = NULL,
@optional_command_line nvarchar(4000) = '',
@frequency_type int = 2, -- 2 == OnDemand
@frequency_interval int = 1,
@frequency_relative_interval int = 1,
@frequency_recurrence_factor int = 1,
@frequency_subday int = 1,
@frequency_subday_interval int = 1,
@active_start_time_of_day int = 0,
@active_end_time_of_day int = 0,
@active_start_date int = 0,
@active_end_date int = 0,
@distribution_jobid binary(16) = NULL OUTPUT,
@encrypted_distributor_password bit = 0,
@enabled_for_syncmgr nvarchar(5) = 'false', -- Enabled for SYNCMGR: true or false
@ftp_address sysname = NULL,
@ftp_port int = NULL,
@ftp_login sysname = NULL,
@ftp_password sysname = NULL,
@alt_snapshot_folder nvarchar(255) = NULL,
@working_directory nvarchar(255) = NULL,
@use_ftp nvarchar(5) = 'false',
@publication_type tinyint = 0, -- 0 - Transactional, 1 - Snapshot, 2 - Merge
@dts_package_name sysname = NULL, -- value will be sent and validated at distributor
@dts_package_password sysname = NULL,
@dts_package_location nvarchar(12) = N'subscriber',
@reserved nvarchar(100) = N'', -- Not default to null because null problems in conditional expressions.
@offloadagent nvarchar(5) = 'false',
@offloadserver sysname = NULL,
@job_name sysname = NULL,
-- used for jobstep level proxy accounts
@job_login nvarchar(257) = NULL,
@job_password sysname = NULL
)
AS
BEGIN
SET NOCOUNT ON

--
-- Declarations.
--

DECLARE @command nvarchar(4000)
,@retcode int
,@subscription_type_id int -- 1 = pull, 2 = anonymous
,@independent_agent_id bit
,@distribution_agent nvarchar(100)
,@category_name sysname
,@platform_nt binary
,@subscriber_enc_password nvarchar(524)
,@distributor_enc_password nvarchar(524)
,@use_ftp_bit bit
,@distribution_job_step_uid uniqueidentifier
,@edition_id int

select @platform_nt = 0x1
,@distribution_job_step_uid = NULL
,@edition_id = sys.fn_MSrepl_editionid ()

--
-- Security Check
--

EXEC @retcode = sys.sp_MSreplcheck_subscribe
IF @@ERROR <> 0 or @retcode <> 0
RETURN(1)

--
-- Initializations.
--

-- Set null @optional_command_line to empty string to avoid string concat problem
if @optional_command_line is null
set @optional_command_line = ''
else
set @optional_command_line = N' ' + LTRIM( RTRIM(@optional_command_line) ) + N' '


IF @distributor_password = N''
select @distributor_password = NULL

IF @ftp_password = N''
select @ftp_password = NULL

IF @dts_package_password = N''
select @dts_package_password = NULL

-- @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

IF RTRIM(ISNULL(@dts_package_name, N'')) != N''
AND RTRIM(ISNULL(@dts_package_password, N'')) = N''
BEGIN
-- Use of DTS packages in replication requires a non-NULL/non-empty string password. Specify a valid value for parameter '%s'.
RAISERROR(21732,16, -1, '@dts_package_password')
RETURN (1)
END

--
-- Parameter Check: @publisher
-- Check to make sure that the publisher is define
--
IF @publisher IS NULL
BEGIN
RAISERROR (14043, 16, -1, '@publisher', 'sp_addpullsubscription_agent')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @publisher

IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)

-- ensure that the distributor is set to a value...
-- we will default to publisher since this is sp default
SELECT @distributor = ISNULL(@distributor, @publisher)

--
-- 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_agent')
RETURN (1)
END

EXECUTE @retcode = sys.sp_validname @publication

IF @@ERROR <> 0 OR @retcode <> 0
RETURN (1)

--
-- Parameter Check: @subscriber and @subscriber_db
--

IF @subscriber IS NOT NULL
OR @subscriber_db IS NOT NULL
BEGIN
-- The @subscriber and @subscriber_db parameter(s) have been deprecated and should no longer be used. See the 'sp_addpullsubscription_agent' documentation for more information.
RAISERROR(21827, 10, -1, '@subscriber and @subscriber_db', 'sp_addpullsubscription_agent')
END

SELECT @subscriber = @@SERVERNAME,
@subscriber_db = DB_NAME()

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

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

--
-- Check to see if MSreplictaion_subscriptions table exists.
-- If so, copy it into the temp table
--
IF object_id('MSreplication_subscriptions', 'U') is NULL
BEGIN
RAISERROR (20017, 16, -1)
RETURN (1)
END

--
-- Check to make sure that the subscription does exist
--
IF NOT EXISTS (SELECT * FROM MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher) AND
publisher_db = @publisher_db AND
publication = @publication)
BEGIN
RAISERROR (20017, 16, -1)
RETURN (1)
END

declare @update_mode_id int
SELECT @distribution_agent = NULL
SELECT @independent_agent_id = independent_agent,
@subscription_type_id = subscription_type,
@distribution_agent = distribution_agent,
@update_mode_id = update_mode
FROM MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher) AND
publisher_db = @publisher_db AND
publication = @publication
-- Distribution agent for push subscriptions is at distributor side --
IF @subscription_type_id = 0
BEGIN
RAISERROR (21001, 16, -1)
RETURN (1)
END

IF @distribution_agent IS NOT NULL
BEGIN
RAISERROR (21002, 11, -1, @distribution_agent)
RETURN (1)
END

-- Parameter check: @subscriber_security_mode, @subscriber_login, @subscriber_password
IF (@subscriber_security_mode IS NOT NULL
AND @subscriber_security_mode <> 1)
OR @subscriber_login IS NOT NULL
OR @subscriber_password IS NOT NULL
BEGIN
-- The @subscriber_security_mode, @subscriber_login and @subscriber_password parameter(s) have been deprecated and should no longer be used. See the 'sp_addpullsubscription_agent' documentation for more information.
RAISERROR(21827, 10, -1, '@subscriber_security_mode, @subscriber_login and @subscriber_password', 'sp_addpullsubscription_agent')
END

-- if we do not require yukon security then check paramters.
IF sys.fn_yukonsecuritymodelrequired(NULL) = 0
BEGIN
IF @job_login IS NULL
BEGIN
IF @job_password IS NOT NULL
BEGIN
-- Parameter '@job_login' can be set to 'NULL' only when '@job_password' is set to 'NULL'.
RAISERROR(21678, 16, -1, '@job_login', 'NULL', '@job_password', 'NULL')
RETURN 1
END
END
ELSE
BEGIN
IF @job_password IS NULL
BEGIN
-- Parameter '@job_password' can be set to 'NULL' only when '@job_login' is set to 'NULL'.
RAISERROR(21678, 16, -1, '@job_password', 'NULL', '@job_login', 'NULL')
RETURN 1
END

IF @edition_id <> 40 and @edition_id <> 22
AND sys.fn_replisvalidwindowsloginformat(@job_login) != 1
BEGIN
-- '@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addpullsubscription_agent'.
RAISERROR(21797, 16, -1, '@job_login', 'sp_addpullsubscription_agent')
RETURN 1
END
END
END
-- if yukon sec required then a valid windows login/password is required
ELSE
BEGIN
IF @job_login IS NULL
OR @job_password IS NULL
BEGIN
-- Only members of the sysadmin fixed server role can perform this operation without specifying @job_login or @job_password.
RAISERROR(21832, 16, -1, '@job_login or @job_password')
RETURN 1
END

IF @edition_id <> 40 and @edition_id <> 22
AND sys.fn_replisvalidwindowsloginformat(@job_login) != 1
BEGIN
-- '@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_addpullsubscription_agent'.
RAISERROR(21797, 16, -1, '@job_login', 'sp_addpullsubscription_agent')
RETURN 1
END
END


-- Subscriber must be Windows Authentication
SELECT @subscriber_security_mode = 1,
@subscriber_login = N'',
@subscriber_password = N'-- -- -- -- -- ',
@subscriber_enc_password = @subscriber_password

EXEC @retcode = sys.sp_MSreplencrypt @subscriber_enc_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1

-- default to Windows Auth.
SELECT @distributor_security_mode = ISNULL(@distributor_security_mode, 1)

-- check security mode
IF @distributor_security_mode not in (0, 1)
BEGIN
-- The specified '@distributor_security_mode' is invalid (valid values are: 0, 1).
RAISERROR(14266, 16, -1, '@distributor_security_mode', '0, 1')
RETURN 1
END

-- make sure that the login is valid
SELECT @distributor_login = RTRIM(LTRIM(ISNULL(@distributor_login, '')))

-- Security Mode = 1
IF @distributor_security_mode = 1
BEGIN
SELECT @distributor_login = '',
@distributor_password = newid()
END
-- Security Mode = 0
ELSE IF @distributor_login = ''
BEGIN

-- '@distributor_login cannot be null or empty when @distributor_security_mode is set to 0 (SQL Server authentication).'
RAISERROR(21694, 16, -1, '@distributor_login', '@distributor_security_mode')
RETURN 1
END

SELECT @distributor_enc_password = @distributor_password

EXEC @retcode = sys.sp_MSreplencrypt @distributor_enc_password OUTPUT
IF @@ERROR <> 0 OR @retcode <> 0
RETURN 1

IF object_id('MSsubscription_properties','U') is NULL
begin
exec @retcode = sys.sp_MScreate_sub_tables_internal @property_table = 1
if @retcode <> 0 or @@error <> 0
return (1)
end

--
-- Parameter check: @alt_snapshot_folder
-- @alt_snapshot_folder and @use_ftp are mutually exclusive
--

IF @alt_snapshot_folder <> N'' AND @alt_snapshot_folder IS NOT NULL
AND LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = N'true'
BEGIN
RAISERROR(21146, 16, -1)
RETURN (1)
END

--
-- Parameter check: @use_ftp
-- Must be 'true' or 'false'
--
IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
BEGIN
RAISERROR (14148, 16, -1, '@use_ftp')
RETURN (1)
END

IF LOWER(@use_ftp collate SQL_Latin1_General_CP1_CS_AS) = 'true'
BEGIN
SELECT @use_ftp_bit = 1
END
ELSE
BEGIN
SELECT @use_ftp_bit = 0
END


--
-- Parameter check: @publication_type
-- Must be 0 - Transactional or 1 - Snapshot
--
IF @publication_type NOT IN (0, 1)
BEGIN
RAISERROR (20033, 16, -1)
RETURN (1)
END

--
-- 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

-- Have to be a push, non updatable subscription to set DTS package name
if @dts_package_name is not null
begin
if @update_mode_id != 0
begin
RAISERROR(21180, 16, -1)
RETURN (1)
end
end

-- Copy the passwords to new value before attempting to encrypt
-- We no longer supported passing in encrypted passwords
IF @encrypted_distributor_password = 1
BEGIN
-- Parameter '@encrypted_distributor_password' is no longer supported.
RAISERROR(21698, 16, -1, '@encrypted_distributor_password')
RETURN (1)
END

declare @dts_package_enc_password nvarchar(524)
set @dts_package_enc_password = @dts_package_password

if @dts_package_enc_password is not null
begin
EXEC @retcode = sys.sp_MSreplencrypt @dts_package_enc_password OUTPUT
IF @@error <> 0 OR @retcode <> 0
return 1
end

--
-- Parameter Check: @offloadserver
--
IF @offloadagent IS NOT NULL
AND RTRIM(LTRIM(LOWER(@offloadagent))) != 'false'
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

--
-- Construct unique name
--
if @subscriber is NULL select @subscriber = ''
if @subscriber_db is NULL select @subscriber_db = ''

declare @job_existing bit
if @job_name is null
begin
select @job_existing = 0
SELECT @job_name = CONVERT(nvarchar(18),@publisher ) + '-' +
CASE @publisher_db
WHEN NULL THEN ''
ELSE CONVERT(nvarchar(18),@publisher_db) + '-'
END +
CONVERT(nvarchar(18),@publication) + '-' + CONVERT(nvarchar(18),@subscriber) + '-' +
CONVERT(nvarchar(18),@subscriber_db) + '-' + CONVERT(nvarchar(36),newid())
end
else
select @job_existing = 1

BEGIN TRAN
save tran sp_addpullsubagent

--
-- If the publication is independent agent type or it is the first
-- subscription on the non independent agent publications.
--

IF @independent_agent_id = 1 OR
NOT EXISTS (SELECT * FROM MSreplication_subscriptions WHERE
UPPER(@publisher) = UPPER(publisher) and
@publisher_db = publisher_db and
agent_id IS NOT NULL and
independent_agent = 0)
BEGIN
if @job_existing = 0
begin
-- Construct agent command --
SELECT @command = '-Publisher ' + @publisher + ' '

IF @publisher_db IS NOT NULL
SELECT @command = @command + '-PublisherDB ' + QUOTENAME(@publisher_db) + ' '

IF @independent_agent_id = 1
SELECT @command = @command + '-Publication ' + QUOTENAME(@publication) + ' '

SELECT @command = @command + '-Distributor ' + QUOTENAME(@distributor) + ' '

SELECT @command = @command + '-SubscriptionType ' + convert(nvarchar(10),@subscription_type_id) + ' '

SELECT @command = @command + '-Subscriber ' + QUOTENAME(@subscriber) + ' '

-- Always use integrated for local security
select @command = @command + '-SubscriberSecurityMode 1 '

SELECT @command = @command + '-SubscriberDB ' + QUOTENAME(@subscriber_db) + ' '

if @dts_package_name is not null
select @command = @command + '-UseDTS '

--
-- make sure the command line is not truncated
--
-- Use datalength because len doesn't count the last space in @command --
IF (datalength(@command) + datalength(@optional_command_line)) > 8000
BEGIN
RAISERROR(20018, 16, -1)
goto cleanup
END

SELECT @command = @command + @optional_command_line

-- Get Distribution category name (assumes category_id = 10)
select @category_name = name FROM msdb.dbo.syscategories where category_id = 10

EXEC @retcode = sys.sp_MSadd_repl_job
@name = @job_name,
@subsystem = 'Distribution',
@server = @@SERVERNAME,
@databasename = @subscriber_db,
@enabled = 1,
@freqtype = @frequency_type,
@freqinterval = @frequency_interval,
@freqsubtype = @frequency_subday,
@freqsubinterval = @frequency_subday_interval,
@freqrelativeinterval = @frequency_relative_interval,
@freqrecurrencefactor = @frequency_recurrence_factor,
@activestartdate = @active_start_date,
@activeenddate = @active_end_date,
@activestarttimeofday = @active_start_time_of_day,
@activeendtimeofday = @active_end_time_of_day,
@command = @command,
@category_name = @category_name,
@retryattempts = 10,
@retrydelay = 1,
@job_id = @distribution_jobid OUTPUT,
@job_step_uid = @distribution_job_step_uid OUTPUT,
@job_login = @job_login,
@job_password = @job_password

IF @@ERROR <> 0 or @retcode <> 0
goto cleanup
end
else
begin
SELECT @distribution_jobid = sjv.job_id,
@distribution_job_step_uid = sjs.step_uid
FROM msdb.dbo.sysjobs_view as sjv
JOIN msdb.dbo.sysjobsteps as sjs
ON sjv.job_id = sjs.job_id
WHERE sjv.name = @job_name collate database_default
AND UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
AND sjv.master_server = 0
AND sjv.category_id = 10
AND sjs.subsystem = N'Distribution'
AND sjs.database_name = db_name()

if @distribution_jobid IS NULL
begin
-- Message from msdb.dbo.sp_verify_job_identifiers
RAISERROR(14262, -1, -1, 'Job', @job_name)
goto cleanup
end
end
END

-- If we do not have independent agents , i.e. independent_agent=0, but there is
already a row for that publisher and that publisher database with a NOT null
distribution_agent_id, then set the @distribution_jobid to that id. Note that if
there are no rows returned, the value of the variable does not change, which is what we want.
There should never be more than one row ever returned for this query - but will use TOP 1
to insist that is the case.
--

IF @independent_agent_id = 0
BEGIN
SELECT DISTINCT @distribution_jobid=agent_id, @job_name = distribution_agent
FROM MSreplication_subscriptions
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND agent_id IS NOT NULL AND independent_agent=0
END

UPDATE MSreplication_subscriptions
SET distribution_agent = @job_name,
agent_id = @distribution_jobid
WHERE UPPER(publisher) = UPPER(@publisher)
AND publisher_db = @publisher_db
AND publication = @publication
AND (subscription_type = 1 -- pull -- OR subscription_type = 2) -- anonymous --

IF @@ERROR <> 0
goto cleanup

if (@subscription_type_id = 1) OR (@subscription_type_id = 2)
BEGIN
-- if the job step uid is null then we will
-- attempt to retrieve it based on job info
if @distribution_job_step_uid IS NULL
begin
select @distribution_job_step_uid = sjs.step_uid
from msdb.dbo.sysjobs_view as sjv
join msdb.dbo.sysjobsteps as sjs
on sjv.job_id = sjs.job_id
where sjv.job_id = @distribution_jobid
and sjv.master_server = 0
and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
and sjv.category_id = 10
and sjs.subsystem = N'Distribution'
and sjs.database_name = db_name()
end

IF NOT EXISTS (select * from MSsubscription_properties
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication)
BEGIN
-- Publication type:
-- 0 transactional
-- 1 snapshot
-- 2 merge (not allowed)

INSERT INTO MSsubscription_properties
(publisher, publisher_db, publication, publication_type,
publisher_login,publisher_password, publisher_security_mode,
distributor, distributor_login, distributor_password,
distributor_security_mode, ftp_address, ftp_port, ftp_login,
ftp_password, alt_snapshot_folder, working_directory, use_ftp,
dts_package_name, dts_package_password, dts_package_location,
offload_agent, offload_server, dynamic_snapshot_location, job_step_uid)
values (@publisher, @publisher_db, @publication, @publication_type, NULL, NULL, -1,
@distributor, @distributor_login, @distributor_enc_password,
@distributor_security_mode, null, null, null,
null, @alt_snapshot_folder, @working_directory, @use_ftp_bit,
@dts_package_name, @dts_package_enc_password,
@dts_package_location_id, 0, null, null, @distribution_job_step_uid)
END
ELSE
BEGIN
update MSsubscription_properties set
distributor = @distributor,
distributor_login = @distributor_login,
distributor_password = @distributor_enc_password,
distributor_security_mode = @distributor_security_mode,
dts_package_name = @dts_package_name,
dts_package_password = @dts_package_enc_password,
dts_package_location = @dts_package_location_id,
job_step_uid = @distribution_job_step_uid
where UPPER(publisher) = UPPER(@publisher)
and publisher_db = @publisher_db
and publication = @publication
END
IF @@ERROR <> 0
goto cleanup


-- For dependent subscriptions we need to fix up all the
-- shared properties
IF @independent_agent_id = 0
BEGIN

EXEC @retcode = sys.sp_MSfixupsharedagentproperties
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@distributor = @distributor,
@distributor_security_mode = @distributor_security_mode,
@distributor_login = @distributor_login,
@distributor_password = @distributor_enc_password,
@ftp_address = @ftp_address,
@ftp_port = @ftp_port,
@ftp_login = @ftp_login,
@ftp_password = @ftp_password,
@alt_snapshot_folder = @alt_snapshot_folder,
@working_directory = @working_directory,
@use_ftp = @use_ftp_bit

IF @retcode <> 0 OR @@ERROR <> 0
goto cleanup
END

IF @@ERROR <> 0
goto cleanup
END

-- Conditional support for MobileSync --
if LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'
BEGIN
-- Call sp_MSregistersubscription so that the subscription can be synchronized via MobileSync etc. --
declare @subscription_id uniqueidentifier
declare @failover_mode_id int
set @subscription_id = convert(uniqueidentifier, @distribution_jobid)

if @update_mode_id in (3,5)
select @failover_mode_id = 1
else if @update_mode_id in (2,4)
select @failover_mode_id = 2
else
select @failover_mode_id = 0

exec @retcode = sys.sp_MSregistersubscription @replication_type = 1,
@publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@subscriber = @subscriber,
@subscriber_db = @subscriber_db,
@subscriber_security_mode = @subscriber_security_mode,
@subscriber_login = @subscriber_login,
@subscriber_password = @subscriber_password,
@distributor = @distributor,
@subscription_id = @subscription_id,
@independent_agent = @independent_agent_id,
@subscription_type = @subscription_type_id,
@failover_mode = @failover_mode_id
IF @@ERROR <> 0 or @retcode <> 0
goto cleanup
END

-- If we didn't need to create the job but
-- we were given the job_login and job_password
-- then we will attempt to change them or add
-- NOTE:
-- We do this at the very end because the actual
-- agent must be added prior to setting the login
-- and passwords...
IF @job_existing = 1
BEGIN
IF @job_login IS NOT NULL
BEGIN
EXEC @retcode = sys.sp_change_subscription_properties @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@property = 'distrib_job_login',
@value = @job_login
IF @retcode <> 0 OR @@ERROR <> 0
GOTO cleanup

EXEC @retcode = sys.sp_change_subscription_properties @publisher = @publisher,
@publisher_db = @publisher_db,
@publication = @publication,
@property = 'distrib_job_password',
@value = @job_password
IF @retcode <> 0 OR @@ERROR <> 0
GOTO cleanup
END
END

-- success
COMMIT TRAN
RETURN(0)
-- error cleanup
cleanup:
ROLLBACK TRAN sp_addpullsubagent
COMMIT TRAN
RETURN(1)
END

Total Pageviews