Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


-- Name:
--		sp_MSrepl_getdistributorinfo

-- Description:
--		Procedure used to efficiently obtain distributor, distribution database
--              rpc server name, and publisher type.

-- Returns:
--		0 == SUCCESS
--		1 == FAILURE
--              Several output parameters

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

-- Notes:
--		This is an internal stored procedure to be used as an alternative
--              to sp_helpdistributor when only basic information about the
--              distributor is desired.  It must be called from a publisher
--              having an entry in the sysservers table associated
--              with the distributor.

create procedure sys.sp_MSrepl_getdistributorinfo
    @distributor    sysname = NULL OUTPUT,
    @distribdb      sysname = NULL OUTPUT,
    @publisher      sysname = NULL,
    @local          nvarchar(5) = NULL,
    @rpcsrvname     sysname = NULL OUTPUT,
    @publisher_type sysname = NULL OUTPUT,
    @publisher_id   int = NULL OUTPUT,
    @working_directory nvarchar(255) = NULL OUTPUT,
    @version int = NULL OUTPUT
    DECLARE @loc_distributor sysname,
            @loc_distribdb sysname,
            @loc_rpcsrvname sysname,
            @loc_publishertype sysname,
            @loc_publisherid int,
            @proc nvarchar(255),
            @retcode int,
            @srvid smallint,
            @dist_rpcname sysname,
            @loc_working_directory nvarchar(255),
            @loc_version int

    -- Get the distribution server
    SELECT  @dist_rpcname = name,
                @loc_distributor = upper(data_source collate database_default ),
                @srvid = server_id,
                @loc_rpcsrvname = name
    FROM sys.servers
    WHERE is_distributor = 1
    IF (@loc_distributor is null)
        RETURN (1)
    -- set publisher if necessary
    -- process based on where this is invoked
    SELECT @publisher = case when (@publisher IS NULL) then upper(publishingservername()) else upper(@publisher) end
    if @loc_distributor = UPPER(@@servername)
        -- invoked on distributor
        -- validate the calling publisher
        SELECT @loc_distribdb = msd.distribution_db,
                @loc_publishertype = msd.publisher_type,
                @loc_publisherid = ss.server_id,
                @loc_working_directory = msd.working_directory,
                @loc_version = @@microsoftversion
        FROM msdb.dbo.MSdistpublishers as msd join sys.servers as ss
            ON msd.name = UPPER(ss.name collate database_default )
                AND msd.name = @publisher
        IF (@@ERROR != 0) -- OR @loc_distribdb IS NULL
            RETURN (1)
        -- invoked on publisher with remote distributor
        SELECT @proc = @dist_rpcname + N'.master.sys.sp_MSrepl_getdistributorinfo'
        EXECUTE @retcode = @proc @distributor  = @loc_distributor OUTPUT,
                                 @distribdb      = @loc_distribdb OUTPUT,
                                 @publisher      = @publisher,
                                 @local          = @local,
                                 @publisher_type = @loc_publishertype OUTPUT,
                                 @publisher_id   = @loc_publisherid OUTPUT,
                                 @working_directory = @loc_working_directory OUTPUT,
                                 @version = @loc_version OUTPUT
        IF @retcode <> 0 or @@ERROR <> 0
        RETURN (1)
    -- Return output parameters
    -- NOTE: For HREPL, treat the RPC name as the same as the distributor name
    -- to avoid using RPC calls for HREPL since all HREPL is local
    SELECT  @distributor = @loc_distributor,
                @distribdb = @loc_distribdb,
                @publisher_type = @loc_publishertype,
                @publisher_id = @loc_publisherid,
                @working_directory = @loc_working_directory,
                @version = @loc_version,
                -- Remote distributor or local with non-sysadmin rights
                @rpcsrvname = @loc_rpcsrvname
    -- BUGBUG : The value for @rpcsrvname must match the value returned
    --  by sp_helpdistributor or we will see indefinite blocking
    --  in some areas of our code. Example-incremental add article.
    --  So if you make a change here make it in sp_helpdistributor
    -- For the following cases use LOCAL SERVER NAME
    --  1. Hetero will always use local server
    --  2. Local distributor with sysadmin access (used to avoid blocking issues)
    if (@loc_publishertype != N'MSSQLSERVER'
        OR (IS_SRVROLEMEMBER('sysadmin') = 1
            AND @loc_distributor = UPPER(@@SERVERNAME)))
        select @rpcsrvname = name
        from sys.servers
        where UPPER(name collate database_default ) = @loc_distributor
    -- all done
    return 0

