Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_changesubscription

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepl_changesubscription
(
    @publication 	sysname,
    @article		sysname,
    @subscriber 	sysname,
    @destination_db	sysname,
    @property 		nvarchar(30),
    @value 			nvarchar(4000),
    @publisher		sysname,
    @publisher_type	sysname
)
AS
BEGIN
	SET NOCOUNT ON
	
	DECLARE @SUBSCRIBER_BIT		int
	
	DECLARE @retcode 				int,
			@publisher_db			sysname,
			@pubid					int,
			@artid					int,
			@subscription_type_id	int,
			@update_mode_id			int,
			@distproc 				nvarchar(1000),
			@distributor 			sysname,
			@distribution_db 		sysname,
			@options				int,
			@OPT_ENABLED_FOR_HET_SUB int,
			@odbc_subscriber		tinyint,
			@oledb_subscriber		tinyint,
			@independent_agent		bit,
			@distagent_exists		bit,
			@proxy_id				int
			
	SELECT	@odbc_subscriber = 1
	SELECT	@oledb_subscriber = 3

	SELECT @SUBSCRIBER_BIT = 4
	SELECT @OPT_ENABLED_FOR_HET_SUB = 0x4

	SELECT @publisher_db = DB_NAME()
				,@property = LOWER(@property collate SQL_Latin1_General_CP1_CS_AS)
			
	-- Security Check
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
    BEGIN
        RETURN 1
    END

    -- Check to see if the database has been activated for publication.
    IF sys.fn_MSrepl_istranpublished(@publisher_db,1) <> 1
    BEGIN
        -- "This database is not enabled for publication."
        RAISERROR (14013, 16, -1)
        RETURN 1
    END

	-- Parameter Check:  @publication.
    IF @publication IS NULL
    BEGIN
    	-- "The parameter @publication cannot be NULL."
        RAISERROR (14043, 16, -1, '@publication', 'sp_MSrepl_changesubscription')
        RETURN 1
    END

    EXEC @retcode = sys.sp_validname @publication
    IF @@ERROR <> 0 OR @retcode <> 0
    	RETURN 1

    -- Parameter Check : @publisher
    IF @publisher IS NULL
    BEGIN
		select @publisher = publishingservername()
    END

	-- Get publication id
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

	IF (@pubid IS NULL)
	BEGIN
		-- "The publication '@publication' does not exist."
	    RAISERROR (20026, 16, -1, @publication)
	   	RETURN 1
	END

	-- retrieve the independent agent setting
	SELECT @independent_agent = independent_agent
		FROM syspublications
		WHERE pubid = @pubid
	
	-- check to see if the subscription exists
	IF NOT EXISTS
	(
		SELECT	ss.*
		FROM	syspublications sp,
				sysextendedarticlesview seav,
				syssubscriptions ss
		WHERE	ss.srvname = UPPER(@subscriber)
			and (ss.srvname is not null and len(ss.srvname)> 0)
			AND	ss.dest_db = @destination_db
		  AND	(seav.name = @article OR lower(@article) = 'all')
		  AND	sp.pubid = @pubid
		  AND	sp.pubid = seav.pubid
		  AND	ss.artid = seav.artid
	)
	BEGIN
		-- "The subscription could not be found."
		RAISERROR (20021, 16, -1)
		RETURN 1
	END

    -- Parameter Check:  @property.
	IF @property IS NULL
		OR @property NOT IN ('subscriber_security_mode',
								'subscriber_login',
								'subscriber_password',
								'subscriptionstreams',
								'distrib_job_login',
								'distrib_job_password',
								'subscriber_type',
								'subscriber_provider',
								'subscriber_datasource',
								'subscriber_providerstring',
								'subscriber_location',
								'subscriber_catalog')
    BEGIN
    	-- Invalid property name '@property'.
    	SELECT @property = isnull(@property, 'NULL')
    	
        RAISERROR (21183, 16, -1, @property)
        RETURN 1
    END
	
	-- retrieve the subscription type
	SELECT	@subscription_type_id = ss.subscription_type,
			@update_mode_id = ss.update_mode,
			@options = sp.options
	FROM	syspublications sp,
			sysextendedarticlesview seav,
			syssubscriptions ss
	WHERE	ss.srvname = UPPER(@subscriber)
		and (ss.srvname is not null and len(ss.srvname)> 0)
		AND	ss.dest_db = @destination_db
	  AND	(seav.name = @article OR lower(@article) = 'all')
	  AND	sp.pubid = @pubid
	  AND	sp.pubid = seav.pubid
	  AND	ss.artid = seav.artid
	
	-- retrieve distributor rpc info
    EXEC @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor OUTPUT,
    										@distribdb = @distribution_db OUTPUT,
    										@publisher = @publisher
	IF @@ERROR <> 0 or @retcode <> 0 or @distributor is NULL
	BEGIN
		-- "The Distributor has not been installed correctly."
		RAISERROR (20036, 16, -1)
		RETURN 1
	END

	-- we only need to check if we have enough permissions to modify
	-- subscription properties for push cases since well known pull
	-- subscriptions will never have jobs at the distributor...
	IF @subscription_type_id = 0
	BEGIN
		-- make sure we have permission to perform this action
		SELECT @distproc =  QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSreplagentjobexists'
		EXEC @retcode = @distproc @type = 0,
									@exists = @distagent_exists output,
									@proxy_id = @proxy_id output,
									@publisher = @publisher,
									@publisher_db = @publisher_db,
									@publication = @publication,
									@subscriber = @subscriber,
									@subscriber_db = @destination_db,
									@independent_agent = @independent_agent				
		IF @retcode <> 0 OR @@ERROR <> 0
			RETURN 1

		IF IS_SRVROLEMEMBER('sysadmin') != 1
			AND (@distagent_exists = 0
				OR @proxy_id IS NULL)
		BEGIN
			-- Only members of the sysadmin fixed server role can modify a subscription that does not have a job with a proxy account defined.
			RAISERROR(20813, 16, -1, 'subscription')
			RETURN 1
		END
	END
	
	BEGIN TRANSACTION tran_sp_changesubscription
	SAVE TRANSACTION tran_sp_changesubscription

    -- Begin Making Changes
	IF @property in ('subscriber_security_mode', 'subscriber_login', 'subscriber_password')
	BEGIN
		-- Param check for @subscriber_... settings.
		-- These are not valid for pull subscriptions unless Queued is involved
		IF @subscription_type_id = 1 AND @update_mode_id in (0, 1)
		BEGIN
			-- "The property '@property' is only valid for push subscriptions and pull subscriptions involved in Queued."
			RAISERROR (21819, 16, -1, @property)
			goto UNDO
		END
		
		-- these properties require that the article be set to 'all'
		IF lower(@article) <> N'all' or @article IS NULL
		BEGIN
			-- "An invalid value was specified for parameter '@article'. The value must be 'all' when changing this property."
			RAISERROR (21816, 16, -1, '@article', 'all')
			goto UNDO
		END

		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSchange_distribution_agent_properties'	
		EXEC @retcode = @distproc @publisher		= @publisher,
									@publisher_db	= @publisher_db,
									@publication 	= @publication,
									@subscriber 	= @subscriber,
									@subscriber_db	= @destination_db,
									@property		= @property,
									@value			= @value
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			goto UNDO
		END
	END

	IF @property in ('distrib_job_login', 'distrib_job_password')
	BEGIN
		-- Param check for @subscriber_... settings.
		-- These are not valid for pull subscriptions
		IF @subscription_type_id != 0
		BEGIN
			-- "The property '@property' is only valid for push subscriptions. Use 'sp_change_subscription_properties' for pull subscriptions."
			RAISERROR (21826, 16, -1, @property, 'push', 'sp_change_subscription_properties', 'pull')
			goto UNDO
		END
		
		-- these properties require that the article be set to 'all'
		IF lower(@article) <> N'all' or @article IS NULL
		BEGIN
			-- "An invalid value was specified for parameter '@article'. The value must be 'all' when changing this property."
			RAISERROR (21816, 16, -1, '@article', 'all')
			goto UNDO
		END
		
		-- Parameter Check: @distrib_job_login
		IF @property in ('distrib_job_login')
		BEGIN
			IF sys.fn_replisvalidwindowsloginformat(@value) != 1
			BEGIN
				-- '@job_login' must be a valid Windows Login in the form : 'MACHINE\Login' or 'DOMAIN\Login'. Please see the documentation for 'sp_changesubscription'.
				RAISERROR(21797, 16, -1, '@value (distrib_job_login)', 'sp_changesubscription')
				goto UNDO
			END
		END
		
		IF @property in ('distrib_job_password')
		BEGIN
			-- Parameter Check: distrib_job_password
			IF @value IS NULL
			BEGIN
				-- The parameter @value (distrib_job_password) cannot be NULL.
				RAISERROR(14043, 16, -1, '@value (distrib_job_password)', 'sp_MSrepl_changesubscription')
				goto UNDO
			END
		END

		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSchange_distribution_agent_properties'	
		EXEC @retcode = @distproc @publisher		= @publisher,
									@publisher_db	= @publisher_db,
									@publication 	= @publication,
									@subscriber 	= @subscriber,
									@subscriber_db	= @destination_db,
									@property		= @property,
									@value			= @value
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			goto UNDO
		END
	END
	
	IF @property in ('subscriber_type', 'subscriber_provider', 'subscriber_datasource', 'subscriber_providerstring', 'subscriber_location', 'subscriber_catalog')
	BEGIN
		-- Param check for @subscriber_... settings.
		-- These are not valid for pull subscriptions
		IF @subscription_type_id != 0
		BEGIN
			-- "The property '@property' is only valid for push subscriptions. Use 'sp_change_subscription_properties' for pull subscriptions."
			RAISERROR (21826, 16, -1, @property, 'push', 'sp_change_subscription_properties', 'pull')
			goto UNDO
		END
		
		-- these properties require that the article be set to 'all'
		IF lower(@article) <> N'all' or @article IS NULL
		BEGIN
			-- "An invalid value was specified for parameter '@article'. The value must be 'all' when changing this property."
			RAISERROR (21816, 16, -1, '@article', 'all')
			goto UNDO
		END
		
		-- Parameter Check: subscriber_type
		IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('subscriber_type')
		BEGIN
			IF NOT ((@options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
			BEGIN
				-- The 'subscriber_type' property can only be changed if the publication is enabled for heterogeneous subscriptions.  The publication is not enabled.
				RAISERROR(14041, 16, -1, @property)
				goto UNDO
			END
		
			IF @value IS NULL OR @value NOT IN ( @odbc_subscriber, @oledb_subscriber )
			BEGIN
				-- The value of property 'subscriber_type' is not a supported heterogeneous subscriber type.  The value must be 1 (ODBC subscriber), or 3 (OLEDB subscriber).
				RAISERROR(14026, 16, -1)
				goto UNDO
			END
		END
		
		-- Parameter Check: subscriber_provider
		IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) in ('subscriber_provider')
		BEGIN
			IF NOT ((@options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
			BEGIN
				-- The 'subscriber_provider' property can only be changed if the publication is enabled for heterogeneous subscriptions.  The publication is not enabled.
				RAISERROR(14041, 16, -1, @property)
				goto UNDO
			END
		
			IF @value IS NULL
			BEGIN
				-- The value of property 'subscriber_provider' cannot be NULL.
				RAISERROR(14024, 16, -1)
				goto UNDO
			END
		END
		
		-- Parameter Check: subscriber_datasource, subscriber_providerstring,
		-- subscriber_location, and subscriber_catalog
		IF LOWER(@property collate SQL_Latin1_General_CP1_CS_AS) IN ('subscriber_datasource', 'subscriber_providerstring', 'subscriber_location', 'subscriber_catalog')
		BEGIN
			IF NOT ((@options & @OPT_ENABLED_FOR_HET_SUB) = @OPT_ENABLED_FOR_HET_SUB)
			BEGIN
				-- The '%s' property can only be changed if the publication is enabled for heterogeneous subscriptions.  The publication is not enabled.
				RAISERROR(14041, 16, -1, @property)
				goto UNDO
			END
		END

		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSchange_distribution_agent_properties'	
		EXEC @retcode = @distproc @publisher		= @publisher,
									@publisher_db	= @publisher_db,
									@publication 	= @publication,
									@subscriber 	= @subscriber,
									@subscriber_db	= @destination_db,
									@property		= @property,
									@value			= @value
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			goto UNDO
		END
	END

	IF @property in ('subscriptionstreams')
	BEGIN
		-- these properties require that the article be set to 'all'
		IF lower(@article) <> N'all' or @article IS NULL
		BEGIN
			-- "An invalid value was specified for parameter '@article'. The value must be 'all' when changing this property."
			RAISERROR (21816, 16, -1, '@article', 'all')
			goto UNDO
		END
		
		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSchange_distribution_agent_properties'	
		EXEC @retcode = @distproc @publisher		= @publisher,
									@publisher_db	= @publisher_db,
									@publication 	= @publication,
									@subscriber 	= @subscriber,
									@subscriber_db	= @destination_db,
									@property		= @property,
									@value			= @value
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
			goto UNDO
		END
	END
	
	COMMIT TRANSACTION tran_sp_changesubscription

	
	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tran_sp_changesubscription
	COMMIT TRANSACTION

	RETURN 1
END

 
Last revision 2008RTM
See also

  sp_changesubscription (Procedure)
sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash