Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_getdistributorinfo

  No additional text.


Syntax


-- 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
)
AS
BEGIN
    SET NOCOUNT ON
    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)
    begin
        
        -- 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)
    end
    else
    begin
        
        -- 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)
    end
    
    -- 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)))
    begin
        select @rpcsrvname = name
        from sys.servers
        where UPPER(name collate database_default ) = @loc_distributor
    end
    -- all done
    return 0
END

 
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)
       



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