Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_adddistributor

  No additional text.


Syntax
create procedure sys.sp_adddistributor (
    @distributor sysname,            /* distributor server name */
    @heartbeat_interval int = 10,    	-- minutes
    @password sysname = NULL,
    @from_scripting bit = 0
)
AS
BEGIN

    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode int
                ,@agentname nvarchar(100)
                ,@command nvarchar(255)
                ,@distribution_db sysname
                ,@distproc nvarchar(255)
                ,@dist_rpcname sysname
                ,@dist_datasource sysname
                ,@server_added bit
                ,@login sysname

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

    select @server_added = 0
    select @login = 'distributor_admin'

    -- Verify that this SKU is allowed to be a distributor
    exec @retcode= sys.sp_MSsku_allows_replication
    if @@error<>0 return 1
    if @retcode <> 0
    begin
        raiserror(21105, 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

    -- Must be at master db.
    IF db_name() <> 'master'
    BEGIN
        RAISERROR(5001, 16,-1)
        return (1)
    END
    /*
    ** Parameter Check:  @distributor.
    ** Check to make sure that the distributor is not NULL and that it
    ** conforms to the rules for identifiers.
    */
    IF @distributor IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@distributor', 'sp_adddistributor')
            RETURN (1)
        END

    EXECUTE @retcode = sys.sp_validname @distributor

    IF @@ERROR <> 0 OR @retcode <> 0
       RETURN (1)
	
 	-- Verify password
 	IF (@password IS NULL) OR (@password = N'')
	BEGIN
		IF (UPPER(CONVERT(sysname, ServerProperty('ServerName'))) = UPPER(@distributor))
		BEGIN
		    -- LOCAL DISTRIBUTOR: Seed password with random value
	        SELECT @password = convert(sysname, newid())
		END
		ELSE
		BEGIN
			-- REMOTE DISTRIBUTOR: Require non-null password for security
			RAISERROR(21768, 16, -1)
			RETURN (1)
		END
	END

	if isnumeric(@heartbeat_interval) = 0 or convert(int, isnull(@heartbeat_interval, 0)) < 1
	begin
		raiserror (21115, 16, -1, @heartbeat_interval, '@heartbeat_interval')
		return 1
	end
	
    /*
    ** Check to make sure that the distributor doesn't already exist.
    */
    SELECT @dist_rpcname = NULL
    SELECT @dist_rpcname = srvname
               ,@dist_datasource = datasource
    FROM master.dbo.sysservers
    WHERE  srvstatus & 8 <> 0
    IF @dist_rpcname IS NOT NULL
    BEGIN
        RAISERROR (14099, 16, -1, @dist_datasource)
        RETURN(1)
    END

    -- drop repl_distributor if it exists.
    if exists (select * from master.dbo.sysservers where lower(srvname collate SQL_Latin1_General_CP1_CS_AS) =
        N'repl_distributor'  collate SQL_Latin1_General_CP1_CS_AS)
    begin
        exec @retcode = sys.sp_dropserver 'repl_distributor', 'droplogins'
        IF @@error <> 0 OR @retcode <> 0
        BEGIN
            RETURN(1)
        END
    end

    -- Add the linked server entry for the distributor
    -- Note we do this even for local server for consistancy
    EXECUTE @retcode = sys.sp_addserver  'repl_distributor'
    IF @@error <> 0 OR @retcode <> 0
    BEGIN
        RETURN (1)
    END

    select @server_added = 1

    -- Mark system link
    EXECUTE @retcode = sys.sp_serveroption  'repl_distributor', 'system','true'
    IF @@error <> 0 OR @retcode <> 0
    BEGIN
        goto UNDO
    END

    -- We will always use UPPERCASE name
    select @distributor = upper(@distributor)
    EXECUTE @retcode = sys.sp_setnetname  'repl_distributor', @distributor
    IF @@error <> 0 OR @retcode <> 0
    BEGIN
        goto UNDO
    END

    exec @retcode = sys.sp_addlinkedsrvlogin
        @rmtsrvname= 'repl_distributor',
        @useself = 'false',
        @locallogin = NULL,
        @rmtuser = @login,
        @rmtpassword = @password
    IF @@error <> 0 OR @retcode <> 0
    BEGIN
        goto UNDO
    END

    /*
    ** If this is not the local server, remote distributor must be set up first
    */
    IF UPPER(@distributor) <> UPPER(@@SERVERNAME)
    BEGIN
		-- make sure distributor version is >= publisher version
		declare @dist_ver bigint
		select @dist_ver = 0
		EXEC @retcode = repl_distributor.master.sys.sp_executesql N'select @dist_ver = @@microsoftversion',
		                                N'@dist_ver bigint output', @dist_ver output
		IF (@retcode <> 0 or @@ERROR <> 0)
		begin
		        GOTO UNDO
		end
		-- compare major versions
		if (@dist_ver & 0xFF000000 < @@microsoftversion & 0xFF000000 )
		begin
		        RAISERROR (21320,16,-1)
		        GOTO UNDO
		end
		/*
		** Test to see if the local server is defined as publisher/subscriber
		** at the remove distributor.
		** Note: cannot call sp_helpdistributor locally since the server is not
		** marked for distribution.
		** We can not move the serveroption call before this RPC because RPC failure
		** may cause the SP to terminate. Thus, we can not UNDO the server option.
		*/
		SELECT @distproc = 'repl_distributor.master.sys.sp_helpdistributor'

		DECLARE @loc_directory             nvarchar(255)
		DECLARE @loc_account             nvarchar(255)
		DECLARE @loc_mindistretention     int
		DECLARE @loc_maxdistretention     int
		DECLARE @loc_historyretention   int
		DECLARE @loc_historycleanupagent nvarchar(100)
		DECLARE @loc_distribcleanupagent nvarchar(100)
		DECLARE @alert_name nvarchar(100)
		DECLARE @alert_id int

		
		-- from publisher
		
	    EXECUTE @retcode = @distproc
			@distributor = @distributor OUTPUT,
			@distribdb = @distribution_db OUTPUT,
			@directory = @loc_directory OUTPUT,
			@account = @loc_account OUTPUT,
			@min_distretention = @loc_mindistretention OUTPUT,
			@max_distretention = @loc_maxdistretention OUTPUT,
			@history_retention = @loc_historyretention OUTPUT,
			@history_cleanupagent = @loc_historycleanupagent OUTPUT,
			@distrib_cleanupagent = @loc_distribcleanupagent OUTPUT,
			@publisher = @@SERVERNAME,
			@local = 'local'
		IF @@error <> 0 OR @retcode <> 0 OR @distribution_db is NULL
		BEGIN
			RAISERROR (21007,16,1)
			GOTO UNDO
		END

		--cleanup potential leftover here rather than in sys.sp_changedistpublisher as it used to be
		--because sp_changedistpublisher is public proc which can be called by user in ad-hoc fashion
		if @distribution_db is not null
		begin
			SELECT @distproc = 'repl_distributor.' + quotename(@distribution_db) + '.sys.sp_MSdistpublisher_cleanup'
			EXECUTE @retcode = @distproc @@SERVERNAME
			IF @@error <> 0 OR @retcode <> 0
			BEGIN
				GOTO UNDO
			END
		end

		/* Activate the dist publisher at the remote distributor */
		SELECT @distproc = 'repl_distributor.master.sys.sp_changedistpublisher'
		EXECUTE @retcode = @distproc @@SERVERNAME, 'active','true'
		IF @@error <> 0 OR @retcode <> 0
		BEGIN
			GOTO UNDO
		END
	END
	ELSE
	/* set the registry */
	BEGIN
		EXEC @retcode = sys.sp_MScreate_distributor_tables
		if @@error <> 0 or @retcode <> 0
			goto UNDO

		declare @distributor_login sysname
		select @distributor_login = 'distributor_admin'

		-- Add publisher/subscriber rpc login
		if not exists (select * from master.dbo.syslogins where loginname = @distributor_login collate database_default)
		begin
			EXEC @retcode = sys.sp_addlogin @loginame = @distributor_login,
					@passwd = @password
			if @@error <> 0 or @retcode <> 0
				goto UNDO
		end
		else
		begin
			-- Change the password if the distributor is local
			EXEC @retcode = sys.sp_password NULL, @password, 'distributor_admin'
			if @@error <> 0 or @retcode <> 0
				goto UNDO
		end

	    -- Add the login to sysadmin
        -- Refer to sp_MSpublication_access in distribution db
        if is_srvrolemember('sysadmin', @distributor_login) <> 1
        begin
		exec @retcode = sys.sp_addsrvrolemember @distributor_login, 'sysadmin'
		IF @@error <> 0 OR @retcode <> 0
			GOTO UNDO
        end

		if @from_scripting <> 1
		begin
			-- Add Replication Agent Checkup Agent
			exec @retcode = sys.sp_MScreate_replication_checkup_agent @heartbeat_interval = @heartbeat_interval
			if @@error <> 0 or @retcode <> 0
				goto UNDO
		end

		delete msdb..MSdistributor where property = 'heartbeat_interval'
		if @@error <> 0
			goto UNDO
		insert into msdb..MSdistributor (property, value) values ('heartbeat_interval',
				convert(nvarchar(10), @heartbeat_interval))
		if @@error <> 0
			goto UNDO

		-- Add Replication Alerts and Response Jobs
		exec @retcode = sys.sp_MSadd_distributor_alerts_and_responses
			@from_scripting = @from_scripting
		if @@error <> 0 or @retcode <> 0
			goto UNDO
	END

	/*
	** Set the server option to indicate that there is a distributor.
	*/
	EXECUTE @retcode = sys.sp_serveroption 'repl_distributor', 'dist', true
	IF @@error <> 0 OR @retcode <> 0
	BEGIN
		GOTO UNDO
	END

	-- Set sp_MSrepl_startup to be a startup stored procedure
	-- Note: This needs to be after the marking the distribution server
	exec @retcode = sys.sp_procoption 'sp_MSrepl_startup', 'startup', 'true'
	if @@error <> 0 or @retcode <> 0
		goto UNDO1

	exec @retcode = sys.sp_MSrepl_startup_internal
	if @@error <> 0 or @retcode <> 0
		goto UNDO1

	EXEC @retcode = sys.sp_MSrepl_add_expired_sub_cleanup_job
	IF @@ERROR <> 0 or @retcode <> 0
	BEGIN
		GOTO UNDO1
	END
	
	RETURN (0)

UNDO1:
	exec sys.sp_serveroption 'repl_distributor', 'dist', false
	EXEC sys.sp_MSrepl_drop_expired_sub_cleanup_job

UNDO:
	IF @server_added = 1
	begin
		-- Drop the remote logins, otherwise, sp_dropserver will fail.
		EXECUTE sys.sp_dropserver 'repl_distributor', 'droplogins'
	end

	RETURN (1)
END

 
Last revision 2008RTM
See also

  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