Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSrepl_refresh_heterogeneous_publisher

  No additional text.


Syntax

create procedure sys.sp_MSrepl_refresh_heterogeneous_publisher
(
    @publisher					sysname,
    @publisher_type				sysname
)
as
begin
    set nocount on

	declare @retcode int
	declare @cmd nvarchar(4000)
 	declare @article sysname
 	declare @filter_clause nvarchar(4000)
	declare @publisher_id int
    declare @distributortimestamp datetime
    declare @publisher_guid uniqueidentifier
    declare @distribution_db sysname
    declare @security_mode int
    declare @login sysname
    declare @password nvarchar(524)
    declare @xact_seqno varbinary(16)
    declare @OptimizeTrigger bit
	declare @RecreateTriggers bit
    declare @XCALL bit
	declare @artcnt int
	declare @owner sysname
	declare @table_name sysname
	declare @table_id int
	declare @article_view nvarchar(255)
	declare @columns binary(128)
	declare @publishedcolumns binary(128)
	declare @repl_freq int
	declare @instance_id int
	declare @article_id int
	declare @publication_id int
	declare @upd_cmd nvarchar(255)
	declare @del_cmd nvarchar(255)
	declare @user_name sysname
	declare @type tinyint
	declare @status tinyint
	declare @version sysname

    -- Security Check: require sysadmin
    if (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    begin
        raiserror(21089,16,-1)
        raiserror(21610,16,-1,@publisher)
        return (1)
    end

    -- Query msdb for current publisher information.
    select @distribution_db = distribution_db,
           @security_mode = security_mode,
           @login = login,
           @password = password
    from msdb.dbo.MSdistpublishers
	where UPPER(name collate database_default) = UPPER(@publisher)
      and distribution_db = DB_NAME()
    if @@error <> 0
    begin
        raiserror(21607,16,-1)
        raiserror(21610,16,-1,@publisher)
        return (1)
    end

    exec @retcode = sys.sp_MSrepldecrypt @password output
    if @@error <> 0 OR @retcode <> 0
    begin
        raiserror(21607,16,-1)
        raiserror(21610,16,-1,@publisher)
        return (1)
    end

    -- Obtain Publisher's server id
    select @publisher_id = srvid
    from master.dbo.sysservers
    where upper(srvname collate database_default) = upper(@publisher) collate database_default
    if @@error <> 0
    begin
        raiserror(21607,16,-1)
        raiserror(21610,16,-1,@publisher)
        return (1)
    end

    -- Obtain the last committed transaction for the publisher from MSrepl_transactions
    set @xact_seqno = NULL
    set @xact_seqno = (
        select TOP 1 xact_seqno
        from MSrepl_transactions t, MSpublisher_databases d
        where t.publisher_database_id = d.id
          and d.publisher_id = @publisher_id
        order by xact_seqno DESC)
    if @@error <> 0
    begin
        raiserror(21607,16,-1)
        raiserror(21610,16,-1,@publisher)
        return (1)
    end

    -- Setup the Oracle publisher with current code.
    set @distributortimestamp = GETDATE()
    set @publisher_guid = NEWID()

    exec @retcode = sys.sp_ORAaddpublisher
						@publisher		= @publisher,
						@dist_db		= @distribution_db,
						@security_mode	= @security_mode,
						@remotelogin	= @login,
						@remotepassword	= @password,
						@distributortimestamp	= @distributortimestamp,
						@publisher_guid	= @publisher_guid

    if @@error <> 0 OR @retcode <> 0
    begin
        raiserror(21603,16,-1,@publisher)
        return (1)
    end

    if @xact_seqno IS NULL
    begin
        set @xact_seqno = 0x00000000000000000000
    end

    -- Define remote query support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	--  Update publisher entry in HREPL_Publisher to reflect current xact_seqno.
	insert into #hquery (cmd) values (N'UPDATE HREPL_PUBLISHER SET PUBLISHER_LSN = ')
	insert into #hquery (cmd) values (N'''' + RIGHT(LEFT(sys.fn_varbintohexstr(@xact_seqno), 22), 20) + N'''')
	
	exec @retcode = sys.sp_IHquery @publisher
    if @@error <> 0 OR @retcode <> 0
	begin
		raiserror (21625, 16, -1, @publisher)
		raiserror (21603, 16, -1, @publisher)
		return (1)
	end
	
	drop table #hquery
	
    -- Call sp_ORAaddarticle for each published article to create meta data entries
    -- for the articles at the publisher and to generate triggers and article log tables.
	declare #hArticles cursor local FAST_FORWARD for
        -- Get the article information.
        select iha.name,	
			iha.article_id,
			iha.publication_id,
			ihpt.name,
			ihpt.owner,
			iha.type,
			iha.status,
			iha.table_id,
			iha.article_view,
			iha.upd_cmd,
			iha.del_cmd,
			iha.instance_id,
			iha.filter_clause			
        from	IHarticles iha,
    		IHpublishertables ihpt
        where	iha.table_id		= ihpt.table_id
          and	iha.publisher_id	= ihpt.publisher_id
          and	ihpt.publisher_id	= @publisher_id

	open #hArticles

	fetch #hArticles into @article,
			@article_id,
			@publication_id,
			@table_name,
			@user_name,
			@type,
			@status,
			@table_id,
			@article_view,
			@upd_cmd,
			@del_cmd,
			@instance_id,
			@filter_clause		

	while @@FETCH_STATUS != -1
	begin
	
        select @repl_freq = repl_freq
        from IHpublications
        where pubid = @publication_id
	
        -- Generate bitmap of ordinal columns
        execute @columns = sys.fn_IHarticle_columns
        	@article_id,
        	@table_id,
        	@publisher_id
        if @@error <> 0
        begin
        	raiserror (21775, 11, -1, @article)
        	raiserror (21603, 16, -1, @publisher)
        	return (1)
        end

        if (@publisher_type = N'ORACLE') AND (@repl_freq = 0)
        begin
        	-- If this is an ORACLE publisher and the article belongs to a transactional publication,
        	-- Generate the bitmap of published columns to be used to generate the trigger and article
        	-- log table
        	execute @publishedcolumns = sys.fn_IHpublished_columns
        	    @table_id,
        	    @publisher_id
        	if @@error <> 0
        	begin
        	    raiserror (21776, 11, -1, @article)
        	    raiserror (21603, 16, -1, @publisher)
        	    return (1)
        	end
        end
        else
        begin
        	-- If this is an ORACLE GATEWAY publisher or the article belongs to a snapshot publication,
        	-- set the publish column bitmask to the article columns bitmask.
        	select @publishedcolumns = @columns
        end
		
        set @OptimizeTrigger = 0
        set @RecreateTriggers = 0
        set @XCALL = 0

        if @repl_freq = 0
        begin
        	-- If publisher type is ORACLE GATEWAY, for transactional publications,
        	-- always regenerate triggers, even if they already exist, and generate
        	-- with optimal performance
        	if @publisher_type = N'ORACLE GATEWAY'
        	begin
        	    set @OptimizeTrigger = 1
        	    set @RecreateTriggers = 1
        	    if upper(@upd_cmd) like 'XCALL%' or
        	       upper(@del_cmd) like 'XCALL%' or
        	       -- all filtered columns must be captured in the trigger and placed
        	       -- in the article log if a filter clause is specified for the article
        	       DATALENGTH(@filter_clause) > 0	
        	       set @XCALL = 1
        	end	
        end	

        -- Create the publisher objects for the article
        execute @retcode = sys.sp_ORAaddarticle
        	@owner             = @user_name
        	,@tablename         = @table_name
        	,@publisher         = @publisher
        	,@table_id          = @table_id
        	,@OptimizeTrigger   = @OptimizeTrigger
        	,@RecreateTriggers  = @RecreateTriggers
        	,@XCALL             = @XCALL				
        	,@article_view      = @article_view
        	,@columns           = @columns
        	,@publishedcolumns  = @publishedcolumns
        	,@repl_freq         = @repl_freq
        	,@filter_clause     = @filter_clause
        	,@distributortimestamp = @distributortimestamp	
        	,@instance_id	   = @instance_id	

        if @@error <> 0 OR @retcode <> 0
        begin
        	raiserror (21778, 16, -1, @article)
        	raiserror (21603, 16, -1, @publisher)
        	return (1)
        end

        fetch #hArticles into @article,
			@article_id,
			@publication_id,
			@table_name,
			@user_name,
			@type,
			@status,
			@table_id,
			@article_view,
			@upd_cmd,
			@del_cmd,
			@instance_id,
			@filter_clause		
    end

    close #hArticles
    deallocate #hArticles

    -- Set the SQL version in the Oracle package
    execute @retcode = sys.sp_setOraclepackageversion @publisher = @publisher
    if @@error <> 0 or @retcode <> 0
    begin
        raiserror (21603, 16, -1, @publisher)
        return(1)
    end

    -- Force the publisher version in IHpublishers to be refreshed from the remote server
    UPDATE IHpublishers
    SET version = NULL
    WHERE publisher_id = @publisher_id

    EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
		                        @version	= @version OUTPUT
	
    return(0)

end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_refresh_heterogeneous_publisher (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