Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSarticle_synctran_commands

  No additional text.


Syntax
create procedure sys.sp_MSarticle_synctran_commands(
		@publication sysname    /* publication name */,
		@article sysname			/* individual article name, not all*/,
		@command_only bit = 0,   /* 0 if called by snapshot agent, 1 if called by sp_script_..., */
		@publisher sysname = NULL,
		@publisher_db sysname = NULL,
		@distributor sysname = NULL,
		@has_ts bit = 0, -- base table has timestamp column
		@has_ident bit = 0, -- base table has identity column
		@alter bit = 0, -- if 1 script alter, otherwise script create
        @trig_only bit = 0, -- if 1 we only return the trigger generation command
        @usesqlclr bit = 0
) AS
BEGIN
    SET NOCOUNT ON

DECLARE @retcode int
-- The only case where @command_only = 0 is needed is in
-- sp_MSget_synctran_commands, but if sqlclr is used in
-- sp_MSget_synctran_commands for scripting the synctran commands,
-- sp_MSarticle_synctran_commands is not called at all
if @usesqlclr = 1 and @command_only = 1
begin
    exec @retcode = sys.sp_MSscriptsynctrancommands_sqlclr
        @publication = @publication
        ,@article = @article
        ,@publisher = @publisher
        ,@command_only = @command_only
        ,@alter = @alter
        ,@trig_only = @trig_only
    if (@@ERROR != 0 OR @retcode != 0)
	begin
	    RETURN (1)
	end
