Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSadd_logreader_agent

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_MSadd_logreader_agent
(
    @name						nvarchar(100) = NULL,
    @publisher					sysname,
    @publisher_db				sysname,
    @publication				sysname, -- Only used by 3rd party publisher
    @local_job					bit,
    @job_existing				bit = 0,
    @job_id						binary(16) = NULL,
    @publisher_security_mode 	smallint = NULL,
    @publisher_login			sysname = NULL,
    @publisher_password 		nvarchar(524) = NULL,
    @job_login					nvarchar(257) = NULL,
    @job_password				sysname = NULL,
    @publisher_type			sysname = N'MSSQLSERVER',
    @internal					sysname = N'PRE-YUKON', -- Can be: 'PRE-YUKON', 'YUKON', 'BOTH'
    @publisher_engine_edition int = NULL
)
AS
BEGIN
    SET NOCOUNT ON

    /*
    ** Declarations.
    */
    DECLARE @retcode            int
                ,@agent_args         nvarchar(255)
                ,@publisher_id       smallint
                ,@loc_publisher_db	sysname
                ,@profile_id         int
                ,@logreader_type     int
                ,@databasename       sysname
                ,@agent_id           int
                ,@category_name      sysname
                ,@platform_nt binary
                ,@srvproduct nvarchar(128)
                ,@datasource nvarchar(4000)	
                ,@job_step_uid		uniqueidentifier

    -- Security Check: require sysadmin
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN 1
    END
    
    -- security check
    -- Has to be executed from distribution database
    
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
    begin
        raiserror(21482, 16, -1, 'sp_MSadd_logreader_agent', 'distribution')
        return (1)
    end

    /*
    ** Initializations
    */
    select @platform_nt = 0x1,
    		@job_step_uid = NULL

    select @publisher_id = srvid, @srvproduct = srvproduct, @datasource = datasource from master.dbo.sysservers where
        UPPER(srvname) = UPPER(@publisher)

	-- ONLY ALLOW THIS IN 8.0 or less CASE
	IF @internal = N'PRE-YUKON'
	BEGIN
		-- if @name is not null and @job_existing = 1, the proc is from DMO scripting
		-- check to see if the job is there are not, if not, reset @job_existing and
		-- @name values. This is for the case when the user generate the script at
		-- the publisher but did not re-create repl jobs at the distributor.
		if @local_job = 1 and @job_existing = 1 and @name is not null and
	       @name <> N''
		begin
			if not exists (select * from msdb.dbo.sysjobs_view
				where name = @name
				and UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
				and master_server = 0)
			begin
				set @job_existing = 0
				set @name = null
			end
		end
	END
	
   	-- For naming purposes, use @publisher instead of @publisher_db for HREPL
   	-- publishers that don't support publisher db notion
    IF @publisher_type LIKE N'ORACLE%'
    BEGIN
    	SELECT @loc_publisher_db = @publisher
    END
    ELSE
    BEGIN
    	SELECT @loc_publisher_db = @publisher_db
    END

	IF NOT @publisher_type = N'MSSQLSERVER'
	BEGIN
		-- in the heter case, if user specified integrated security
		-- for the publisher security at sp_adddistpublisher time then
		-- the provided logreader agent job_login must be the same...
		IF EXISTS(SELECT *
						FROM msdb..MSdistpublishers
						WHERE distribution_db = DB_NAME()
							AND name = @publisher
							AND login != @job_login
							AND security_mode = 1
							AND publisher_type = @publisher_type)
		BEGIN
			-- The job_login provided must match the publisher login specified when adding the distribution publisher (sp_adddistpublisher).
			RAISERROR(22537, 16, -1)
			RETURN 1
		END
	END
	
    BEGIN TRAN

    -- If creating locally, try to drop it first
    IF @local_job = 1 and @job_existing = 0
    begin
        EXEC sys.sp_MSdrop_logreader_agent
       @publisher = @publisher,
            @publisher_db = @publisher_db,
            @publication = @publication
        IF @@ERROR <> 0
            GOTO UNDO
    end

    /* Code for log reader agent type in MSagent_profiles */
    SELECT @logreader_type = 2

    -- Get the default profile ID for the logreader agent type. If a third party publication
    -- no profile is used.
    if exists (select * from dbo.MSpublications where
                publisher_id = @publisher_id and
                publisher_db = @publisher_db and
                publication = @publication and
                thirdparty_flag = 1)
    begin
        set @profile_id = 0
    end
    else
    begin
        SELECT @profile_id = profile_id FROM msdb..MSagent_profiles WHERE
            agent_type = @logreader_type and
            def_profile = 1
    end

	-- if the publisher sec info was not provided then default
	-- the vals to the values provided durring sp_adddistpublisher...
	-- this is only possible when called by SYSADMIN or 8.0 pub.
	IF @publisher_security_mode is NULL
	BEGIN
		select @publisher_security_mode = security_mode,
				@publisher_login = login,
				@publisher_password = password
			from msdb.dbo.MSdistpublishers
			where UPPER(name) = UPPER(@publisher)
				and distribution_db = db_name()
				and publisher_type = @publisher_type
	END
	ELSE
	BEGIN
		-- if WINDOWS authentication then clear out the login/password
		IF @publisher_security_mode = 1
	    BEGIN
			select @publisher_login = '',
					@publisher_password = newid()
	    END

		-- Encrypt the password before storing
	    EXEC @retcode = sys.sp_MSreplencrypt @publisher_password OUTPUT
		IF @@ERROR <> 0 OR  @retcode <> 0
	        GOTO UNDO
	END

    -- If publisher_id, publisher_db pair is not in MSpublisher_databases then add it.  This will be used
    -- to store a publisher_database_id in the MSrepl_transactions and MSrepl_commands table.
    IF NOT EXISTS (select *
                        from MSpublisher_databases
                        where publisher_id = @publisher_id
                            and publisher_db = @publisher_db)
    BEGIN
        INSERT INTO MSpublisher_databases (publisher_id, publisher_db, publisher_engine_edition)
                VALUES (@publisher_id, @publisher_db, @publisher_engine_edition)
        IF @@ERROR <> 0
            GOTO UNDO

        INSERT INTO MSrepl_backup_lsns (publisher_database_id) VALUES (@@identity)
        IF @@ERROR <> 0
            GOTO UNDO
    END

    /*
    ** Insert row
    */
    INSERT INTO MSlogreader_agents (name, publisher_id, publisher_db, publication,
                    local_job, profile_id,
                    publisher_security_mode, publisher_login, publisher_password)
         VALUES ('',@publisher_id, @publisher_db, @publication,
                    @local_job, @profile_id,
                    @publisher_security_mode, @publisher_login, @publisher_password)
    IF @@ERROR <> 0
        GOTO UNDO

    set @agent_id = @@IDENTITY

    DECLARE @name_is_generated bit
    SELECT @name_is_generated = 0
    IF @name IS NULL OR @name = N''
    BEGIN
        SELECT @name_is_generated = 1
                ,@name = CONVERT(nvarchar(43),@publisher ) + '-' + CONVERT(nvarchar(43),@loc_publisher_db) + '-' + CONVERT(nvarchar, @@IDENTITY)
    END

    -- If the generated name already exists, re-generate the name with a
    -- guid appended
    IF @name_is_generated = 1
    BEGIN
        IF EXISTS (SELECT * FROM msdb.dbo.sysjobs_view
                    WHERE name = @name
                      AND UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
                      and master_server = 0)
        BEGIN
            SELECT @name = fn_repluniquename(newid(), @publisher, @loc_publisher_db, null, null)
        END
    END

    -- Add Perfmon instance
    dbcc addinstance ("SQL Replication Logreader", @name)

    IF @local_job = 1 and @job_existing = 0
    BEGIN
        SELECT @agent_args = '-Publisher ' + QUOTENAME(@publisher)

        IF @publisher_type = N'MSSQLSERVER'
        BEGIN
            SELECT @agent_args = @agent_args + ' -PublisherDB ' + QUOTENAME(@publisher_db)
        END

        SELECT @agent_args = @agent_args + ' -Distributor ' + QUOTENAME(@@SERVERNAME)

        -- Always use integrated security for local connections
        select @agent_args = @agent_args + ' -DistributorSecurityMode 1 '

        DECLARE @nullchar nchar(20)
        SELECT @nullchar = NULL

        set @databasename = db_name()
        -- Get Logreader category name (assumes category_id = 13)
        select @category_name = name FROM msdb.dbo.syscategories where category_id = 13

        EXECUTE @retcode = dbo.sp_MSadd_repl_job
        @name = @name,
        @subsystem = 'LogReader',
        @server = @publisher,
        @databasename = @databasename,
        @enabled = 1,
        @freqtype = 64,       /* Auto-Start */
        @freqinterval                   = 1,
        @freqsubtype                    = 1,
        @freqsubinterval                = 1,
        @freqrelativeinterval= 1,
        @freqrecurrencefactor   = 1,
        @activestartdate                = 0,
        @activeenddate                  = 0,
        @activestarttimeofday   = 0,
        @activeendtimeofday     = 0,
        @nextrundate                    = 12355,
        @nextruntime                    = 13423,
        @runpriority                    = 0,
        @emailoperatorname              = @nullchar,
        @retryattempts = 10,
        @retrydelay = 1,
        @command = @agent_args,
        @loghistcompletionlevel = 0,
        @category_name = @category_name,
        @failure_detection = 1,
        @agent_id = @agent_id,
        @job_login = @job_login,
        @job_password = @job_password,
        @job_id = @job_id OUTPUT,
        @job_step_uid = @job_step_uid OUTPUT

       IF @@ERROR <> 0 or @retcode <> 0
            GOTO UNDO
    END

    if @local_job = 1 and @job_existing = 1
    begin
		if @job_id is null
		begin
			select @job_id = sjv.job_id
			from msdb.dbo.sysjobs_view as sjv
				join msdb.dbo.sysjobsteps as sjs
					on sjv.job_id = sjs.job_id
			where sjv.name = @name
				and sjv.master_server = 0
				and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
				and sjv.category_id = 13
				and sjs.subsystem = N'LogReader'
				and sjs.database_name = db_name()
			
			if @job_id IS NULL
			begin
				-- Message from msdb.dbo.sp_verify_job_identifiers
				RAISERROR(14262, -1, -1, 'Logreader Job', @name)
				GOTO UNDO
			end
		end
		else
		begin
			if not exists (select * from msdb.dbo.sysjobs_view
				where job_id = @job_id
				and UPPER(originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
				and master_server = 0)
			begin
				-- Message from msdb.dbo.sp_verify_job_identifiers
				RAISERROR(14262, -1, -1, 'Job', @name)
				GOTO UNDO
			end
		end
    end

	-- retrieve the job step uid
	if @job_id is NOT NULL
		and @job_step_uid is NULL
	begin
		select @job_step_uid = sjs.step_uid
			from msdb.dbo.sysjobs_view as sjv
				join msdb.dbo.sysjobsteps as sjs
					on sjv.job_id = sjs.job_id
			where sjv.job_id = @job_id
				and sjv.master_server = 0
				and UPPER(sjv.originating_server) = UPPER(CONVERT(sysname, SERVERPROPERTY('ServerName')))
				and sjv.category_id = 13
				and sjs.subsystem = N'LogReader'
				and sjs.database_name = db_name()
	end

    -- Generate a job GUID for remote agents. This will be used by the UI to uniquely
    -- identify rows returned by the enums
    if @local_job = 0
    begin
		-- Third party publication will pass in logreader agent name which is created as
		-- a SQLServerAgent job.
		if @name is not null
			select @job_id = job_id from msdb.dbo.sysjobs_view where
				name = @name
		if @job_id is null
			set @job_id = newid()
		-- Reset @local_job to 1 so that repl monitor can start the job.
		-- In sp_MSdrop_logreader_agent, we will not drop the job if the publication
		-- is from third party.
		else
			set @local_job = 1
    end

    UPDATE MSlogreader_agents
    SET name = @name,
            job_id = @job_id,
            job_step_uid = @job_step_uid
    WHERE id = @agent_id
    IF @@ERROR <> 0
        GOTO UNDO
	
    COMMIT TRAN

    RETURN(0)

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

 
Last revision 2008RTM
See also

  sp_addtask (Procedure)
sp_instdist (Procedure)
sp_MSfix_6x_tasks (Procedure)
sp_MSislogreaderjobnamegenerated (Procedure)
sp_MSrepl_addlogreader_agent (Procedure)
sp_MSrepl_addpublication (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