Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSget_synctran_column

  No additional text.


Syntax
create procedure sys.sp_MSget_synctran_column
(
    @ts_col sysname,
    @op_type char(3), -- 'ins, 'upd', 'del'
    @is_new    bit,
    @primary_key_bitmap varbinary(4000) = null,
    @colname sysname,
    @this_col int, -- position in source object
    @column nvarchar(4000) output,
    @from_proc bit = 0,
    @coltype sysname = NULL,
    @type    varchar(10) = NULL,
    @art_col int = NULL -- position in the partition.
)
as
begin
    declare @bytestr      nvarchar(10)
            ,@bitstr       nvarchar(10)
            ,@typed_null      nvarchar(255)

    
    -- if @art_col is not NULL then it means one of the following:
    -- 1) If we are scripting for triggers - then it means the subscriber
    --     table (destination table) was altered and column id do not match
    --     relative column order(as specified in PK bitmap or columns_updated()).
    --    The @art_col will represent the relative column order in the bitmap
    --    and @this_col will represent the actual column id
    
    -- 2) If we are scripting for synctran procedures on publisher - then
    --    @art_col represents the relative index of the column that is being
    --    replicated and @this_col represents the actual column id of the column
    
    -- if @art_col is NULL - then we will set it to the value of @this_col
    
    if (@art_col is NULL)
        select @art_col = @this_col

    select @typed_null = case when (@coltype is null) then N'NULL'
                    else N'convert(' + @coltype + N', NULL)' end

    -- Optimization:
    -- If the column value is not needed, we set the corresponding
    -- param to null to reduce the network traffic. Here is the rule:

    -- For new values in update trigger,
    --    Set the param to column value or null depending on whether or
    --  or the column is updated.
    -- For old values
    --    if ts col is replicated and the current column is not the ts col
    --    and the column is not in primary key, set the param to null
    -- For other cases
    --    set the param to column values.

    -- Called by proc
    if @type = 'pk_var'
    begin
        select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
        select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

        select @column = N'case substring(@bitmap,'
            + @bytestr + N',1) & ' + @bitstr +
            N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar, @this_col ) +
            N' else ' + N'@c'+ convert( nvarchar, @this_col )  + N'_old end'
    end
    else if (@from_proc = 1)
    begin
        select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
        select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

        select @column = N'case substring(@bitmap,'
            + @bytestr + N',1) & ' + @bitstr +
            N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar, @this_col ) +
            N' else ' + quotename(@colname) + N' end'
    end
    -- Called in trigger,
    else if (@is_new = 1) and (@op_type = 'upd')
    begin
        -- @bitmap is set using columns_updated() at the beginning
        -- of the trigger.
        select @bytestr = convert( nvarchar, 1 + (@art_col-1) / 8 )
        select @bitstr =  convert( nvarchar, power(2, (@art_col-1) % 8 ) )

        if (lower(@coltype) = 'xml')
        begin
            select @column = N'case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +
                N' when ' + @bitstr + N' then convert(xml, ' + quotename(@colname) + N') ' +
                N' else ' + @typed_null
                +' end'
        end
        else
        begin
            select @column = N'case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr +
                N' when ' + @bitstr + N' then ' + quotename(@colname) + N' ' +
                N' else ' + @typed_null
                +' end'
        end
 end
    else if ((@is_new = 0) and
        (@ts_col is not null and @colname not in (@ts_col, N'msrepl_tran_version')) and
        (@primary_key_bitmap is not null and
        (substring(@primary_key_bitmap, 1 + (@art_col-1) / 8 , 1) & power(2, (@art_col-1) % 8 )) = 0))
        select @column =  @typed_null
    else
        select @column = quotename(@colname)

    -- Add a new line
    select @column = @column + N'
    '
end

 
Last revision 2008RTM
See also

  sp_MSscript_pkvar_assignment (Procedure)
sp_MSscript_trigger_assignment (Procedure)
sp_MSscript_trigger_variables (Procedure)
sp_MSscript_update_statement (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