Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.


CREATE PROCEDURE sys.sp_MSadd_merge_anonymous_agent
    @publisher_id       smallint,
    @publisher_db       sysname,
    @publication        sysname,
    @subscriber_db      sysname,
    @subscriber_name    sysname,
    @subid              uniqueidentifier,
    @first_anonymous	int,		-- 0 means this is the first time for this anonymous agent being ran.
    @subscriber_version int = 60, -- 60=shiloh sp3 or lower, 90=yukon
	@publisher_engine_edition int = null
    declare @min_valid_day  datetime
    declare @merge_type     int
    declare @profile_id     int
    declare @subscriber_id  smallint
    declare @agent_name     sysname
    declare @agent_id       int
    declare @retcode        int
    declare @publication_id int
    declare @not_exist      bit
    declare @last_status	int
    declare @last_history	datetime
    declare @merge_jobid	uniqueidentifier
    declare @by_pass		bit
    declare @retention		int
    declare @retention_period_unit tinyint
    declare @success		int
    declare @expired		int
    declare @dropped		int
    declare @allow_anonymous bit
    declare @publisher		sysname
    declare @subscriber_name_current sysname

    -- security check
    -- Has to be executed from distribution database
    -- PAL check is done after getting publication metadata
    if (sys.fn_MSrepl_isdistdb (db_name()) != 1)
        raiserror(21482, 16, -1, 'sp_MSadd_merge_anonymous_agent', 'distribution')
        return (1)

    select @dropped = 0
    select @expired = 0
    select @success = 2
    select @by_pass = 0

    ** This stored procedure does not really add a job at distribution database;
    ** if add a row in dbo.MSmerge_agent table for anonymous subscription for the
    ** purpose of history logging

    -- Check to see if the publication is valid and allows anonymous subscribers
    select @publication_id = publication_id, @allow_anonymous = allow_anonymous, @retention = retention,
            @retention_period_unit = retention_period_unit
    from dbo.MSpublications where
        publisher_id = @publisher_id and
        publisher_db = @publisher_db and
        publication = @publication

    if @publication_id is null
        RAISERROR (21040, 16, -1, @publication)
        return 1

    select @publisher = srvname from master.dbo.sysservers where srvid = @publisher_id

    if @allow_anonymous = 0
        RAISERROR (21084, 16, -1, @publication)
        return 1

    if @subscriber_name is null
        select @subscriber_name = N''

    if @retention is NULL or @retention =0
        select @by_pass = 1

    -- PAL Security check
    exec @retcode = sys.sp_MScheck_pull_access
        @publication_id = @publication_id, @agent_type = 1
    if @retcode <> 0 or @@error <> 0
        return (1)

    select @not_exist = 0
    SELECT @merge_type = 4
    select @subscriber_id = 0  -- For anonymous subscribers, ID is always 0

    SELECT @profile_id = profile_id
    FROM msdb..MSagent_profiles
    WHERE agent_type = @merge_type
        AND def_profile = 1

    IF @profile_id IS NULL
        RETURN (1)

	** This is to handle Jet only
    IF @subid = '00000000-0000-0000-0000-000000000000'
    	select @subid = anonymous_subid from dbo.MSmerge_agents
    			where publisher_id=@publisher_id and
    				  publisher_db = @publisher_db and
    				  publication = @publication and
    				  subscriber_name = @subscriber_name
    				  and subscriber_db = @subscriber_db
    	if @subid = '00000000-0000-0000-0000-000000000000'
    		select @subid = newid()
    		select @first_anonymous = 1   -- for Jet, schemaversion should not be 0 in this path.

    IF NOT EXISTS (select * from dbo.MSmerge_agents where anonymous_subid=@subid)

    	if @first_anonymous <= 0   --only add agent entry for initial subscription only.
        	select @not_exist = 1
        	begin tran
        	exec @retcode = sys.sp_MScheck_subscription_count_internal @mode = 1, @publisher = @publisher, @publisher_engine_edition = @publisher_engine_edition,	
			if @@error <> 0 or @retcode <> 0
				if @@trancount > 0
					commit tran
				return 1
        	-- Generate a job GUID for remote agents. This will be used by the UI to uniquely
        	-- identify rows returned by the enums
        	set @merge_jobid = newid();

        	insert into dbo.MSmerge_agents (name, publisher_id, publisher_db, publication,
            		    subscriber_id, subscriber_db, anonymous_subid, job_id, profile_id, subscriber_name)
	            	VALUES (convert(nvarchar(40), @subid), @publisher_id, @publisher_db, @publication,
    	            	    @subscriber_id, @subscriber_db, @subid, @merge_jobid, @profile_id, @subscriber_name)
    	    commit tran
    		select @dropped  =1

    select @agent_id = id, @agent_name = name, @subscriber_name_current = subscriber_name from dbo.MSmerge_agents
        where anonymous_subid=@subid  -- subid guarantees uniqueness

    if @subscriber_name <> @subscriber_name_current and  @subscriber_name is not null and @subscriber_name <> N''
		-- Due to a bug in the upgrade code in yukon RTM that has been fixed in SP2 the subscriber_name column of MSmerge_agents may not be correct for anonymous
		-- subscribers if the database has been upgraded from shiloh to pre yukon SP2 build. Running the following query to do the fixup.
		update dbo.MSmerge_agents set subscriber_name = @subscriber_name
	        where anonymous_subid=@subid  -- subid guarantees uniqueness

	if @by_pass = 0 and @subscriber_version < 90 --by pass the checking if retention is NULL or 0 or if yukon
    	select @min_valid_day = sys.fn_add_units_to_date(-@retention, @retention_period_unit, getdate())

    	-- only do history based expiration if there is a valid successful run information available.    	
        -- If we do not find the history for the last successful run we will not cleanup.
        -- however a seperate cleanup happens that uses the last_sync_time on the publisher. So
        -- even if we do not cleanup here sp_MSdrop_expired_mergesubscription will take care of it
    	select Top 1 @last_status = runstatus, @last_history = end_time from dbo.MSmerge_sessions where agent_id = @agent_id and runstatus=2
        			order by session_id DESC

    	** This anonymous subscription is gone for too long to be efficiently reconciled. Either reinitialization or
    	** re-deployment of this subscription is needed. Merge agent will fail.
    	if @last_history is not NULL
        	if @last_history < @min_valid_day and @first_anonymous <> 0	--do not check for re-initialized replicas.
                select @expired = 1

    select @agent_id, @agent_name, @expired where @dropped = 0 --return empty result set

Last revision 2008RTM
See also

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