Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_adddistpublisher

  No additional text.


Syntax

-- Name: sp_MSrepl_adddistpublisher

-- Descriptions:

-- Parameters: as defined in create statement

-- Returns: 0 - success
--          1 - Otherwise

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

-- NOTE: @trusted is a deprecated parameter.  It is left here in
-- case we need to reactivate in the future.  It will be checked
-- in sp_addarticle (the entry proc) to see if it is anything other
-- than false.


create procedure sys.sp_MSrepl_adddistpublisher
(
    @publisher				sysname,
    @distribution_db		sysname,
    @security_mode			int,
    @login					sysname,
    @password				sysname,
    @working_directory		nvarchar(255),
    @trusted				nvarchar(5),	-- DEPRECATED
    @encrypted_password		bit,
    @thirdparty_flag		bit,
    @publisher_type			sysname
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */

    DECLARE @retcode					int
    DECLARE @active_value				int
    DECLARE @server_added				bit
    DECLARE @proc						nvarchar(524)
    declare @fExists					int
    declare @command					nvarchar(255)
    declare @trusted_id					bit
    declare @platform_nt				binary
    declare @qv_replication				varchar(10)
    declare @qv_replication_unlimited	integer
    declare @qv_value_replication		integer
    declare @enc_password				nvarchar(524)
    declare @srvproduct					nvarchar(128)
    declare @hrepl_provider				nvarchar(128)
    declare @distributortimestamp		datetime
    declare @publisher_guid				uniqueidentifier
    DECLARE @vendor						sysname
	DECLARE @sysuser                    sysname
	DECLARE @loc_publisher_type			sysname
    DECLARE @sa_login                   sysname


	-- Initial values
	SELECT	@sysuser                  = SYSTEM_USER,
			@platform_nt              = 0x1,
			@qv_replication           = '2745196162',
			@qv_replication_unlimited = 0,
			@server_added             = 0,
			@hrepl_provider           = NULL,
            @sa_login                 = SUSER_SNAME(0x01)

    /*
    ** Security Check: require sysadmin
    */
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN (1)
    END

    /*
    ** Check if replication components are installed on this server
    */
    exec @retcode = sys.sp_MS_replication_installed
    if (@retcode <> 1)
    begin
        return (1)
    end

    /*
    ** Instead of raising an error when the working is empty or null,
    ** compute a default working directory based on the default data path of
    ** the distributor.
    */


    IF @working_directory IS NULL or ltrim(rtrim(@working_directory)) = ''
    BEGIN
        EXECUTE @retcode = master.dbo.xp_instance_regread
                    'HKEY_LOCAL_MACHINE',
                    'SOFTWARE\Microsoft\MSSQLServer\Setup',
                    'SQLDataRoot',
                    @param = @working_directory OUTPUT,
                    @no_output = 'no_output'

        SELECT @working_directory = RTRIM(LTRIM(@working_directory))
        /*
        ** If the returned data path is not a a UNC path (which is almost
        ** always the case) then remap the data path as an admin. UNC share.
        ** i.e. \\\$\\ReplData
        */
        IF LEFT(@working_directory, 2) <> N'\\' AND
           SUBSTRING(@working_directory, 2, 1) = N':'
        BEGIN
            SELECT @working_directory = N'\\' +
                   CONVERT(sysname, SERVERPROPERTY('MachineName')) + N'\' +
                   LEFT(@working_directory,1) + '$' +
                   SUBSTRING(@working_directory, 3, 260) -- Note:The returned working directory cannot be more than 260 characters long
        END
        SELECT @working_directory = @working_directory +
            CASE WHEN RIGHT(@working_directory, 1) = N'\' THEN 'ReplData'
                 ELSE '\ReplData'
            END
    END


    /*
    ** Parameter Check:  @publisher.
    ** Check to make sure that the publisher is not NULL and that it
    ** conforms to the rules for identifiers.
    */

    IF @publisher IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@publisher', 'sp_adddistpublisher')
            return (1)
        END

    EXECUTE @retcode = sys.sp_validname @publisher
    IF @@ERROR <> 0 OR @retcode <> 0
        return (1)

    /*
    ** Parameter Check:  @publisher_type.
    */
    SELECT	@publisher_type     = UPPER(@publisher_type),
    		@loc_publisher_type = UPPER(@publisher_type)

    IF @publisher_type != N'MSSQLSERVER'
    BEGIN
		-- Verify SQL Server edition
		-- HREPL is restricted to Eval, Enterprise and Developer editions
		IF (sys.fn_MSrepl_editionid () not in (30,31))
		BEGIN
			DECLARE @edition sysname
			SELECT @edition = CONVERT(sysname, SERVERPROPERTY('Edition'))
			RAISERROR(21793, 16, -1, @edition)
			RETURN (1)
		END

    	-- Get provider
    	EXEC @retcode = sys.sp_IHgetprovider @loc_publisher_type OUTPUT, @hrepl_provider OUTPUT

    	IF @retcode != 0 OR @@ERROR != 0
    	BEGIN
    		RETURN (1)
    	END

         -- is this a distributor?
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	master.dbo.sysservers
			WHERE	UPPER(datasource collate database_default) = UPPER(@@SERVERNAME) COLLATE DATABASE_DEFAULT
              AND	srvstatus & 8 <> 0
        )
        BEGIN
            RAISERROR(21605, 16, -1)
            RETURN (1)
        END

        -- make sure we have the table that will tell us if this is a distribution database
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	msdb.sys.objects
        	WHERE	name = 'MSdistributiondbs' COLLATE DATABASE_DEFAULT
        	  AND	type = 'U'
       	)
        BEGIN
            RAISERROR(21605, 16, -1)
            RETURN (1)
        END

        -- check if this is a distribution database
        IF NOT EXISTS
        (
        	SELECT	*
        	FROM	msdb..MSdistributiondbs
        	WHERE	name = DB_NAME() COLLATE DATABASE_DEFAULT
        )
        BEGIN
            RAISERROR(21605, 16, -1)
            RETURN (1)
        END
    END

    IF @loc_publisher_type NOT IN (N'MSSQLSERVER', N'ORACLE', N'ORACLE GATEWAY')
    BEGIN
        RAISERROR (21643, 16, -1, '@publisher_type')
        return (1)
    END
    	
 	/*
	** Parameter Check:  @password.
	*/
   IF @loc_publisher_type = N'MSSQLSERVER'
    BEGIN
        IF @password = N''
            select @password = NULL
    END
    ELSE
    BEGIN
        IF @password = N'' or @password is NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@password', 'sp_adddistpublisher')
            return (1)
        END
    END

    /* On REPLICATION_LIMITED server, only local publisher is supported.
     * Note: The login and password registered for local publisher will be used for
     * local agents to login to distributor, thus local publisher has to be installed first.
     * We choose not to support remote dist publshers on REPLICATION_LIMITED server altogether.
     * On NT, local agents will always use integrated security to log into
     * distributor
     * Today, REPLICATION_LIMITED means desktop but we check specific sku entry just in case
    */
    exec @qv_value_replication = sys.sp_MSinstance_qv @qv_replication
    if ( @qv_value_replication != @qv_replication_unlimited ) and ( UPPER(@publisher) <> UPPER(@@servername) )
    begin
        -- remote dist publisher is not supported on this server version
        raiserror(21041,16,-1)
        return (1)
    end


    -- Set default security
    IF @security_mode IS NULL
    BEGIN
		IF @loc_publisher_type = N'MSSQLSERVER' AND (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( platform() & @platform_nt = @platform_nt ) )
			SELECT @security_mode = 1
		ELSE
			SELECT @security_mode = 0
    END

    /*
    ** Check for invalid security mode
    */
    IF @security_mode < 0 OR @security_mode > 1
        BEGIN
            RAISERROR(14109, 16, -1)
            return (1)
        END

    IF (UPPER(@publisher) = UPPER(@@SERVERNAME) and ( @platform_nt != platform() & @platform_nt ) and @security_mode = 1)
    BEGIN
        RAISERROR(21038, 16, -1)
        RETURN (1)
    END

	-- Check to ensure a login is provided if security mode is SQL Server authentication.
	select @login = rtrim(ltrim(isnull(@login, '')))

	-- Security Mode = 1
	IF @security_mode = 1
	BEGIN
		SELECT @login = N'',
				@password = newid()
	END
	-- Security Mode = 0
	ELSE IF @login = ''
	BEGIN
		-- '@login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
		raiserror(21694, 16, -1, '@login', '@security_mode')
		return 1
	END

    -- Encrypt the password
    -- We no longer supported passing in encrypted passwords
	IF @encrypted_password = 1
	BEGIN
		-- Parameter '@encrypted_password' is no longer supported.
		RAISERROR(21698, 16, -1, '@encrypted_password')
		RETURN (1)
	END

    -- Validate the working directory
    -- Remove heading and trailing spaces
    select @working_directory = RTRIM(LTRIM(@working_directory))

    -- if the last char is '\', remove it.
    if substring(@working_directory, len(@working_directory),1) = '\'
        select @working_directory = substring(@working_directory, 1,
            len(@working_directory)-1)

    -- Don't do validation if it is a UNC path due to security problem.
    -- If the server is started as a service using local system account, we
    -- don't have access to the UNC path.
    if substring(@working_directory, 1,2) <> '\\'
    begin
        select @command = 'dir "' + sys.fn_escapecmdshellsymbolsremovequotes(@working_directory) collate database_default + '"'
        exec @retcode = master.dbo.xp_cmdshell @command, 'no_output'
        if @@error <> 0
            return (1)
        if @retcode <> 0
        begin
            raiserror (21037, 16, -1, @working_directory)
            return (1)
        end
    end

    /*
    ** Parameter Check:  @trusted
    */

	-- SECURITY RISK:
	-- Trusted should be false by default to avoid potential security hole.
	-- If another machine spoofs the local box, it could be allowed to have
	-- a trusted connection.  User must explicitly request for trusted to be used.
	
	-- sp_addarticle now checks to see that this is always false making this check
	-- redundant.  It remains in case we need to reactivate for backwards compat.

    IF LOWER(@trusted collate SQL_Latin1_General_CP1_CS_AS) NOT IN ('true', 'false')
    BEGIN
        RAISERROR (14148, 16, -1, '@trusted')
        RETURN (1)
    END

    IF NOT @loc_publisher_type = N'MSSQLSERVER'
    BEGIN
        IF @trusted = 'true'
        BEGIN
            RAISERROR (21639, 16, -1)
            RETURN (1)
        END

        IF @thirdparty_flag = 1
        BEGIN
            RAISERROR (21640, 16, -1)
            RETURN (1)
        END
    END	

    IF LOWER(@trusted collate SQL_Latin1_General_CP1_CS_AS) = 'true'
    BEGIN
		-- OBSOLETE SETTING
    	SELECT @trusted_id = 1
    END
    ELSE
    BEGIN
    	SELECT @trusted_id = 0
    END

    /*
    ** Check to make sure this is a distributor
    */
    IF NOT EXISTS (SELECT * FROM master.dbo.sysservers
              WHERE UPPER(datasource collate database_default) = UPPER(@@SERVERNAME) collate database_default
                 AND srvstatus & 8 <> 0)
    BEGIN
        RAISERROR (14114, 16, -1, @@SERVERNAME)
        return (1)
    END

    /*
    ** Check if database is configured as a distributor database
    */
    IF NOT EXISTS (SELECT * FROM msdb..MSdistributiondbs WHERE name = @distribution_db collate database_default)
    BEGIN
        RAISERROR (14117, 16, -1, @distribution_db)
        return (1)