end
else
begin
DECLARE @artid int
        ,@tabid int
        ,@filter_clause nvarchar(4000)
        ,@pubid int
        ,@dest_table sysname
        ,@dest_owner sysname
        ,@proc_owner sysname

        ,@ts_col sysname
        ,@replcmd nvarchar(max)
        ,@insproc sysname
        ,@updproc sysname
        ,@delproc sysname
        ,@cftproc sysname
        ,@cft_table sysname
        ,@is_synctran bit
        ,@is_queued bit
        ,@identity_col sysname
        ,@identity_support bit
        ,@independent_agent bit
        ,@ver_check_cmd nvarchar(4000)
        ,@f_refresh_columns bit

    select @insproc = null
            ,@updproc = null
            ,@delproc = null
            ,@ts_col = NULL
            ,@identity_col = NULL
            ,@identity_support = 0
            ,@f_refresh_columns = 0

    
    -- @command_only=1 is only called by DDL, in which case we want to output the scripting code
    --otherwise called by sp_MSget_synctran_commands, in which case we just want to populate #art_commands here
    
    if (@command_only = 1)
        create table #art_commands (artid int NOT NULL, commands nvarchar(max) collate database_default null, id int identity NOT NULL)
    SELECT @artid = art.artid
                ,@tabid = art.objid
                ,@dest_table = art.dest_table
                ,@dest_owner = ISNULL(art.dest_owner, N'null')
                ,@filter_clause = case art.filter
                                            when 0 then N'null'
                                            else case art.type & 0x3
                                                    when 0x3 then N'null'
                                                        else RTRIM(LTRIM(CONVERT(nvarchar(4000), filter_clause)))
                                                    end
                                            end
                ,@pubid = pub.pubid
                ,@independent_agent = independent_agent
                ,@is_synctran = allow_sync_tran
                ,@is_queued = allow_queued_tran
                ,@proc_owner = schema_name(objectproperty(sync_ins_proc, 'SchemaId'))
                ,@insproc = object_name(sync_ins_proc)
                ,@updproc = object_name(sync_upd_proc)
                ,@delproc = object_name(sync_del_proc)
                ,@cftproc = object_name(ins_conflict_proc)
                ,@cft_table = object_name(conflict_tableid)
                ,@identity_support = identity_support
    FROM sysarticles art join syspublications pub
                on pub.pubid = art.pubid
        join sysarticleupdates upd
            on art.artid = upd.artid and upd.pubid = art.pubid
    WHERE pub.name = @publication
        AND (art.type & 0x1) = 1
        AND (art.name = @article)

    if @insproc IS NULL
    begin
        RAISERROR (14043, 11, -1, '@insproc', 'sp_MSarticle_synctran_commands')
        RETURN (1)
    end
    if @updproc IS NULL
    begin
        RAISERROR (14043, 11, -1, '@updproc', 'sp_MSarticle_synctran_commands')
        RETURN (1)
    end
    if @delproc IS NULL
    begin
        RAISERROR (14043, 11, -1, '@delproc', 'sp_MSarticle_synctran_commands')
        RETURN (1)
    end
    if (@is_queued = 1 and @cftproc IS NULL)
    begin
        RAISERROR (14043, 11, -1, '@cftproc', 'sp_MSarticle_synctran_commands')
        RETURN (1)
    end
    SET @publisher = UPPER(@publisher) COLLATE DATABASE_DEFAULT

    IF @publisher IS NULL
    BEGIN
        set @publisher = publishingservername()
    END
    -- Construct ver check cmd
    -- Construct message.
    IF (@trig_only = 0)
    BEGIN
        select @ver_check_cmd = formatmessage(21273)
        select @ver_check_cmd = '''' + replace(@ver_check_cmd, '''', '''''') + ''''
        select @ver_check_cmd = 'if @@microsoftversion<0x07320000 raiserror(' +
            @ver_check_cmd + ',16, -1)'

        insert into #art_commands values (@artid, @ver_check_cmd)
        if (@@ERROR != 0)
        begin
            RETURN (1)
        end
    END

    
    -- Determine if table has timestamp property
    
    if @has_ts = 1
    begin
        select @ts_col = sys.fn_MSis_col_replicated (@publication, @article, 'timestamp')
        if (@@ERROR != 0)
        begin
            RETURN (1)
        end
    end

    
    -- Determine if published table has identity  col
    
    if @has_ident = 1
    begin
        select @identity_col = sys.fn_MSis_col_replicated (@publication, @article, 'identity')
        if (@@ERROR != 0)
        begin
            RETURN (1)
        end
    end

    
    -- get the distributor details for this publisher
    
    if @distributor is NULL and @trig_only = 0
    begin
        exec @retcode = sys.sp_MSrepl_getdistributorinfo @distributor = @distributor OUTPUT
        if (@@ERROR != 0 OR @retcode != 0 or @distributor IS NULL)
        begin
            raiserror(14071, 16, -1)
            RETURN (1)
        end
    end
    
    -- Prepare for scripting the commands for subscriber
    
    declare @fullname nvarchar(517)
                ,@indkey       int
                ,@indid        int
                ,@key          sysname
                ,@col          sysname
                ,@this_col     int
                ,@src_cols     int
                ,@primary_key_bitmap varbinary(4000)
                ,@byte varbinary(1)
                ,@i_byte         int
                ,@num_bytes      int
                ,@i_bit          tinyint
                ,@bitmap_str varchar(8000)
                ,@bitmap         varbinary(4000)
                ,@colid int

    -- Get qualified name
    exec sys.sp_MSget_qualified_name @tabid, @fullname output

    -- Get number of columns in the partition.
    exec sys.sp_MSget_col_position @tabid, @artid, @key, @col output,
        @this_col output,
        1, -- Get num of columns in the partition.
        @src_cols output
    select @num_bytes = @src_cols / 8 + 1

    -- Set varbinary length
    set @byte = 0
    set @primary_key_bitmap = @byte
    set @i_byte = 1
    while @i_byte < @num_bytes
    begin
        set @primary_key_bitmap = @primary_key_bitmap + @byte
        set @i_byte = @i_byte + 1
    end

    -- get index id
    exec @indid = sys.sp_MStable_has_unique_index @tabid
    set @indkey = 1
    while @indkey < 16 and index_col(@fullname, @indid, @indkey) is not null
    begin
        set @key = index_col(@fullname, @indid, @indkey)
        exec sys.sp_MSget_col_position @tabid, @artid, @key, @col output, @this_col output
        set @i_byte = 1 + (@this_col-1) / 8
        set @i_bit  = power(2, (@this_col-1) % 8 )
        set @primary_key_bitmap
            = substring(@primary_key_bitmap, 1, @i_byte - 1)
            + convert(binary(1), substring(@primary_key_bitmap, @i_byte, 1) | @i_bit)
            + substring(@primary_key_bitmap, @i_byte + 1, @num_bytes - @i_byte)
        select @indkey = @indkey + 1
    end
    exec @retcode = sys.xp_varbintohexstr @primary_key_bitmap, @bitmap_str output
    if @retcode <> 0 or @@error <> 0
        return 1

    
    -- If we are processing queued publications, insert command
    -- to populate MSsubsciption_articles for this article and refresh
    -- sysarticlecolumn entries
    
    if (@is_queued = 1 AND @trig_only = 0)
    begin
        
        -- cft table should exist
        
        if (@cft_table IS NULL)
        begin
            raiserror(21569, 16, 1, @article, @publication)
            return 1
        end
        
        -- add command for Yukon or newer subscribers
        
        select @replcmd = N'if (@@microsoftversion >= 0x09000000) begin ' +
            N'exec sp_addqueued_artinfo ' +
            cast(@artid as nvarchar(10)) collate database_default + ', N' +
            quotename(@article,'''') collate database_default + ', N' +
            quotename(@publisher,'''') collate database_default + ', N' +
            quotename(@publisher_db,'''') collate database_default + ', N' +
            quotename(@publication,'''') collate database_default + ', N' +
            quotename(@dest_table,'''') collate database_default + ', N' +
            quotename(@dest_owner,'''') collate database_default + ', N' +
            quotename(@cft_table,'''') collate database_default +
            N' end '
        
        -- Must add ver check cmd for each article since user can subscribe
        -- to just one article.
        
        insert into #art_commands values (@artid, @replcmd)

        -- shiloh ones need to be called by both initsync and DDL
        -- as this is the channel which propogates articlecolumns updates
        
        -- add command for Shiloh subscribers
        
        select @replcmd = N'if (@@microsoftversion < 0x09000000) begin ' +
            N'exec sp_addqueued_artinfo ' +
            cast(@artid as nvarchar(10)) collate database_default + ', N' +
            quotename(@article,'''') collate database_default + ', N' +
            quotename(@publisher,'''') collate database_default + ', N' +
            quotename(@publisher_db,'''') collate database_default + ', N' +
            quotename(@publication,'''') collate database_default + ', N' +
            quotename(@dest_table,'''') collate database_default + ', N' +
            quotename(@dest_owner,'''') collate database_default + ', N' +
            quotename(@cft_table,'''') collate database_default + ', ' +
            sys.fn_varbintohexstr (sys.fn_replgetbitmapfromcolid(@artid)) collate database_default +
            N' end '

        insert into #art_commands values (@artid, @replcmd)
        
        -- refresh MSsubscription_articlecolumns entries - these are only for Yukon or newer subscribers
        
        if @alter = 0
        begin
            select @replcmd = N'if (@@microsoftversion >= 0x09000000) begin ' +
                N'delete from MSsubscription_articlecolumns where artid = ' + cast(@artid as nvarchar(10)) collate database_default +
                    ' and agent_id = (select id from MSsubscription_agents where update_mode > 0 and UPPER(publisher) = UPPER(N' +
                            quotename(@publisher,'''') collate database_default +
                            ') and publisher_db = N' +
                            quotename(@publisher_db,'''') collate database_default +
                            ' and publication = N' +
                            quotename(@publication,'''') collate database_default +
                N' ) end '
            insert into #art_commands values (@artid, @replcmd)
        end
        else
        begin
            declare #artcol cursor LOCAL FAST_FORWARD for
                -- delete all the columns mentioned in #tran_columnstable, this should apply to add/drop/alter
                select colid from #tran_columnstable
            FOR READ ONLY
            open #artcol
            fetch #artcol into @colid
            while (@@fetch_status = 0)
            begin
                select @replcmd = N'if (@@microsoftversion >= 0x09000000) begin ' +
                    N'delete from MSsubscription_articlecolumns where artid = ' + cast(@artid as nvarchar(10)) collate database_default +
                    ' and colid = ' + cast(@colid as nvarchar(10)) collate database_default +
                    ' and agent_id = (select id from MSsubscription_agents where update_mode > 0 and UPPER(publisher) = UPPER(N' +
                    quotename(@publisher,'''') collate database_default +
                    ') and publisher_db = N' +
                    quotename(@publisher_db,'''') collate database_default +
                    ' and publication = N' +
                    quotename(@publication,'''') collate database_default +
                    N' ) end '
                insert into #art_commands values (@artid, @replcmd)
                fetch #artcol into @colid
            end
            close #artcol
            deallocate #artcol
        end
        
        -- in case of alter, we need to refersh the sysarticlecolumns on subscriber side
        -- compare #tran_columnstable with sysarticlecolumns at publisher side to find out what need to be done.
        -- #tran_columnstable is populated by parsing DDL statement, it contains the list of columns being add/drop/altered
        -- sysarticlecolumns on publisher side should have been updated base on #tran_columnstable by the time we get here
        

        --special case for alter since #tran_columnstable may not exists for the other case, having one single query will cause compilation error
        if (@alter = 1 )
        begin
			if exists(select sac.colid from #tran_columnstable tc join sysarticlecolumns sac on tc.colid = sac.colid where artid = @artid)
			begin
				select @f_refresh_columns = 1
			end
        end

        if (@alter = 0 and exists (select colid from sysarticlecolumns where artid = @artid))
        	or (@alter = 1 and @f_refresh_columns = 1)
        begin
	        select @replcmd = N'if (@@microsoftversion >= 0x09000000) begin ' +
	            N'declare @agent_id_' + cast(@artid as nvarchar(12)) + N' int ' +
	            N'select @agent_id_' + cast(@artid as nvarchar(12)) + N' = id from MSsubscription_agents where update_mode > 0 and UPPER(publisher) = UPPER(N' +
	            quotename(@publisher,'''') collate database_default +
	            N') and publisher_db = N' +
	            quotename(@publisher_db,'''') collate database_default +
	            N' and publication = N' +
	            quotename(@publication,'''') collate database_default +
	            N' if @agent_id_' + cast(@artid as nvarchar(12)) + N' is not null begin '
	        if (@alter = 0)
	        begin
	            declare #artcol cursor LOCAL FAST_FORWARD for
	                select colid from sysarticlecolumns where artid = @artid
	            FOR READ ONLY
	        end
	        else
	        begin
	            declare #artcol cursor LOCAL FAST_FORWARD for
	                select sac.colid from #tran_columnstable tc join sysarticlecolumns sac on tc.colid = sac.colid where artid = @artid
	            FOR READ ONLY
	        end
	        open #artcol
	        fetch #artcol into @colid
	        while (@@fetch_status = 0)
	        begin
	            select @replcmd = @replcmd + N'insert MSsubscription_articlecolumns (agent_id, artid, colid) values (@agent_id_' + cast(@artid as nvarchar(12)) + N', ' +
	            cast(@artid as nvarchar(10)) collate database_default + N', ' +
	            cast(@colid as nvarchar(10)) collate database_default + N') '
	            fetch #artcol into @colid
	        end
	        close #artcol
	        deallocate #artcol
	        select @replcmd = @replcmd + N' end end '
	        insert into #art_commands values (@artid, @replcmd)
        end
    end -- if (@is_queued = 1)
    
    
    
    if @alter = 1 and @trig_only = 0
    begin
        select @replcmd = N'if (@@microsoftversion >= 0x09000389) begin ' +
            N'exec sp_addsynctriggerscore N' +
            quotename(@dest_table,'''') + ', N' +
            quotename(@dest_owner,'''') + ', N' +
            quotename(@publisher,'''') + ', N' +
            quotename(@publisher_db,'''') + ', N' +
            quotename(@publication,'''') + ', N' +
            quotename(@insproc,'''') + ', N' +
            quotename(@updproc,'''') + ', N' +
            quotename(@delproc,'''') + ', N' +
            ISNULL(quotename(@cftproc,''''), '''null''')  + ', N' +
            quotename(@proc_owner,'''') + ', N' +
            ISNULL(quotename(@identity_col,''''),'''null''') + ', N' +
            ISNULL(quotename(@ts_col,''''), '''null''') + ', N''' +
            replace(@filter_clause,'''', '''''')  + ''', ' +
            @bitmap_str   + ', ' +
            convert(nvarchar(2), @identity_support)  + ',' +
            convert(nvarchar(2), @independent_agent) + ',' +
            N'2, NULL, NULL, NULL, 1 ' +
            N'end '
        insert into #art_commands values (@artid, @replcmd)
        select @replcmd = N'if (@@microsoftversion >= 0x080002C0 and @@microsoftversion < 0x09000389) begin ' +
            N'exec sp_addsynctriggers N' +
            quotename(@dest_table,'''') + ', N' +
            quotename(@dest_owner,'''') + ', N' +
            quotename(@publisher,'''') + ', N' +
            quotename(@publisher_db,'''') + ', N' +
            quotename(@publication,'''') + ', N' +
            quotename(@insproc,'''') + ', N' +
            quotename(@updproc,'''') + ', N' +
            quotename(@delproc,'''') + ', N' +
            ISNULL(quotename(@cftproc,''''), '''null''')  + ', N' +
            quotename(@proc_owner,'''') + ', N' +
            ISNULL(quotename(@identity_col,''''),'''null''') + ', N' +
            ISNULL(quotename(@ts_col,''''), '''null''') + ', N''' +
            replace(@filter_clause,'''', '''''')  + ''', ' +
            @bitmap_str   + ', ' +
            convert(nvarchar(2), @identity_support)  + ',' +
            convert(nvarchar(2), @independent_agent) + ',N' +
            quotename(@distributor,'''') +  ', ' +
            N'2 ' +
            N'end '
        insert into #art_commands values (@artid, @replcmd)
    end
    
    -- insert the command to generate the triggers
    -- Handle version specific command scripting
    -- Script the command for 80Sp3 or later subscribers first
    
    else
    begin
        if @trig_only = 0
        begin
           select @replcmd = N'if (@@microsoftversion >= 0x080002C0) begin ' +
                N'exec sp_addsynctriggers N' +
                quotename(@dest_table,'''') + ', N' +
                quotename(@dest_owner,'''') + ', N' +
                quotename(@publisher,'''') + ', N' +
                quotename(@publisher_db,'''') + ', N' +
                quotename(@publication,'''') + ', N' +
                quotename(@insproc,'''') + ', N' +
                quotename(@updproc,'''') + ', N' +
                quotename(@delproc,'''') + ', N' +
                ISNULL(quotename(@cftproc,''''), '''null''')  + ', N' +
                quotename(@proc_owner,'''') + ', N' +
                ISNULL(quotename(@identity_col,''''),'''null''') + ', N' +
                ISNULL(quotename(@ts_col,''''), '''null''') + ', N''' +
                replace(@filter_clause,'''', '''''')  + ''', ' +
                @bitmap_str   + ', ' +
                convert(nvarchar(2), @identity_support)  + ',' +
                convert(nvarchar(2), @independent_agent) + ',N' +
                quotename(@distributor,'''') +  ', ' +
                N'2 ' +
                N'end '
        end
        else -- trigger generation command only
        begin
            select @replcmd = N'exec sys.sp_addsynctriggers N' + -- only called by Yukon SP1+, so sys. is okay
                quotename(@dest_table,'''') + ', N' +
                quotename(@dest_owner,'''') + ', N' +
                quotename(@publisher,'''') + ', N' +
                quotename(@publisher_db,'''') + ', N' +
                quotename(@publication,'''') + ', N' +
                quotename(@insproc,'''') + ', N' +
                quotename(@updproc,'''') + ', N' +
                quotename(@delproc,'''') + ', N' +
                ISNULL(quotename(@cftproc,''''), '''null''')  + ', N' +
                quotename(@proc_owner,'''') + ', N' +
                ISNULL(quotename(@identity_col,''''),'''null''') + ', N' +
                ISNULL(quotename(@ts_col,''''), '''null''') + ', N''' +
                replace(@filter_clause,'''', '''''')  + ''', ' +
                @bitmap_str   + ', ' +
                convert(nvarchar(2), @identity_support)  + ',' +
                convert(nvarchar(2), @independent_agent) + ',' +
                'N''NULL'', ' +
                N'2, ' +
                N'1 ' -- extra flag to dump trigger creation command
        end

        insert into #art_commands values (@artid, @replcmd)
    end
    
    -- Script the command for pre 80Sp3 or later subscribers
    -- NOTE: This block can be removed once we stop supporting
    -- backwards compatibility with 80Sp3 queued subscribers
    
    if @trig_only = 0 -- flag only in 90sp1+
    begin
        select @replcmd = N'if (@@microsoftversion < 0x080002C0) begin ' +
            N'exec sp_addsynctriggers N' +
            quotename(@dest_table,'''') + ', N' +
            quotename(@dest_owner,'''') + ', N' +
            quotename(@publisher,'''') + ', N' +
            quotename(@publisher_db,'''') + ', N' +
            quotename(@publication,'''') + ', N' +
            quotename(@insproc,'''') + ', N' +
            quotename(@updproc,'''') + ', N' +
            quotename(@delproc,'''') + ', N' +
            ISNULL(quotename(@cftproc,''''), '''null''')  + ', N' +
            quotename(@proc_owner,'''') + ', N' +
            ISNULL(quotename(@identity_col,''''),'''null''') + ', N' +
            ISNULL(quotename(@ts_col,''''), '''null''') + ', N''' +
            replace(@filter_clause,'''', '''''')  + ''', ' +
            @bitmap_str   + ', ' +
            convert(nvarchar(2), @identity_support)  + ',' +
            convert(nvarchar(2), @independent_agent) + ',N' +
            quotename(@distributor,'''') +
            N' end '

        insert into #art_commands values (@artid, @replcmd)
    end

    if @command_only = 1
        select commands from #art_commands where artid = @artid order by id
    
    -- all done
    
end
    return 0
END

 
Last revision 2008RTM
See also

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