Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addpushsubscription_agent

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSrepl_addpushsubscription_agent
(
	@publication					sysname,
	@subscriber 					sysname,
	@subscriber_db					sysname,
	@subscriber_security_mode		smallint,
	@subscriber_login				sysname,
	@subscriber_password			sysname,
	@job_login						nvarchar(257),
	@job_password					sysname,
	@job_name						sysname,
	@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,
	@dts_package_name				sysname,
	@dts_package_password			sysname,
	@dts_package_location			nvarchar(12),
	@enabled_for_syncmgr			nvarchar(5),
	@distribution_job_name			sysname,
	@publisher						sysname,
	@publisher_type 				sysname,
	@subscriber_provider			sysname,
	@subscriber_datasrc				nvarchar(4000),
	@subscriber_location			nvarchar(4000),
	@subscriber_provider_string		nvarchar(4000),
	@subscriber_catalog				sysname
)
AS
BEGIN
	SET NOCOUNT ON

    DECLARE @retcode                        int,
            @procedure                      nvarchar(4000),
            @distributor                    sysname,
            @distributor_rpc                sysname,
            @distribution_db                sysname,
            @publisher_db                   sysname,
            @pubid                          int,
            @publication_name               sysname,
            @distagent_exists               bit,
            @distrib_job_name               sysname,
            @proxy_id                       int,
            @dsn_dbname                     sysname,
            @subscriber_type                int,
            @dist_publisher_id              int,
            @dist_subscriber_id             int,
            @subscriber_id                  int,
            @dts_location                   int,
            @allow_dts                      int,
            @sync_method                    int,
            @independent_agent              int,
            @subscription_id                uniqueidentifier,
            @non_sql_subscriber             int,
            @distribution_jobid             varbinary(16),
            @CHAR_BCP                       int,
            @CONCURRENT_CHAR                int,
            @DATABASE_SNAPSHOT_CHARACTER     int,
            @SUBSCRIBER_BIT                 smallint,
            @publisher_local                sysname
			
	SELECT @SUBSCRIBER_BIT = 4,
			@CHAR_BCP = 1,
			@CONCURRENT_CHAR = 4,
			@DATABASE_SNAPSHOT_CHARACTER = 6,
			@publisher_db = DB_NAME()
	
	-- Security Check
	EXEC @retcode = sys.sp_MSreplcheck_publish
	IF @@ERROR <> 0 or @retcode <> 0
		RETURN 1

	-- Check to see if database is activated for publication
	IF sys.fn_MSrepl_istranpublished(DB_NAME(),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_addpushsubscription_agent')
		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

    
    -- Verify publication exists
    
	SELECT @pubid = sys.fn_MSrepl_getpubid(@publication, @publisher, @publisher_type)

	IF (@pubid IS NULL)
	BEGIN
		RAISERROR (20026, 11, -1, @publication)
		RETURN (1)
	END

	-- Retrieve publication options	
	SELECT	@allow_dts         = allow_dts,
			@sync_method       = sync_method,
			@independent_agent = independent_agent
	FROM	syspublications
	WHERE	pubid = @pubid
		
	-- Parameter Check:  check subscriber server
	--leave this query the way is, for now, syspublications.options setting is too drastice for this one
	--if user need the nonsqlsub agent parameter, they can call sp_addsubscriber... , otherwise no need to error out
	SELECT @subscriber_id = srvid,
			@non_sql_subscriber = nonsqlsub
		FROM master.dbo.sysservers
		WHERE UPPER(srvname collate database_default) = UPPER(@subscriber) collate database_default

	IF LOWER(@subscriber_db) IS NULL
	BEGIN
		IF @non_sql_subscriber = 1
			SELECT @subscriber_db = formatmessage(20586)
		ELSE
			SELECT @subscriber_db = DB_NAME()
	END

	-- @subscriber_db cannot be all or master
	IF LOWER(@subscriber_db) = 'all'
	BEGIN
		-- "The value of parameter @subscriber_db cannot be 'all'. It is reserved by replication stored procedures."
	    RAISERROR (14032, 16, -1, '@subscriber_db')
	    RETURN 1
	END

	IF LOWER(@subscriber_db) = 'master'
	BEGIN
		-- "Cannot create replication subscription(s) in the master database. Choose another database for creating subscriptions."
	    RAISERROR (21481, 16, 1)
	    RETURN 1
	END

	EXEC @retcode = sys.sp_validname @subscriber_db
	IF @retcode <> 0
		RETURN 1

	-- Check Subscriber Security information
	SELECT @subscriber_security_mode = ISNULL(@subscriber_security_mode, 1)

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

	-- make sure that the login is valid
	SELECT @subscriber_login = RTRIM(LTRIM(ISNULL(@subscriber_login, '')))
	IF @subscriber_security_mode = 0 AND @subscriber_login = ''
	BEGIN
		-- '@subscriber_login cannot be null or empty when @subscriber_security_mode is set to 0 (SQL Server authentication).'
		RAISERROR(21694, 16, -1, '@subscriber_login', '@subscriber_security_mode')
		RETURN 1
	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 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_addpushsubscription_agent'.
				RAISERROR(21797, 16, -1, '@job_login', 'sp_addpushsubscription_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 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_addpushsubscription_agent'.
			RAISERROR(21797, 16, -1, '@job_login', 'sp_addpushsubscription_agent')
			RETURN 1
		END
	END
	
	-- Parameter check: @enabled_for_syncmgr
	SELECT @enabled_for_syncmgr = ISNULL(LTRIM(RTRIM(LOWER(@enabled_for_syncmgr))), 'false')
	IF @publisher_type = N'MSSQLSERVER'
	BEGIN
		IF @enabled_for_syncmgr not in ('true', 'false')
		BEGIN
			-- Invalid '@enabled_for_syncmgr' value. Valid values are 'true' or 'false'.
			RAISERROR (14148, 16, -1, '@enabled_for_syncmgr')
			RETURN 1
		END
	END
	ELSE
	BEGIN
		IF @enabled_for_syncmgr != 'false'
		BEGIN
			-- @enabled_for_syncmgr is not supported for heterogeneous publications, must be 'false' or NULL.
	        RAISERROR (21632, 16, -1, '@enabled_for_syncmgr', '''false'' or NULL')
	        RETURN (1)
		END
	END

	-- check to see if the subscription exists
	IF NOT EXISTS (SELECT ss.*
					FROM sysextendedarticlesview seav
						JOIN syssubscriptions ss
							ON ss.artid = seav.artid
					WHERE seav.pubid = @pubid
						AND ss.srvname = UPPER(@subscriber) collate database_default
						and (ss.srvname is not null and len(ss.srvname)> 0)
						AND ss.dest_db = @subscriber_db
						AND ss.subscription_type = 0)
	BEGIN
		-- "The subscription could not be found."
		RAISERROR (20021, 16, -1)
		RETURN 1
	END

	-- Check DTS parameters
	IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('distributor', 'subscriber')
    BEGIN
    	-- Invalid @dts_package_location parameter value. Valid options are 'Distributor' or 'Subscriber'.
        RAISERROR(21179, 16, -1)
        RETURN 1
    END

    IF LOWER(@dts_package_location collate SQL_Latin1_General_CP1_CS_AS) = 'distributor'
        SELECT @dts_location = 0
    ELSE
        SELECT @dts_location = 1

    IF RTRIM(ISNULL(@dts_package_name, N'')) = N''
    	AND @dts_package_password IS NOT NULL
    BEGIN
    	-- "The @dts_package_password parameter cannot have a non-null value while the @dts_package_name parameter is left unspecified."
        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

    IF @dts_package_name is not null
    	AND @allow_dts = 0
    BEGIN
    	-- "DTS properties cannot be set because the publication does not allow for data transformation."
    	RAISERROR(21178, 16, -1)
        RETURN 1
    END

    -- If the publication is 'allow_dts', push subscription has to specify a DTS package.
	-- Error check that disallow ODBC subscriber to subscriber with DTS package
	-- is at the distributor.
	-- Show dts error first, otherwise user will get 21060 below which is confusing
	IF @allow_dts <> 0
		AND @dts_package_name IS NULL
		AND @subscriber IS NOT NULL
	BEGIN
		-- "Subscribers cannot subscribe to publications that allow DTS without using a DTS package."
	    RAISERROR(21213, 16, -1)
	    RETURN 1
	END
	
	-- Parameter check:  Provider specific parameters may only be non-NULL
	-- for non-SQL subscribers
	IF			 @non_sql_subscriber = 0
		AND NOT
				(@subscriber_provider IS NULL AND
				 @subscriber_datasrc IS NULL AND
				 @subscriber_location IS NULL AND
				 @subscriber_provider_string IS NULL AND
				 @subscriber_catalog IS NULL)
	BEGIN
	    -- "Subscriber parameters specifying provider properties must be NULL for SQL Server subscribers."
	    RAISERROR(14060, 16, -1)
	    RETURN 1
	END

	IF @non_sql_subscriber = 1
	BEGIN
		-- check sync method
		IF @sync_method NOT IN (@CHAR_BCP, @CONCURRENT_CHAR, @DATABASE_SNAPSHOT_CHARACTER)
			AND @dts_package_name IS NULL
			AND @publisher_type = N'MSSQLSERVER'
		BEGIN
			-- Could not subscribe to publication '@publication' because heterogeneous Subscriber '@subscriber' only supports the @sync_method parameter value 'bcp character', 'concurrent_c', and 'database snapshot character' .
			RAISERROR (14095, 16, -1, @publication, @subscriber)
			RETURN 1
		END

		IF @sync_method NOT IN (@CHAR_BCP, @CONCURRENT_CHAR, @DATABASE_SNAPSHOT_CHARACTER)
			AND @publisher_type != N'MSSQLSERVER'
		BEGIN
			RAISERROR (21676, 16, -1, @subscriber, @publication)
			RETURN 1
		END
		
	END
	
	-- retrieve the distribution database name			
	EXEC @retcode = sys.sp_helpdistributor @rpcsrvname = @distributor_rpc OUTPUT,
											@distribdb = @distribution_db OUTPUT,
											@publisher = @publisher
	IF @@error <> 0 or @retcode <> 0 or @distribution_db is NULL
	BEGIN
		-- "The Distributor has not been installed correctly."
		RAISERROR(20036, 16, -1)
		RETURN 1
	END

	-- set the publication name in the shared case
	
	-- NOTE:
	-- This value is used for checking for job existence or
	-- for creating the job via sp_MSadd_distribution_agent.
	-- Creating the sync_mgr agent will use the un-changed
	-- @publication value specified by the user as input...
	IF @independent_agent = 0
	BEGIN
		SELECT @publication_name = 'ALL'
	END
	ELSE
	BEGIN
		SELECT @publication_name = @publication
	END
	
	SELECT @procedure =  QUOTENAME(RTRIM(@distributor_rpc)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSreplagentjobexists'
	EXEC @retcode = @procedure @type = 0,
								@exists = @distagent_exists output,
								@job_name = @distrib_job_name output,
								@proxy_id = @proxy_id output,
								@publisher = @publisher,
								@publisher_db = @publisher_db,
								@publication = @publication_name,
								@subscriber = @subscriber,
								@subscriber_db = @subscriber_db,
								@publisher_id = @dist_publisher_id output,
								@subscriber_id = @dist_subscriber_id output,
								@independent_agent = @independent_agent				
	IF @retcode <> 0 OR @@ERROR <> 0
		RETURN 1

	BEGIN TRANSACTION tr_addpush_agent
	SAVE TRANSACTION  tr_addpush_agent
	
	IF @distagent_exists = 1
	BEGIN
		-- there are 2 cases here:
		
		-- 1) We require the yukon security model then fail since
		-- the add was expected and the subscription already exists
		
		-- 2) We are here then this is a sysadmin call and if a
		-- proxy account already exists on the job then they can
		-- not call the add proc again they need to call change
		IF sys.fn_yukonsecuritymodelrequired(NULL) = 1
			OR @proxy_id IS NOT NULL		
		BEGIN
			IF @independent_agent = 1
			BEGIN
				-- "The Distribution Agent for this subscription already exists (@job_name)."
				RAISERROR (21002, 11, -1, @distrib_job_name)
				GOTO UNDO
			END
			ELSE
			BEGIN
				-- "A shared distribution agent (@job_name) already exists for this subscription."
				RAISERROR (22549, 10, -1, @distrib_job_name)
				GOTO UNDO
			END
		END
		
		SELECT @procedure =  QUOTENAME(RTRIM(@distributor_rpc)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSdrop_distribution_agent'
		EXEC @retcode = @procedure @publisher_id = @dist_publisher_id,
									@publisher_db = @publisher_db,
									@publication = @publication_name,
									@subscriber_id = @dist_subscriber_id,
									@subscriber_db = @subscriber_db,
									@subscription_type = 0,
									@job_only = 1
		IF @retcode <> 0 OR @@ERROR <> 0
			GOTO UNDO
	END
	
	-- Schedule Distribution agent for the push subscription
	SELECT @procedure = QUOTENAME(RTRIM(@distributor_rpc)) + '.' + QUOTENAME(RTRIM(@distribution_db)) + '.sys.sp_MSadd_distribution_agent'	

	EXEC @retcode = @procedure @name = @job_name,
								@publisher_id = @dist_publisher_id,
								@publisher_db = @publisher_db,
								@publication = @publication_name,
								@subscriber_id = @dist_subscriber_id,
								@subscriber_db = @subscriber_db,
								@subscription_type = 0,
								@local_job = 1,
								@frequency_type = @frequency_type,
								@frequency_interval = @frequency_interval,
								@frequency_relative_interval = @frequency_relative_interval,
								@frequency_recurrence_factor = @frequency_recurrence_factor,
								@frequency_subday = @frequency_subday,
								@frequency_subday_interval = @frequency_subday_interval,
								@active_start_time_of_day = @active_start_time_of_day,
								@active_end_time_of_day = @active_end_time_of_day,
								@active_start_date = @active_start_date ,
								@active_end_date = @active_end_date,
								@dts_package_name = @dts_package_name,
								@dts_package_password = @dts_package_password,
								@dts_package_location = @dts_location,
								@subscriber_security_mode = @subscriber_security_mode,
								@subscriber_login = @subscriber_login,
								@subscriber_password = @subscriber_password,
								@job_login = @job_login,
								@job_password = @job_password,
								@subscriber_provider = @subscriber_provider,
								@subscriber_datasrc = @subscriber_datasrc,
								@subscriber_provider_string = @subscriber_provider_string,
								@subscriber_location = @subscriber_location,
								@subscriber_catalog = @subscriber_catalog,
								@distribution_jobid = @distribution_jobid OUTPUT,
								@internal = N'YUKON ADD AGENT'
	IF @@ERROR <> 0 or @retcode <> 0
		GOTO UNDO

	IF @independent_agent = 1
	BEGIN
	    UPDATE syssubscriptions
	    	SET distribution_jobid = @distribution_jobid
			FROM syspublications sysp
				JOIN sysextendedarticlesview syseav
					on sysp.pubid = syseav.pubid
				JOIN syssubscriptions syss
					on syseav.artid = syss.artid
			WHERE sysp.pubid = @pubid
				AND syss.srvname = UPPER(@subscriber)
				AND syss.dest_db = @subscriber_db
	END
	ELSE
	BEGIN
	    UPDATE syssubscriptions
	    	SET distribution_jobid = @distribution_jobid
			WHERE srvname = UPPER(@subscriber)
				AND dest_db = @subscriber_db
	END

	-- Conditional support for MobileSync
	IF LOWER(@enabled_for_syncmgr collate SQL_Latin1_General_CP1_CS_AS) = 'true'
	BEGIN
		EXEC @retcode = sys.sp_MSrepl_getdistributorinfo @publisher   = @publisher,
													     	@distributor = @distributor OUTPUT
		IF @@ERROR <> 0 or @retcode <> 0
			GOTO UNDO
			
		-- Call sp_MSregistersubscription so that the subscription can be synchronized via MobileSync
		SELECT @subscription_id = convert(uniqueidentifier, @distribution_jobid)
		
		EXEC @retcode = sys.sp_MSregistersubscription @replication_type = 1,
														@publisher = @publisher,
														@publisher_db = @publisher_db,
														@publication = @publication,
														@subscriber = @subscriber,
														@subscriber_db = @subscriber_db,
														@distributor = @distributor,
														@distributor_security_mode = 1,
														@subscription_id = @subscription_id,
														@independent_agent = @independent_agent,
														@subscription_type = 0
		IF @@ERROR <> 0 or @retcode <> 0
			GOTO UNDO
	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_name IS NOT NULL
	BEGIN
		IF @job_login IS NOT NULL
		BEGIN
			IF @publisher_type = 'MSSQLSERVER'
			BEGIN
				SELECT @publisher_local = NULL
			END
			ELSE
			BEGIN
				SELECT @publisher_local = @publisher
			END
			
			EXEC @retcode = sys.sp_changesubscription @publication = @publication,
														@article = 'all',
														@subscriber = @subscriber,
														@destination_db = @subscriber_db,
													    @property = 'distrib_job_login',
														@value = @job_login,
														@publisher = @publisher_local
			IF @retcode <> 0 OR @@ERROR <> 0
	            GOTO UNDO

			EXEC @retcode = sys.sp_changesubscription @publication = @publication,
														@article = 'all',
														@subscriber = @subscriber,
														@destination_db = @subscriber_db,
													    @property = 'distrib_job_password',
														@value = @job_password,
														@publisher = @publisher_local
			IF @retcode <> 0 OR @@ERROR <> 0
	            GOTO UNDO
		END
	END

	COMMIT TRANSACTION tr_addpush_agent
	
	RETURN 0
UNDO:
	ROLLBACK TRANSACTION tr_addpush_agent
	COMMIT TRANSACTION
	
	RETURN 1
END

 
Last revision 2008RTM
See also

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