Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSaddpub_snapshot

  No additional text.


Syntax
create procedure sys.sp_MSaddpub_snapshot (
    @publication sysname,

    @freqtype  int = 4 ,                  /* 4== Daily */
    @freqinterval int  = 1,             /* Every day */
    @freqsubtype int =  4,                 /* Sub interval = Minute */
    @freqsubinterval int = 5,              /* Every five minutes */
    @freqrelativeinterval int = 1,
    @freqrecurrencefactor int = 0,
    @activestartdate int = 0,             /* 12:00 am - 11:59 pm */
    @activeenddate int =99991231 ,         /* No start date */
    @activestarttimeofday int = 0,
    @activeendtimeofday int = 235959,     /* No end time */
    @newagentid int = 0 OUTPUT,
    @snapshot_job_name nvarchar(100) = null,
	@publisher_security_mode int = NULL,
	@publisher_login sysname = NULL,
	@publisher_password sysname = NULL,
	@job_login nvarchar(257) = NULL,
	@job_password sysname = NULL,
	@publisher sysname = NULL
)
AS
    SET NOCOUNT ON

    -- Declarations
    DECLARE @retcode            int
    DECLARE @distributor        sysname
    DECLARE @distribdb          sysname
    DECLARE @distproc           nvarchar (4000)
    DECLARE @agentname          nvarchar(100)
    DECLARE @database           sysname
    DECLARE @newid              int
    DECLARE @mergepublish_bit   smallint
    DECLARE @centralpublish_bit int
    DECLARE @fFoundPublication  int
    DECLARE @agent_args         nvarchar(4000)
    DECLARE @snapshot_jobid     binary(16)
    DECLARE @dist_rpcname       sysname
    DECLARE @publication_type   int
    DECLARE @job_existing       bit
	DECLARE	@publisher_type		sysname
	DECLARE @pubid				int
	DECLARE @agent_exists		bit
	DECLARE @publisher_local	sysname

    -- Initializations
    select @mergepublish_bit    = 4
    select @centralpublish_bit  = 1
    select @fFoundPublication   = 0
	select @distproc			= N''
	SELECT @publisher_type		= NULL

    if (@snapshot_job_name is null) or (@snapshot_job_name = N'')
    begin
        select @job_existing = 0
    end
    else
    begin
        select @job_existing = 1
    end

	-- Get distributor information
    EXEC @retcode = sys.sp_MSrepl_getdistributorinfo	@publisher		= @publisher,
														@distributor	= @distributor OUTPUT,
														@distribdb		= @distribdb OUTPUT,
														@rpcsrvname		= @dist_rpcname OUTPUT,
														@publisher_type	= @publisher_type OUTPUT

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

    IF (@publisher IS NOT NULL) AND (@publisher_type IS NULL OR @distribdb IS NULL OR @distributor IS NULL)
    BEGIN
        RAISERROR(21600, 16, -1, @publisher)
        RETURN (1)
    END

	-- Get publication info
	SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT
	
	IF NOT @publisher_type = N'MSSQLSERVER'
	BEGIN
		SELECT @distproc = QUOTENAME(RTRIM(@distributor)) + N'.' + QUOTENAME(@distribdb) + '.'
	END

	SELECT @distproc = RTRIM(@distproc) + N'sys.sp_helppublication'
    EXEC @retcode = @distproc
					@publication = @publication,
					@publisher   = @publisher,
					@found       = @fFoundPublication OUTPUT

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

    IF @fFoundPublication = 0
    BEGIN
        SELECT @newagentid = 0
        RETURN (0)
    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

    SELECT @database = DB_NAME()

	
    -- Set local publisher value
    
    SELECT @publisher_local = ISNULL(@publisher, publishingservername())

    -- Make sure the publication does not already have a agent.
    SELECT @distproc = QUOTENAME(RTRIM(@dist_rpcname)) + '.' + QUOTENAME(@distribdb) + '.sys.sp_MSreplagentjobexists'
	EXEC @retcode = @distproc @type				= 3,
								@exists 		= @agent_exists output,
								@publisher		= @publisher_local,
								@publisher_db	= @database,
								@publication	= @publication
	IF @@ERROR <> 0 OR @retcode <> 0
		RETURN (1)
	
	IF @agent_exists = 1
	BEGIN
        RAISERROR (14101, 11, -1, @publication)
        RETURN(1)
    END

    -- Get publication_type
	SELECT	@publication_type = repl_freq
	FROM	syspublications
	WHERE	pubid = @pubid

	-- Set command based on distributor info
    SELECT @distproc = QUOTENAME(RTRIM(@dist_rpcname)) + '.' + QUOTENAME(@distribdb) + '.dbo.sp_MSadd_snapshot_agent'

	-- Check pub type
	IF @publisher_type = N'MSSQLSERVER'
	BEGIN
	    SELECT @agent_args = '-Publisher ' + QUOTENAME(publishingservername())
	    SELECT @agent_args = @agent_args + ' -PublisherDB ' + QUOTENAME(@database)
	    SELECT @agent_args = @agent_args + ' -Distributor ' + QUOTENAME(@distributor)
	    SELECT @agent_args = @agent_args + ' -Publication ' + QUOTENAME(@publication)
	END
	ELSE
	BEGIN
		SELECT @agent_args = '-Publisher ' + QUOTENAME(@publisher)
	
	    IF NOT (@publisher_type LIKE N'ORACLE%')
		BEGIN
			-- TODO: Support publisher db for non-Oracle HREPL providers
			SELECT @agent_args = @agent_args + ' -PublisherDB '   + QUOTENAME(@database)
		END
			
	    SELECT @agent_args = @agent_args + ' -Distributor '   + QUOTENAME(@distributor)
	    SELECT @agent_args = @agent_args + ' -Publication '   + QUOTENAME(@publication)
	END

    BEGIN TRAN
		DECLARE @pubname sysname
		SELECT	@pubname =	CASE @publisher_type
								WHEN N'MSSQLSERVER' THEN publishingservername()
								ELSE @publisher
							END

	    EXEC @retcode = @distproc
						@name 						= @snapshot_job_name,
						@publisher					= @pubname,
						@publisher_db				= @database,
						@publication				= @publication,
						@publication_type			= @publication_type,
						@local_job					= 1,
						@freqtype					= @freqtype,
						@freqinterval				= @freqinterval,
						@freqsubtype				= @freqsubtype,
						@freqsubinterval			= @freqsubinterval,
						@freqrelativeinterval		= @freqrelativeinterval,
						@freqrecurrencefactor		= @freqrecurrencefactor,
						@activestartdate			= @activestartdate,
						@activeenddate				= @activeenddate,
						@activestarttimeofday		= @activestarttimeofday,
						@activeendtimeofday			= @activeendtimeofday,
						@command					= @agent_args,
						@snapshot_jobid				= @snapshot_jobid OUTPUT,
						@job_existing				= @job_existing,
						@publisher_security_mode	= @publisher_security_mode,
						@publisher_login			= @publisher_login,
						@publisher_password			= @publisher_password,
	  					@job_login					= @job_login,
						@job_password				= @job_password,
						@publisher_type				= @publisher_type,
						@internal					= N'YUKON'
						
		IF @@ERROR <> 0 or @retcode <> 0
		BEGIN
	        GOTO UNDO
		END
	
	    -- Legacy, use non zero taskid to indicate agent already created at the distributor.
	    UPDATE	syspublications
		SET		snapshot_jobid =  @snapshot_jobid
		WHERE	pubid = @pubid
	
	    IF @@ERROR <> 0
		BEGIN
	        GOTO UNDO
		END
	
	    -- This is the output parameter to indicate agent created.
	    SELECT  @newagentid = 1

    COMMIT TRAN

    return (0)

UNDO:
    if @@TRANCOUNT = 1
        ROLLBACK TRAN
    else
        COMMIT TRAN
    return(1)

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSrepl_addpublication_snapshot (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