-- Name: sp_changesubscriptiondtsinfo
-- Descriptions:
-- Parameters: as defined in create statement
-- Returns: 0 - success
-- 1 - Otherwise
-- Security:
-- Requires Certificate signature for catalog access
create procedure sys.sp_changesubscriptiondtsinfo (
@job_id varbinary(16),
@dts_package_name sysname = NULL,
@dts_package_password sysname = NULL,
@dts_package_location nvarchar(12) = NULL
) AS
/*
** Declarations.
*/
DECLARE @srvid smallint
DECLARE @artid int
DECLARE @retcode int
declare @login_name sysname
declare @update_mode int
declare @subscription_type int
declare @dts_package_location_id int
/*
** Initializations.
*/
SET NOCOUNT ON
/*
** Get subscription properties and do Security Check.
** We use login_name stored in syssubscriptions to manage security
*/
select @update_mode = update_mode, @login_name = login_name,
@subscription_type = subscription_type,
@artid = artid
FROM syssubscriptions s WHERE
s.distribution_jobid = @job_id
/*
** Check if the subscription exists.
*/
IF @update_mode is null
BEGIN
RAISERROR (14055, 11, -1)
RETURN (1)
END
if @update_mode != 0
begin
RAISERROR(21180, 16, -1)
RETURN (1)
end
-- Only push store DTS info at distributor.
if @subscription_type <> 0
begin
RAISERROR(21181, 16, -1)
RETURN (1)
end
--Security check
IF suser_sname(suser_sid()) <> @login_name AND is_srvrolemember('sysadmin') <> 1
AND is_member ('db_owner') <> 1
BEGIN
-- Only members of the sysadmin fixed server role, db_owner fixed database role or the creator of the subscription can change this subscription property.'
RAISERROR(21175, 11, -1)
RETURN (1)
END
-- Get pubid
declare @pubid int
select @pubid = pubid from sysarticles where artid = @artid
/* Get subscription type of the publication */
if not exists (select * from syspublications where
pubid = @pubid and
allow_dts = 1)
begin
RAISERROR(21178, 16, -1)
RETURN (1)
end
if @dts_package_location is null
select @dts_package_location_id = null
else IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = N'distributor'
select @dts_package_location_id = 0
ELSE IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = N'subscriber'
select @dts_package_location_id = 1
ELSE
begin
raiserror(20587, 16, -1, '@dts_package_location', 'sp_changesubscriptiondtsinfo')
return(1)
end
-- Encrypt DTS package password
declare @change_password bit
if @dts_package_password is null
select @change_password = 0
else
begin
IF RTRIM(@dts_package_password) = 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
select @change_password = 1
end
-- When user sends in empty string, reset it to null.
-- Have to do this before scramble because the result may contains invalid
-- unicode code points which are equael to some collation.
if @dts_package_password = N''
select @dts_package_password = NULL
declare @enc_dts_package_password nvarchar(524)
select @enc_dts_package_password = @dts_package_password
if @enc_dts_package_password is not null
begin
EXEC @retcode = sys.sp_MSreplencrypt @enc_dts_package_password OUTPUT
IF @@error <> 0 OR @retcode <> 0
return 1
end
/*
** Get distribution server information for remote RPC
** agent verification.
*/
declare @distributor sysname
declare @distribdb sysname
declare @distproc nvarchar(1000)
EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @rpcsrvname = @distributor OUTPUT,
@distribdb = @distribdb OUTPUT
IF @@error <> 0 OR @retcode <> 0
BEGIN
RAISERROR (14071, 16, -1)
RETURN (1)
END
/*
** Call proc to change the distributor
*/
SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(@distribdb) +
'.dbo.sp_MSchange_subscription_dts_info'
exec @retcode = @distproc
@job_id = @job_id,
@dts_package_name = @dts_package_name,
@dts_package_password = @enc_dts_package_password,
@dts_package_location = @dts_package_location_id,
@change_password = @change_password
IF @@error <> 0 OR @retcode <> 0
RETURN (1)
RETURN (0)