Last revision 2008RTM
See also

  sp_addmergepublication (Procedure)
sp_addqreader_agent (Procedure)
sp_addsubscriber_schedule (Procedure)
sp_changedistpublisher (Procedure)
sp_changeqreader_agent (Procedure)
sp_changereplicationserverpasswords (Procedure)
sp_changesubscriptiondtsinfo (Procedure)
sp_createpalrole (Procedure)
sp_distcounters (Procedure)
sp_dropanonymousagent (Procedure)
sp_dropdistpublisher (Procedure)
sp_dropdistributor (Procedure)
sp_dsninfo (Procedure)
sp_enumdsn (Procedure)
sp_expired_subscription_cleanup (Procedure)
sp_grant_publication_access (Procedure)
sp_helpdistributor (Procedure)
sp_helpdynamicsnapshot_job (Procedure)
sp_helpqreader_agent (Procedure)
sp_help_publication_access (Procedure)
sp_IHgetversion (Procedure)
sp_MSaddmergedynamicsnapshotjob (Procedure)
sp_MSaddpub_snapshot (Procedure)
sp_MSadd_compensating_cmd (Procedure)
sp_MSadd_distribution_agent (Procedure)
sp_MSarticle_synctran_commands (Procedure)
sp_MScdc_tranrepl_check (Procedure)
sp_MSchangemergedynamicsnapshotjob (Procedure)
sp_MScheck_subscription (Procedure)
sp_MScreatemergedynamicsnapshot (Procedure)
sp_MSdisableautonosync (Procedure)
sp_MSdistributoravailable (Procedure)
sp_MSdrop_expired_mergesubscription90 (Procedure)
sp_MSdrop_expired_subscription (Procedure)
sp_MSdrop_replcom (Procedure)
sp_MSenableautonosync (Procedure)
sp_MSenumallpublications (Procedure)
sp_MSfix_6x_tasks (Procedure)
sp_MSget_agent_names (Procedure)
sp_MSget_synctran_commands (Procedure)
sp_MShelp_identity_property (Procedure)
sp_MSincrementpublicationminautonosynclsn (Procedure)
sp_MSisdistributionjobnamegenerated (Procedure)
sp_MSislogreaderjobnamegenerated (Procedure)
sp_MSissnapshotjobnamegenerated (Procedure)
sp_MSmerge_autoident_upgrade (Procedure)
sp_MSpublishdb (Procedure)
sp_MSpub_adjust_identity (Procedure)
sp_MSrefreshdynamicsnapshotlocations (Procedure)
sp_MSreinit_article (Procedure)
sp_MSreplagentjobexists (Procedure)
sp_MSreplcheck_pull (Procedure)
sp_MSrepl_addarticle (Procedure)
sp_MSrepl_addpublication (Procedure)
sp_MSrepl_addpushsubscription_agent (Procedure)
sp_MSrepl_addsubscriber (Procedure)
sp_MSrepl_addsubscription_article (Procedure)
sp_MSrepl_browsesnapshotfolder (Procedure)
sp_MSrepl_changearticle (Procedure)
sp_MSrepl_changepublication (Procedure)
sp_MSrepl_changesubscriber (Procedure)
sp_MSrepl_changesubscriber_schedule (Procedure)
sp_MSrepl_changesubstatus (Procedure)
sp_MSrepl_droparticle (Procedure)
sp_MSrepl_droppublication (Procedure)
sp_MSrepl_dropsubscriber (Procedure)
sp_MSrepl_dropsubscription (Procedure)
sp_MSrepl_enumarticlecolumninfo (Procedure)
sp_MSrepl_enumpublishertables (Procedure)
sp_MSrepl_enumtablecolumninfo (Procedure)
sp_MSrepl_FixPALRole (Procedure)
sp_MSrepl_FixTranPALRole (Procedure)
sp_MSrepl_getpublisherinfo (Procedure)
sp_MSrepl_gettype_mappings (Procedure)
sp_MSrepl_helpsubscriberinfo (Procedure)
sp_MSrepl_helpsubscription (Procedure)
sp_MSrepl_posttracertoken (Procedure)
sp_MSrepl_publicationsnapshotjobcontrol (Procedure)
sp_MSrepl_publicationsummary (Procedure)
sp_MSrevokeconnectreplication (Procedure)
sp_MSvalidate_distpublisher (Procedure)
sp_oledbinfo (Procedure)
sp_reinitsubscription (Procedure)
sp_replrestart (Procedure)
sp_revoke_publication_access (Procedure)
sp_vupgrade_updatingpublicationarticle (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash