Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSget_shared_agent

  No additional text.


Syntax
create procedure sys.sp_MSget_shared_agent
(
    @server_name    sysname,
    @database_name    sysname,
    @agent_type        int,
    @publisher        sysname = NULL,
    @publisher_db    sysname = NULL
)
as
begin
    DECLARE @retcode    int

    SET @retcode = 0

    
    -- security check
    -- only db_owner and replmonitor can execute this
    
    if not ((is_member ('db_owner') = 1) or (isnull(is_member('replmonitor'),0) = 1))
    begin
        raiserror(14260, 16, -1)
        return (1)
    end

    -- push agents to SQL publications
    if @agent_type = 0
    begin
        if object_id('syssubscriptions') is not null
        begin
            DECLARE @publisher_type sysname

            EXEC @retcode = sys.sp_MSrepl_getpublisherinfo	@publisher      = @publisher,
															@publisher_type = @publisher_type OUTPUT

            IF @retcode <> 0
                RETURN (@retcode)

            select	top 1
                    distribution_jobid AS N'job_id'
            from    syssubscriptions s,
                    sysarticles a,
                    syspublications p
            where   s.srvname = UPPER(@server_name)
              and   s.dest_db            = @database_name
              and   s.subscription_type  = 0
              and   p.independent_agent  = 0
              and   s.artid              = a.artid
              and   p.pubid              = a.pubid
              and   p.pubid IN (SELECT * FROM sys.fn_IHgetpubid(p.name, @publisher, @publisher_type))
        end
    end
    -- push agents to third party
    else if @agent_type = 1
    begin
        if object_id('MSsubscriptions') is not null
        begin
            select    top 1 job_id AS N'job_id'
            from    MSdistribution_agents da,
                    master.dbo.sysservers spub,
                    master.dbo.sysservers ssub
            where    da.subscriber_id        = ssub.srvid
              and    da.subscriber_db        = @database_name
              and    da.publication            = N'ALL'
              and    da.publisher_id            = spub.srvid
              and    da.publisher_db            = @publisher_db
              and    da.subscription_type    = 0
              and    UPPER(spub.srvname)        = UPPER(@publisher) collate database_default
              and    UPPER(ssub.srvname)        = UPPER(@server_name) collate database_default
        end
    end
    else if @agent_type = 2
    begin
        if object_id('MSreplication_subscriptions') is not null
        begin
            -- For subscriptions added through Active X control
            -- where there's no local agent created, we still don't
            -- want to create new agent.
            select    top 1 agent_id as N'job_id'
            from    MSreplication_subscriptions
            where    UPPER(publisher)    = UPPER(@server_name)
              and    publisher_db        = @database_name
              and    independent_agent    = 0
              and    subscription_type in (1,2)
        end
    end

    RETURN (@retcode)
end

 
Last revision 2008RTM
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