END

    /* Check if publisher is already defined. */
    IF EXISTS (SELECT *
         FROM msdb..MSdistpublishers
        WHERE UPPER(name collate database_default) = UPPER(@publisher) collate database_default)

    BEGIN
        RAISERROR (14074, 16, -1, @publisher)
        RETURN (1)
    END


    IF @loc_publisher_type = N'MSSQLSERVER'
    BEGIN
        IF NOT EXISTS (SELECT *
                 FROM master.dbo.sysservers
                WHERE UPPER(srvname collate database_default) = UPPER(@publisher) collate database_default)

        /* Add the server if it does not exist. */
        BEGIN
        	
            -- We will always add UPPERCASE entries
            
            select @publisher = upper(@publisher)

            EXECUTE @retcode = sys.sp_addserver @publisher
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14075, 16, -1)
                GOTO UNDO
            END
            SELECT @server_added = 1
        END
        ELSE
        BEGIN
            SELECT @publisher = sys.fn_getpersistedservernamecasevariation(@publisher) collate database_default
        END
    END
    ELSE
    BEGIN
	    -- For heterogeneous publisher set the server product based on the publisher type
	    IF @loc_publisher_type = N'ORACLE'
	    BEGIN
	        SELECT	@srvproduct	= 'Oracle Replication',
					@vendor		= 'ORACLE'
	    END
	    ELSE IF @loc_publisher_type = N'ORACLE GATEWAY'
	    BEGIN
	        SELECT	@srvproduct	= 'Oracle Gateway Replication',
					@vendor		= 'ORACLE'
	    END

    	-- Check if a linked server already exists
        IF EXISTS
        (
        	SELECT	*
			FROM	master.dbo.sysservers
			WHERE	UPPER(srvname collate database_default) = UPPER(@publisher)
		)
        BEGIN
            RAISERROR(21642, 16, -1, @publisher)
            RETURN (1)
        END
        
        -- We will always add UPPERCASE entries
        
        SELECT @publisher = upper(@publisher)

        -- If the login is entered double quoted, we will retain the value as entered retaining
        -- the double quotes.  Otherwise, we will upper case the entered value to mirror
        -- PL/SQL behavior.
        IF NOT ((substring(@login, 1, 1) = '"') AND (substring(@login, LEN(@login), 1) = '"'))
        BEGIN
	    SET @login = UPPER(@login)
        END

		-- Verify connection
		EXEC @retcode = sys.sp_MSrepl_testconnection	@publisher_type	= @publisher_type,
														@publisher		= @publisher,
														@security_mode	= @security_mode,
														@login			= @login,
														@password		= @password
						
        IF @@ERROR<>0 OR @retcode<>0
        BEGIN
            RAISERROR(21747, 16, -1, @publisher)
            GOTO UNDO
        END

        -- Add linked server for the hrepl publisher
        EXECUTE @retcode = sys.sp_addlinkedserver	@server     = @publisher,
													@srvproduct = @srvproduct,
													@provider   = @hrepl_provider,
													@datasrc    = @publisher

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END

        SELECT @server_added = 1

        -- Set data access option
        exec @retcode = sys.sp_serveroption  @publisher, 'data access', 'true'
        IF @@error <> 0 or @retcode <> 0
        BEGIN
            RAISERROR (15206, 16, -1, 'data access')
            GOTO UNDO
        END

        -- Set linked server options
        EXEC @retcode = sys.sp_serveroption	@publisher, 'rpc', 'true'

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (15206, 16, -1, 'rpc')
            GOTO UNDO
        END

        EXEC @retcode = sys.sp_serveroption	@publisher, 'rpc out', 'true'

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (15206, 16, -1, 'rpc out')
       GOTO UNDO
        END

        -- Set server publisher option
        exec @retcode = sys.sp_serveroption @publisher, 'pub', 'true'

        IF @@error != 0 OR @retcode != 0
        BEGIN
            RAISERROR (15206, 16, -1, 'pub')
            GOTO UNDO
        END

        --Mark system link
        exec @retcode = sys.sp_serveroption @publisher, 'system', 'true'

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (15206, 16, -1, 'system')
            GOTO UNDO
        END

        -- Map sa to use provided remote login/password
        -- All linked server queries must be done as sa (via execute as owner)

        EXEC @retcode = sys.sp_addlinkedsrvlogin	@rmtsrvname		= @publisher,
													@useself		= N'false',
													@locallogin		= @sa_login,
													@rmtuser		= @login,
													@rmtpassword		= @password

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END

        -- Map all other users to use invalid login/password
        -- User must add any additional mappings via direct calls to sp_addlinkedsrvlogin
        EXEC @retcode = sp_addlinkedsrvlogin	@rmtsrvname		= @publisher,
												@useself		= 'false',
												@locallogin		= null,
												@rmtuser		= null,
												@rmtpassword	= null

        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RAISERROR (14075, 16, -1)
            GOTO UNDO
        END
    END

    /*
    ** Set the Active  value.
    ** If the @publisher is local, set it to true.
    ** If the publisher is heterogeneous, set it to true.
    ** Otherwise, set it to false
    */
    IF ((UPPER(@publisher) = UPPER(@@SERVERNAME)) OR (@loc_publisher_type != N'MSSQLSERVER'))
    BEGIN
        SELECT @active_value = 1
    END
    ELSE
    BEGIN
        SELECT @active_value = 0
	END

    DELETE	msdb.dbo.MSdistpublishers
    WHERE	UPPER(name collate database_default) = UPPER(@publisher) collate database_default

    IF @@ERROR <> 0
    BEGIN
        GOTO UNDO
    END

	-- we must encrypt the password using the distdb certs/keys
    select @enc_password = @password,
		    @proc = QUOTENAME(@distribution_db) + '.sys.sp_MSreplencrypt'

    EXEC @retcode = @proc @enc_password OUTPUT
    IF @@error <> 0 OR @retcode <> 0
    	GOTO UNDO
    	
    -- Distributed transactions won't work with HREPL.
    -- Since data is needed from MSdistpublishers,
    -- insert it here and be sure to clean it up
    -- later if failure occurs.  Normally would use
    -- a transaction
    INSERT INTO msdb.dbo.MSdistpublishers
				(
					name,
					distribution_db,
					working_directory,
					security_mode,
					login,
					password,
					active,
					trusted,
					thirdparty_flag,
					publisher_type
				)
        VALUES  (
					UPPER(@publisher),
					@distribution_db,
					@working_directory,
					@security_mode,
					@login,
					@enc_password,
					@active_value,
					@trusted_id,
					@thirdparty_flag,
					@loc_publisher_type
				)

    IF @@ERROR <> 0
    BEGIN
        GOTO UNDO
    END

	-- HREPL: Setup publisher side
	IF @loc_publisher_type IN ('ORACLE', 'ORACLE GATEWAY')
	BEGIN
		SET @distributortimestamp = GETDATE()
		SET @publisher_guid = NEWID()

		EXEC @retcode = sys.sp_ORAaddpublisher
						@publisher		= @publisher,
						@dist_db		= @distribution_db,
						@security_mode	= @security_mode,
						@remotelogin	= @login,
						@remotepassword	= @password,
						@distributortimestamp	= @distributortimestamp,
						@publisher_guid	= @publisher_guid

		IF @@ERROR <> 0 OR @retcode <> 0
		BEGIN
			DELETE	msdb.dbo.MSdistpublishers
			WHERE	UPPER(name collate database_default) = UPPER(@publisher) collate database_default

			GOTO UNDO
		END

		-- Set the SQL version in the Oracle package
		EXEC @retcode = sys.sp_setOraclepackageversion @publisher = @publisher
		
		if @@ERROR <> 0 or @retcode <> 0
		BEGIN
			DELETE	msdb.dbo.MSdistpublishers
			WHERE	UPPER(name collate database_default) = UPPER(@publisher) collate database_default
			RAISERROR (21647, 16, -1)

			GOTO UNDO
		END

	END

    -- If this is a heterogeneous publisher then add it to the heterogeneous publisher table
    -- this will 'mark' this publisher as heterogeneous
    -- Also connects to the het pub to validate and enable it as a publisher
    IF NOT @loc_publisher_type = N'MSSQLSERVER'
    BEGIN
        EXEC @retcode = sys.sp_IHaddpublisher	@publisher	= @publisher,
												@vendor		= @vendor,
												@distributortimestamp	= @distributortimestamp,
												@publisher_guid	= @publisher_guid
        if @@ERROR <> 0 or @retcode <> 0
        BEGIN
			DELETE	msdb.dbo.MSdistpublishers
			WHERE	UPPER(name collate database_default) = UPPER(@publisher) collate database_default

            GOTO UNDO
        END

		-- Add expired subscription cleanup job and alerts
		EXEC @retcode = sys.sp_MSrepl_add_expired_sub_cleanup_job @publisher
		
        IF @@ERROR <> 0 or @retcode <> 0
        BEGIN
        	GOTO UNDO
        END
	END

    IF @loc_publisher_type = N'MSSQLSERVER'
    BEGIN
        -- Add distributor_admin to distribution_admin non trusted mapping
        exec @fExists = sys.sp_MSIfExistsRemoteLogin @publisher, null,
            'distributor_admin'
        if( @fExists = 0 )
        BEGIN
            EXECUTE @retcode = sys.sp_addremotelogin @publisher, 'distributor_admin',
                'distributor_admin'
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14075, 16, -1)
                GOTO UNDO
            END
        END

        if @trusted_id = 1
        begin
            exec @fExists = sys.sp_MSIfExistsRemoteLogin @publisher, 'distributor_admin', @sa_login
            if( @fExists = 1 )
            BEGIN
                EXECUTE @retcode = sys.sp_remoteoption @publisher, 'distributor_admin', @sa_login, trusted, true
                IF @@error <> 0 OR @retcode <> 0
                BEGIN
                    RAISERROR (14075, 16, -1)
                    GOTO UNDO
                END
            END

            EXECUTE @retcode = sys.sp_remoteoption @publisher, 'distributor_admin', 'distributor_admin', trusted, true
            IF @@error <> 0 OR @retcode <> 0
            BEGIN
                RAISERROR (14075, 16, -1)
                GOTO UNDO
            END
        END
    END

    RETURN(0)

UNDO:
    -- If the server is marked, drop it
    IF EXISTS
    (
    	SELECT	*
		FROM	msdb..MSdistpublishers
        WHERE	UPPER(name collate database_default) = UPPER(@publisher) collate database_default
	)
	BEGIN
		EXEC sys.sp_dropdistpublisher @publisher
	END

    IF @server_added = 1
    BEGIN
    	-- get the servername that was persisted in the sysservers table
    	SELECT @publisher = sys.fn_getpersistedservernamecasevariation(@publisher) collate database_default

		IF @loc_publisher_type = N'MSSQLSERVER'
		BEGIN
			EXEC sys.sp_dropserver @publisher
		END
		ELSE
		BEGIN
			EXEC sys.sp_dropserver @server = @publisher, @droplogins = 'droplogins'
		END
	END

    RETURN(1)
END

 
Last revision 2008RTM
See also

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