Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_IHaddpublication

  No additional text.


Syntax


-- Name:
--		sp_IHaddpublication

-- Description:
--		Creates a snapshot or transactional publication. This stored procedure is executed at
--		the Publisher on the publication database.

-- Security:
--		Internal only
-- Requires Certificate signature for catalog access

-- Returns:
--		Return code (0 for success, 1 for failure)

-- Notes:
--		Re-directs to call sp_MSrepl_addpublication in the correct db context (distributor for HREPL)


CREATE PROCEDURE sys.sp_IHaddpublication
(
	@name							sysname,
	@publisher						sysname,
	@rfid							int = 1,					-- repl freq 0 = Tran based, 1 = sched table refresh
	@statid							tinyint = 0,				-- status id  0 = inactive, 1 = active
	@sync_method_id					tinyint = 1,				-- default to character
	@enabled_for_internet_id		bit = 0,
    @allow_sync_tran				bit = 0,
	@autogen_sync_procs				bit = 0,					-- sync procs defaulting to false for het. pubs
	@snapshot_in_defaultfolder_bit	int = 1,
	@alt_snapshot_folder			nvarchar(255) = NULL,
	@pre_snapshot_script			nvarchar(255) = NULL,
	@post_snapshot_script			nvarchar(255) = NULL,
	@compress_snapshot_bit			bit = 1,
    @ftp_address					sysname = NULL,
    @ftp_port						int = 21,
    @ftp_subdirectory				nvarchar(255) = NULL,
    @ftp_login						sysname = N'anonymous',
	@enc_ftp_password				nvarchar(524) = NULL,
	@allow_dts_id					bit = 0,
	@allow_anonymous_id				bit = 0,
	@description					nvarchar(255) = NULL,
	@independent_agent				bit,
	@immediate_sync					bit,
	@allow_push						bit,
	@allow_pull						bit,
	@retention						int,
	@allow_subscription_copy		bit,
	@options						int = 0
)
as
BEGIN
    set nocount on

	DECLARE @publication_id int
	DECLARE @LinkedServer	sysname
	DECLARE @retcode		int

    /*
    ** Parameter Check:  @sync_method.
    **           1  concurrent
    **           4  concurrent_c
    */
    IF @sync_method_id IS NULL OR @sync_method_id NOT IN (1,4)
    BEGIN
        RAISERROR (21612, 16, -1)
        RETURN (1)
    END

    /*
    ** Parameter Check:  @enabled_for_internet_id.
    **           0  Not enabled for internet
    **           1  enabled for internet
    */
    IF @enabled_for_internet_id IS NULL OR @enabled_for_internet_id NOT IN (0,1)
    BEGIN
        RAISERROR (14148, 16, -1, '@enabled_for_internet')
        RETURN (1)
    END

    /*
    ** Parameter Check:  @snapshot_in_defaultfolder_bit.
    **           0  Not enabled for internet
    **           1  enabled for internet
    */
    IF @snapshot_in_defaultfolder_bit IS NULL OR @snapshot_in_defaultfolder_bit NOT IN (0,1)
    BEGIN
        RAISERROR (14148, 16, -1, '@snapshot_in_defaultfolder')
        RETURN (1)
    END

    /*
    ** Parameter Check:  @alt_snapshot_folder.
	** required if enabled for internet
    */
    IF @enabled_for_internet_id = 1 AND (@alt_snapshot_folder IS NULL OR
        @alt_snapshot_folder = N'')
        BEGIN
            RAISERROR (21159, 16, -1)
            RETURN (1)
        END

    /*
    ** Parameter Check:  @compress_snapshot_bit.
    **           0  do not compress
    **           1  compress snapshot
    */
    IF @compress_snapshot_bit IS NULL OR @compress_snapshot_bit NOT IN (0,1)
    BEGIN
        RAISERROR (14148, 16, -1, '@compress_snapshot')
        RETURN (1)
    END

    -- Parameter check: ftp_address
    -- If the publication is enabled for internet, ftp_address cannot be null
    IF @enabled_for_internet_id = 1 AND (@ftp_address IS NULL OR @ftp_address = N'')
    BEGIN
        RAISERROR (21158, 16, -1)
        RETURN (1)
    END

    -- Parameter check: ftp_port
    IF @ftp_port IS NULL or @ftp_port < 0 or @ftp_port > 65535
    BEGIN
        RAISERROR (21160, 16, -1)
		RETURN(1)
    END

    /*
    ** Parameter Check:  @allow_dts_id.
    **           0  No not allow DTS
    **           1  allow DTS
    */
    IF @allow_dts_id IS NULL OR @allow_dts_id NOT IN (0,1)
    BEGIN
        RAISERROR (14148, 16, -1, '@allow_dts')
        RETURN (1)
    END

    /*
    ** Parameter Check:  @allow_anonymous_id.
    **           0  No not allow anonymous subscribers
    **           1  allow anonymous subscribers
    */
    IF @allow_anonymous_id IS NULL OR @allow_anonymous_id NOT IN (0,1)
    BEGIN
        RAISERROR (14148, 16, -1, '@allow_anonymous')
        RETURN (1)
    END

    /*
    ** Parameter Check:  @autogen_sync_procs.
    **   must be 0  Do not allow autogen procs
    */
    IF @autogen_sync_procs IS NULL OR @autogen_sync_procs <> 0
    BEGIN
        RAISERROR (14148, 16, -1, '@autogen_sync_procs')
        RETURN (1)
    END

	-- Get the publication ID from MSpublications
	SELECT @publication_id = pubs.publication_id
	FROM   MSpublications pubs
	JOIN master.dbo.sysservers srv ON pubs.publisher_id = srv.srvid
	WHERE  pubs.publication = @name
	  AND  UPPER(srv.srvname collate database_default) = UPPER(@publisher)

    IF @publication_id is NULL
    BEGIN
        raiserror (20026, 16, -1, @name)
        return (1)
    END

	-- now that the publisher is updated we can update the distributor	
	INSERT INTO IHpublications
	        (pubid	
		    ,name
		    ,repl_freq
		    ,status
			,sync_method
		    ,snapshot_jobid
		    ,enabled_for_internet
            ,immediate_sync_ready
			,allow_queued_tran
			,allow_sync_tran
			,autogen_sync_procs
		    ,snapshot_in_defaultfolder
		    ,alt_snapshot_folder
		    ,pre_snapshot_script
		    ,post_snapshot_script
		    ,compress_snapshot
		    ,ftp_address
		    ,ftp_port
		    ,ftp_subdirectory
		    ,ftp_login
		    ,ftp_password
		    ,allow_dts
			,allow_anonymous
			,description
			,independent_agent
			,immediate_sync
			,allow_push
			,allow_pull
			,retention
			,allow_subscription_copy
			,options
	       )
      VALUES
           (@publication_id
			,@name
			,@rfid
			,@statid
			,@sync_method_id
			,NULL
			,@enabled_for_internet_id
            ,0
			,0
			,@allow_sync_tran
			,@autogen_sync_procs
			,@snapshot_in_defaultfolder_bit
			,@alt_snapshot_folder
			,@pre_snapshot_script
			,@post_snapshot_script
			,@compress_snapshot_bit
			,@ftp_address
			,@ftp_port
			,@ftp_subdirectory
			,@ftp_login
			,@enc_ftp_password
			,@allow_dts_id
			,@allow_anonymous_id
			,@description
			,@independent_agent
			,@immediate_sync
			,@allow_push
			,@allow_pull
			,@retention
			,@allow_subscription_copy
			,@options
           )

	-- Get the linked server for this
	-- EXECUTE @retcode = IHgetlinkedserver @publication = @publication, @linkedserver = @linkedserver OUTPUT
END

 
Last revision 2008RTM
See also

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