Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MShelp_snapshot_agentid

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MShelp_snapshot_agentid
(
    @publisher_id       smallint,
    @publisher_db       sysname,
    @publication        sysname,
    @job_id             binary(16) = NULL,
    @dynamic_snapshot_location nvarchar(255) = NULL,
    @dynamic_filter_login sysname = NULL,
    @dynamic_filter_hostname sysname = NULL
)
AS
begin
    set nocount on
    declare @retcode int
                ,@publisher sysname
                ,@description nvarchar(255)
                ,@new_password nvarchar(524)

    
    -- security check
    -- only db_owner can execute this
    
    if (is_member ('db_owner') != 1)
    begin
        raiserror(14260, 16, -1)
        return (1)
    end
    if @dynamic_filter_login = N''
        select @dynamic_filter_login = NULL

    if @dynamic_filter_hostname = N''
        select @dynamic_filter_hostname = NULL

    if @dynamic_filter_login is NULL and @dynamic_filter_hostname is NULL
    begin
        -- Check if agent exists, if not and there is an 6.x tasks then create one
        if @publication is not null and @publication <> '' and not exists (select * from MSsnapshot_agents where
                publisher_id = @publisher_id and
                publisher_db = @publisher_db and
                publication = @publication and
                dynamic_filter_login is NULL  and
                dynamic_filter_hostname is NULL)
        begin
            -- Do it only if the agent name is valid. It will be the case if
            -- the agent is launched by SQL Server Agent
            if exists (select * from msdb.dbo.sysjobs_view where
                job_id = @job_id)
            begin
                select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id
                begin tran
                exec @retcode = sys.sp_MSadd_snapshot_agent
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @local_job = 1,
                    @job_existing = 1,
                    @snapshot_jobid = @job_id,
                    @internal = N'YUKON'
                if @@ERROR<> 0 or @retcode <> 0
                    goto UNDO

                -- Add a publication definition so it shows up in monitoring procs
                set @description = formatmessage(20555)
                exec @retcode = sys.sp_MSadd_publication
                    @publisher = @publisher,
                    @publisher_db = @publisher_db,
                    @publication = @publication,
                    @publication_type = 1,              -- Make all 6.x pubs transactional
                    @description = @description       -- 6.x publication description

                if @@ERROR<> 0 or @retcode <> 0
                    goto UNDO
                commit tran
            end
        end

        select id, name from MSsnapshot_agents  where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication and
            dynamic_filter_login is NULL and
            dynamic_filter_hostname is NULL
    end
    else
    begin
        if @publication is not null and
           @publication <> '' and
           not exists (select * from MSsnapshot_agents where
                        publisher_id = @publisher_id and
                        publisher_db = @publisher_db and
                        publication = @publication and
                        ((@dynamic_filter_login is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @dynamic_filter_login)  and
                        ((@dynamic_filter_hostname is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @dynamic_filter_hostname))
        begin
            -- add an agent for the dynamic snapshot
            declare @regular_snapshot_jobid uniqueidentifier
            declare @dynamic_jobname sysname
            declare @dynamic_jobid uniqueidentifier
            declare @dynamic_job_step_uid uniqueidentifier
            declare @local_job bit
            declare @publication_type int
            declare @profile_id int

            select @regular_snapshot_jobid = job_id,
                   @local_job = local_job,
                   @publication_type = publication_type,
                   @profile_id = profile_id from MSsnapshot_agents
                  where publisher_id = @publisher_id and
                        publisher_db = @publisher_db and
                        publication = @publication and
                        dynamic_filter_login is NULL and
                        dynamic_filter_hostname is NULL

            if (@local_job = 1)
            begin
                exec @retcode = sys.sp_MSadddynamicsnapshotjobatdistributor @regular_snapshot_jobid, @dynamic_filter_login, @dynamic_filter_hostname, @dynamic_snapshot_location, @dynamic_jobname output, @dynamic_jobid output, @dynamic_job_step_uid output



                if @retcode <> 0 or @@error <> 0
                    return 1
            end
            else
            begin
            	SELECT @new_password = newid()

				EXEC @retcode = sys.sp_MSreplencrypt @new_password OUTPUT
		    	IF @@error <> 0 or @retcode <> 0
		    		RETURN (1)

                if @publication_type is NULL
                begin
                    raiserror(20678, 16, -1)
                    return (1)
                end
                select @dynamic_jobname = N'No job yet'
        		INSERT INTO MSsnapshot_agents (name, publisher_id, publisher_db, publication, publication_type,
        								local_job, profile_id, dynamic_filter_login, dynamic_filter_hostname,
        								publisher_security_mode, publisher_login, publisher_password)
        			 VALUES (@dynamic_jobname,@publisher_id, @publisher_db, @publication, @publication_type,
        			            @local_job, @profile_id, @dynamic_filter_login, @dynamic_filter_hostname,
        			            1, NULL, @new_password)
                if @@error <> 0
                    return 1
            end
        end
        select id, name from MSsnapshot_agents  where
            publisher_id = @publisher_id and
            publisher_db = @publisher_db and
            publication = @publication and
            ((@dynamic_filter_login is NULL and dynamic_filter_login is NULL) or dynamic_filter_login = @dynamic_filter_login)  and
            ((@dynamic_filter_hostname is NULL and dynamic_filter_hostname is NULL) or dynamic_filter_hostname = @dynamic_filter_hostname)
    end

    return(0)

UNDO:
    if @@TRANCOUNT = 1
        ROLLBACK TRAN
    else
        COMMIT TRAN
    return(1)
end

 
Last revision 2008RTM
See also

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