Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_pkvar_assignment

  No additional text.


Syntax
create procedure sys.sp_MSscript_pkvar_assignment
(
    @objid          int,
    @artid          int,
    @indent       int = 0,
    @identity_col sysname = NULL, -- Not null value used by trigger scripting
    @ts_col       sysname = NULL,  -- Not null value used by trigger scripting
    @primary_key_bitmap varbinary(4000) = null, -- NULL when synctran processing
    @fisqueuedpub bit = 0  -- 1 = processing a queued publication on publisher
)
as
begin
-- This stored procedure will assign the '_old' var to new var
-- based on @bitmap. This is to avoid using case statement
-- in the where clause in the synctran pub proc, which
-- will cause a table scan.
    declare @cmd          nvarchar(4000)
        ,@spacer       nvarchar(20)
        ,@indkey       int
        ,@indid        int
        ,@this_col     int
        ,@col          sysname
        ,@qualname     nvarchar(517)
        ,@column          nvarchar(255)
        ,@key          sysname
        ,@src_cols      int
        ,@total_col        int
        ,@fcreatedcolmap    bit
        ,@art_col int -- relative position of column
        ,@thisspname sysname
    declare @colmap table (relativeorder int identity(1,1), colid int)

    select @spacer = N'select '
        ,@cmd = N''
        ,@thisspname = N'sp_MSscript_pkvar_assignment'
        ,@indkey = 1
        ,@indid = 0
        ,@fcreatedcolmap = 0
    exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
    select @src_cols = max(column_id)
            ,@total_col = count(column_id)
        from sys.columns where object_id = @objid
    exec sys.sp_MSpad_command @cmd output, @indent
    exec sys.sp_MSflush_command @cmd output, 1, @indent

    if @primary_key_bitmap is null
    begin
        exec @indid = sys.sp_MStable_has_unique_index @objid
        if @indid is null
        begin
            raiserror(21750, 16, 2, @qualname)
            return (1)
        end
    end
    else
    begin
        
        -- PK bitmap is not null
        -- check if column Id match relative column order
        -- for trigger scripting
        
        if ((@total_col < @src_cols) and (@artid is null))
        begin
            
            -- this table may have altered columns, so when we need to
            -- set a mapping for using the bitmaps properly as the bitmap
            -- always refers relative column order
            
            insert into @colmap (colid)
                select column_id from sys.columns where object_id = @objid order by column_id
            if (@@error != 0)
            begin
                raiserror(21499, 16, 6, @thisspname, 'populate', '@colmap', @@error)
                return (1)
            end
            select @fcreatedcolmap = 1
        end
    end

    while (1=1)
    begin
        if @primary_key_bitmap is null
        begin
            select @key = index_col(@qualname, @indid, @indkey)
            if @key is null
                break;
            exec sys.sp_MSget_col_position @objid, @artid, @key, @col output, NULL, 0, NULL, @this_col output
        end
        else
        begin
            exec sys.sp_MSget_map_position @primary_key_bitmap, @indkey, @col output, @this_col output
            if @this_col is null
                break;

                
                -- set the actual column id for this relative order in the PK bitmap if necessary
                
            if (@fcreatedcolmap = 1)
            begin
                select @art_col = @this_col
                select @this_col = colid
                    ,@col = 'c' + convert(sysname, colid)
                from @colmap
                where relativeorder = @art_col
            end
            else
            begin
                select @art_col = NULL
            end

            -- Get column name
            exec sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @key output
        end

        select @indkey = @indkey + 1

        if @key in (@identity_col, @ts_col)
            continue
        
        -- If we are scripting on publisher
        
        if (@primary_key_bitmap is null)
        begin
            declare @isset int
            
            -- skip column if not replicated
            
            exec @isset = sp_isarticlecolbitset @this_col, @artid
            if (@isset != 1)
                continue
            
            -- skip timestamp processing for queued
            
            if ((@fisqueuedpub = 1) and
                exists (select name from sys.columns
                    where object_id = @objid and column_id = @this_col and system_type_id = 189))
                continue
        end
        select @cmd = @spacer + N'@c' + convert(nvarchar(10), @this_col)

        -- Get the new values for the columns in primary key.
        exec sys.sp_MSget_synctran_column
                @ts_col = null,
                @op_type = null , -- 'ins, 'upd', 'del'
                @is_new = null,
                @primary_key_bitmap = null,
                @colname = null,
                @this_col = @this_col,
                @column = @column output,
                @from_proc = 0,
                @coltype = null,
                @type = 'pk_var',
                @art_col = @art_col
        select @cmd = @cmd + N' = ' + @column
        select @spacer = ',
    '

        -- flush command if necessary
        exec sys.sp_MSflush_command @cmd output, 1, @indent
    end
end

 
Last revision 2008RTM
See also

  sp_MSscript_endproc (Procedure)
sp_MSscript_trigger_updates (Procedure)
sp_MSscript_trigger_version_updates (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