Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_addpublication_snapshot

  No additional text.


Syntax
create procedure sys.sp_MSrepl_addpublication_snapshot
(
    @publication					sysname,
    @frequency_type					int,
    @frequency_interval				int,
    @frequency_subday				int,
    @frequency_subday_interval		int,
    @frequency_relative_interval	int,
    @frequency_recurrence_factor	int,
    @active_start_date				int,
    @active_end_date				int,
    @active_start_time_of_day		int,
    @active_end_time_of_day			int,
    @snapshot_job_name				nvarchar(100),
	@publisher_security_mode		int,
	@publisher_login				sysname,
	@publisher_password 			sysname,
	@job_login 						nvarchar(257),
	@job_password 					sysname,
	@publisher						sysname,
	@publisher_type					sysname
)
AS
    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode                int
    DECLARE @newid                  int
    DECLARE @mergepublish_bit       smallint
    DECLARE @transpublish_bit       int
    DECLARE @transpub_found         bit
    DECLARE @mergepub_found         bit
    DECLARE @newagentid             int
    DECLARE @dbname					sysname
    DECLARE @publisher_local		sysname

    /*
    ** Initializations
    */
    select @mergepublish_bit    = 4
    select @transpublish_bit    = 1
    select @transpub_found      = 0
    select @mergepub_found      = 0
    select @dbname				= db_name()

    /*
    ** Parameter Check
    */
    exec @retcode = sys.sp_MSreplcheck_name @publication, '@publication', 'sp_MSrepl_addpublication_snapshot'
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    /*
    ** Security Check
    */
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

	-- ensure that we are published and then go to work
	IF sys.fn_MSrepl_ispublished(@dbname) != 1
	BEGIN
		-- The database is not published.
		RAISERROR (18757, 16, -1)
		RETURN 1
	END
	
	-- SQLSERVER
	IF @publisher_type = N'MSSQLSERVER'
	BEGIN
		-- if @pub_sec_mode is NULL default it to Windows Auth.
		SELECT @publisher_security_mode = ISNULL(@publisher_security_mode, 1)
		
		-- check security mode
	    IF @publisher_security_mode NOT IN (0, 1)
	    BEGIN
			-- The specified '@publisher_security_mode' is invalid (valid values are: 0, 1).
			RAISERROR(14266, 16, -1, '@publisher_security_mode', '0, 1')
			RETURN 1
	    END

	    -- Check to ensure a login is provided if security mode is SQL Server authentication.
		SELECT @publisher_login = rtrim(ltrim(isnull(@publisher_login, '')))
		IF @publisher_security_mode = 0 AND @publisher_login = ''
		BEGIN
			-- '@publisher_login cannot be null or empty when @publisher_security_mode is set to 0 (SQL Server authentication).'
			RAISERROR(21694, 16, -1, '@publisher_login', '@publisher_security_mode')
			RETURN 1
		END
	END
	-- HETERO
	ELSE
	BEGIN
		IF @publisher_security_mode IS NOT NULL
			OR @publisher_login IS NOT NULL
			OR @publisher_password IS NOT NULL
		BEGIN
			-- For heterogeneous publications, the @publisher_security_mode, @publisher_login and @publisher_password parameter(s) should be specified when calling 'sp_adddistpublisher'.
			RAISERROR(22535, 16, -1, '@publisher_security_mode, @publisher_login and @publisher_password', 'sp_adddistpublisher')
			RETURN 1
		END
	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_addpublication_snapshot'.
				RAISERROR(21797, 16, -1, '@job_login', 'sp_addpublication_snapshot')
				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_addpublication_snapshot'.
			RAISERROR(21797, 16, -1, '@job_login', 'sp_addpublication_snapshot')
			RETURN 1
		END
	END

	BEGIN TRANSACTION tr_addsnapshot_agent
	SAVE TRANSACTION tr_addsnapshot_agent
	
    /*
    **  Check if the publication is valid.
    **  1. Check transaction-level publications
    **  2. Check merge publications
    */
    if ((select category & @transpublish_bit from master.dbo.sysdatabases where name = DB_NAME() collate database_default) <> 0) or
		(not @publisher_type = N'MSSQLSERVER')
    begin
        EXEC @retcode = sys.sp_MSaddpub_snapshot
								@publication = @publication,
					            @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,
					            @newagentid = @newagentid OUTPUT,
					            @snapshot_job_name = @snapshot_job_name,
								@publisher_security_mode = @publisher_security_mode,
								@publisher_login = @publisher_login,
								@publisher_password = @publisher_password,
								@job_login = @job_login,
								@job_password = @job_password,
								@publisher = @publisher
        IF @retcode <> 0 OR @@ERROR <> 0
            GOTO UNDO

        if @newagentid <> 0
        begin
            select @transpub_found = 1
            goto DONE
        end
    end

    if (select category & @mergepublish_bit from master.dbo.sysdatabases where name = DB_NAME() collate database_default) <> 0
    begin
        EXEC @retcode = sys.sp_MSaddmergepub_snapshot
					        	@publication = @publication ,
					            @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,
					            @newtaskid = @newagentid OUTPUT,
					            @snapshot_job_name = @snapshot_job_name,
					            @publisher_security_mode = @publisher_security_mode,
								@publisher_login = @publisher_login,
								@publisher_password = @publisher_password,
								@job_login = @job_login,
								@job_password = @job_password

        IF @retcode <> 0 OR @@ERROR <> 0
            GOTO UNDO

        if @newagentid <> 0
        begin
            select @mergepub_found = 1
            goto DONE
        end
    end

DONE:
	-- 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 @snapshot_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_changepublication_snapshot @publication = @publication,
															@job_login = @job_login,
															@job_password = @job_password,
															@publisher = @publisher_local
			IF @retcode <> 0 OR @@ERROR <> 0
	            GOTO UNDO
		END
	END
	
	COMMIT TRANSACTION tr_addsnapshot_agent
	
    if @transpub_found = 0 and @mergepub_found = 0
    begin
        RAISERROR (15001, 11, -1, @publication)
        RETURN (1)
    end
    return (0)

UNDO:
	ROLLBACK TRANSACTION tr_addsnapshot_agent
	COMMIT TRANSACTION

	RETURN 1

 
Last revision 2008RTM
See also

  sp_addpublication_snapshot (Procedure)
sp_MSdrop_replcom (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