Home Microsoft SQL Server DigiMailing Contact
    Keyword



sys.sp_MScreate_distributor_tables

  No additional text.


Syntax
create procedure sys.sp_MScreate_distributor_tables
as
begin
    declare @profile_id     int
    declare @retcode    int
    declare @profile_name nvarchar(100)
    declare @profile_desc nvarchar(100)

    /* Create MSpublishers table */
    IF EXISTS (SELECT * FROM msdb.sys.objects WHERE name = 'MSdistpublishers'
        and type = 'U')
    begin
        DROP TABLE msdb..MSdistpublishers
        if @@error<> 0 goto FAILURE
    end

    /* Create MSdistributiondbs table */
    IF EXISTS (SELECT * FROM msdb.sys.objects WHERE name = 'MSdistributiondbs'
        and type = 'U')
    begin
        DROP TABLE msdb..MSdistributiondbs
        if @@error<> 0 goto FAILURE
    end

    /* create MSdistributor table */
    IF EXISTS (SELECT * FROM msdb.sys.objects WHERE name = 'MSdistributor'
        and type = 'U')
    begin
        DROP TABLE msdb..MSdistributor
        if @@error<> 0 goto FAILURE
    end

    /* create sysreplicationalerts table */
    IF EXISTS (SELECT * FROM msdb.sys.objects WHERE name = 'sysreplicationalerts'
        and type = 'U')
    begin
        DROP TABLE msdb.dbo.sysreplicationalerts
        if @@error<> 0 goto FAILURE
    end

    /* create MSagent_profiles table */
    IF EXISTS (SELECT * FROM msdb.sys.objects WHERE name = 'MSagent_profiles'
        and type = 'U')
    begin
        DROP TABLE msdb..MSagent_profiles
        if @@error<> 0 goto FAILURE
    end

    /* create MSagent_parameters table */
    IF EXISTS (SELECT * FROM msdb.sys.objects WHERE name = 'MSagent_parameters'
        and type = 'U')
    begin
        DROP TABLE msdb..MSagent_parameters
        if @@error<> 0 goto FAILURE
    end

    CREATE TABLE msdb.dbo.MSdistpublishers
    (
        name                sysname     NOT NULL,
        distribution_db     sysname     NOT NULL,
        working_directory   nvarchar(255) NOT NULL,
        security_mode       int         NOT NULL,
        login               sysname     NOT NULL,
        password            nvarchar(524) NULL,
        active              bit         NOT NULL,
        trusted             bit         NOT NULL,
        thirdparty_flag     bit         NOT NULL,
        publisher_type      sysname NOT NULL default N'MSSQLSERVER'
    )
    if @@error<> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX uc1MSdistpublishers
        ON msdb.dbo.MSdistpublishers(name)
    if @@error<> 0 goto FAILURE
    CREATE NONCLUSTERED INDEX nc2MSdistpublishers
        ON msdb.dbo.MSdistpublishers(distribution_db)
    if @@error<> 0 goto FAILURE

    exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSdistpublishers'
    if @@error<> 0 or @retcode <> 0 goto FAILURE

    CREATE TABLE msdb.dbo.MSdistributiondbs
    (
        name            sysname     NOT NULL,
        min_distretention   int     NOT NULL,
        max_distretention   int     NOT NULL,
        history_retention   int     NOT NULL
    )
    if @@error<> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX uc1MSdistributiondbs
            ON msdb.dbo.MSdistributiondbs(name)
    if @@error<> 0 goto FAILURE

    exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSdistributiondbs'
    if @@error<> 0 or @retcode <> 0 goto FAILURE

    CREATE TABLE msdb.dbo.MSdistributor
    (
        property        sysname     NOT NULL,
        value           nvarchar(3000)  NULL
    )
    if @@error<> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX uc1MSdistributor
            ON msdb.dbo.MSdistributor(property)
    if @@error<> 0 goto FAILURE

    exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSdistributor'
    if @@error<> 0 or @retcode <> 0 goto FAILURE

    CREATE TABLE msdb.dbo.sysreplicationalerts
    (
        alert_id            int identity(1,1) NOT NULL,
        status              int NOT NULL,
        agent_type          int NULL,
        agent_id            int NULL,
        error_id            int NULL,
        alert_error_code    int NULL,
        time                datetime NOT NULL,
        publisher           sysname NULL,
        publisher_db        sysname NULL,
        publication         sysname NULL,
        publication_type    int NULL,
        subscriber          sysname NULL,
        subscriber_db       sysname NULL,
        article             sysname NULL,
        destination_object  sysname NULL,
        source_object       sysname NULL,
        alert_error_text    ntext NULL
    )
    if @@error<> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX ucsysreplicationalerts ON msdb.dbo.sysreplicationalerts(alert_id)
    if @@error<> 0 goto FAILURE

    exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.sysreplicationalerts'
    if @@error<> 0 or @retcode <> 0 goto FAILURE

    CREATE TABLE msdb.dbo.MSagent_profiles
    (
        profile_id    int           NOT NULL IDENTITY,
        profile_name  sysname       NOT NULL,
        agent_type          int             NOT NULL, -- 1-Snapshot, 2-Logreader,
                                                      -- 3-Distribution, 4-Merge,
                                                      -- 9-Qreader
        type                int             NOT NULL, -- 0-System, 1-Custom
        description         nvarchar(3000)  NULL,
        def_profile   bit               NOT NULL
    )
    if @@error<> 0 goto FAILURE

    exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSagent_profiles'
    if @@error<> 0 or @retcode <> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX ucMSagent_profiles ON msdb.dbo.MSagent_profiles
        (profile_name, profile_id, agent_type)
    if @@error<> 0 goto FAILURE

    CREATE TABLE msdb.dbo.MSagent_parameters
    (
        profile_id     int          NOT NULL,
        parameter_name       sysname        NOT NULL,
        value                nvarchar(255)  NOT NULL
    )
    if @@error<> 0 goto FAILURE
    exec @retcode = msdb.dbo.sp_MS_marksystemobject 'dbo.MSagent_parameters'
    if @@error<> 0 or @retcode <> 0 goto FAILURE

    CREATE UNIQUE CLUSTERED INDEX ucMSagent_parameters ON msdb.dbo.MSagent_parameters
        (parameter_name, profile_id)
    if @@error<> 0 goto FAILURE

	/*
	** Initialize all the possible parameters for each profile
	**  (for validation and UI)
	*/
	exec sys.sp_createagentparameterlist

    /*
    ** Create default / non default profiles
    ** for all the agents
    */

    /*
    ** Snapshot agent
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default Snapshot Profile
    set @profile_desc = NULL

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 1,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Logreader agent
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default LogReader Profile
    set @profile_desc = NULL

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 2,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Logreader agent - Verbose History Profile
    */
   set @profile_id = NULL
    set @profile_name = formatmessage(20546) -- LogReader Verbose History Profile
    set @profile_desc = formatmessage(20547)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 2,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Distribution agent
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default Distribution Profile
    set @profile_desc = NULL

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Distribution Agent Verbose History Profile
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20546) -- Distribution Verbose History Profile
    set @profile_desc = formatmessage(20547)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : Default profile for well connected scenarios
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default Merge Profile
    set @profile_desc = NULL

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 1

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : Non default profile for disconnected scenarios ( unreliable link )
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20548) -- Non-Default Merge Profile
    set @profile_desc = formatmessage(20549)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : Non default profile for verbose histroy
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20546) -- Verbose Merge Profile
    set @profile_desc = formatmessage(20547)

    exec @retcode = sys.sp_add_agent_profile
        @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : Synchronization Manager Profile
    */
    set @profile_id = NULL
    set @profile_name = N'Windows Synchronization Manager profile' -- SyncMgr Profile
    set @profile_desc = formatmessage(20551)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Distribution agent : Synchronization Manager Profile
    */
    set @profile_id = NULL
    set @profile_name = N'Windows Synchronization Manager profile' -- SyncMgr Profile
    set @profile_desc = formatmessage(20551)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Qreader agent (default profile)
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20545) -- Default QueueReader Profile
    set @profile_desc = formatmessage(20589)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 9,
            @profile_type = 0,
            @description = @profile_desc,
            @default = 1
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : Rowcount Validation profile
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(21308) -- Rowcount Validation Profile
    set @profile_desc = formatmessage(21309) -- Rowcount Validation Profile Description

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : Rowcount & Checksum Validation profile
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(21310) -- Rowcount & Checksum Validation Profile
    set @profile_desc = formatmessage(21311) -- Rowcount & Checksum Validation Profile Description

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE


    /*
    ** Distribution agent, skip error profile
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20599) -- Default Distribution Profile
    set @profile_desc = formatmessage(20600)

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    /*
    ** Merge agent : High volume server-to-server profile
    */
    set @profile_id = NULL
    set @profile_name = formatmessage(20616) -- High volume server-to-server profile
    set @profile_desc = formatmessage(20617) -- High volume server-to-server profile Description

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 4,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE
    
    -- Distribution agent : OLEDB streaming usage Profile
    
    select @profile_id = NULL
            ,@profile_name = isnull(formatmessage(20814),N'Message 20814')
            ,@profile_desc = isnull(formatmessage(20815),N'Message 20815')

    exec @retcode = sys.sp_add_agent_profile
            @profile_id = @profile_id OUT,
            @profile_name = @profile_name,
            @agent_type = 3,   -- 1-Snapshot, 2-Logreader, 3-Distribution, 4-Merge
            @profile_type = 0,   -- 0-System, 1-Custom
            @description = @profile_desc,
            @default = 0

    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE

    exec @retcode = sys.sp_generate_agent_parameter @profile_id
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE
    
    -- Initialize the replication monitor metrics enumeration
    
    exec @retcode = sys.sp_replmonitorinitializemetrics
    if (@retcode = 1 or @@ERROR <> 0)
        goto FAILURE
    
    -- all done
    
    return 0
FAILURE:
    return 1
end

 
Last revision 2008RTM
See also

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