Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_update_statement

  No additional text.


Syntax
create procedure sys.sp_MSscript_update_statement
(
    @publication sysname,
    @article     sysname,
    @objid int,
    @queued_pub bit = 0
)
as
BEGIN
    declare @cmd            nvarchar(4000)
            ,@cmd2          nvarchar(4000)
            ,@qualname      nvarchar(517)
            ,@colname       sysname
            ,@typestring    nvarchar(4000)
            ,@spacer        nvarchar(1)
            ,@ccoltype      sysname
            ,@this_col      int
            ,@rc            int
            ,@column        nvarchar(4000)
            ,@num_col       int
            ,@bytestr nvarchar(10)
            ,@bitstr nvarchar(10)
            ,@art_col       int -- position in the article partition.
            ,@isset         int
            ,@timestamp_subscribed bit
            ,@pubid         int
            ,@artid         int
            ,@hfilterprocid int
            ,@sync_objid int
            ,@status tinyint

    
    -- Get meta data on the article
    
    select @pubid = sp.pubid
            ,@artid = sa.artid
            ,@hfilterprocid = sa.filter
            ,@sync_objid = sa.sync_objid
            ,@status = sa.status
    from syspublications sp join sysarticles sa on sp.pubid = sa.pubid
    where sp.name = @publication and sa.name = @article
    
    -- Check if we have horizontal partition filter
    
    if (@hfilterprocid = 0)
    begin
        
        -- horizontal partional filter not defined, the qualified name should be the source object
        
        exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
    end
    else
    begin
        
        -- horizontal partional filter not defined, the qualified name should be the view object
        
        exec sys.sp_MSget_qualified_name @sync_objid, @qualname OUTPUT
    end
    select @timestamp_subscribed = case when (@status & 32 <> 0) then 1 else 0 end
    
    -- Start scripting
    
    select @cmd = N'
    ' + N'
    ' + N'-- detection/conflict resolution stage
    ' + N'--'
    
    -- Queued specific scripting
    
    if (@queued_pub = 1)
    begin
        select @cmd = @cmd + N'
    if (@execution_mode = @QPubWins)
        save tran cftpass
    '
    end
    insert into #proctext(procedure_text) values(@cmd)
    
    -- Generate the update statement
    
    select @cmd2 = N'
    update ' + @qualname + N' set
    '
    select @spacer = N'
        '
            ,@cmd = N''
    exec sys.sp_MSpad_command @cmd output, 8
    select @num_col = 0
            ,@art_col = 0

    DECLARE #hCColid CURSOR LOCAL FAST_FORWARD FOR
        select column_id from sys.columns where object_id = @objid order by column_id asc

    open #hCColid
    fetch #hCColid into @this_col
    while (@@fetch_status <> -1)
    begin
        -- Get the ordinal of the article partition or not.
        exec @isset = sys.sp_isarticlecolbitset @this_col, @artid
        if @isset = 0
        begin
            
            -- Special handling of a timestamp col and rowguid col for sync tran procedure
            -- See sp_helparticlecolumns : xtype 189 is timestamp
            
            if ((@timestamp_subscribed = 1) and
                    exists ( select * from sys.columns where object_id = @objid and column_id = @this_col and system_type_id = 189))
                select @art_col = @art_col + 1
        end
        else
            select @art_col = @art_col + 1

        exec @rc = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
        if @rc = 0 and EXISTS (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
        begin
            
            -- do not include references to timestamp columns, identity columns or rowguid columns
            
            if (rtrim(@ccoltype) not like N'timestamp'
                    and ColumnProperty(@objid, @colname, 'IsIdentity') != 1
                    and ColumnProperty(@objid, @colname, 'IsRowGuidCol') != 1)
            begin
                if @cmd2 is not null
                begin
                    exec sys.sp_MSflush_command @cmd2 output, 1, 8
                    select @cmd2 = null
                end

                select @num_col = @num_col + 1
                -- Optimization:
                -- Get null or actual column name
                -- Note: the output is quoted.
                exec sys.sp_MSget_synctran_column
                    @ts_col = null,
                    @op_type = null , -- 'ins, 'upd', 'del'
                    @is_new = null,
                    @primary_key_bitmap = null,
                    @colname = @colname,
                    @this_col = @this_col,
                    @column = @column output,
                    @from_proc = 1,
                    @art_col = @art_col -- position in the partition.

                select @cmd = @cmd + @spacer + QUOTENAME(@colname) + N' = ' + @column
                select @spacer = N','
                
                -- flush command if necessary
                
                exec sys.sp_MSflush_command @cmd output, 1, 8
            end
        end
        fetch #hCColid into @this_col
    end
    close #hCColid
    deallocate #hCColid
    
    -- save off cmd fragment
    
    if @num_col > 0
    begin
        
        -- Add the where clause based on the update mode
        
        select @colname = 'msrepl_tran_version'
        exec sys.sp_MSscript_where_clause @objid, @artid, 'upd version', @colname, 4
    end
    else
    -- set the @@rowcount
        insert into #proctext(procedure_text) values( N' select @retcode = @retcode
    ')
    
    -- continue with rest of scripting
    
    select @cmd = N'
    select @rowcount = @@ROWCOUNT, @error = @@ERROR
    '
    
    -- Queued specific case
    
    if (@queued_pub = 1)
    begin
        select @cmd = @cmd + N'
    if (@execution_mode = @QPubWins)
        rollback tran cftpass'
    end
    insert into #proctext(procedure_text) values(@cmd)
    
    -- Queued specific case
    
    if (@queued_pub = 1)
    begin
    	
    	-- script the assignment of new values based on bitmask
    	
    	select @num_col = 0
    			,@art_col = 0
    			,@cmd = N'
    ' + N'
    ' + N'-- for conflict resolution - assign the NEW values based on bitmap
    ' + N'
    if (@execution_mode in (@QPubWins, @QSubWins))
    begin 	
    	select '
    	insert into #proctext(procedure_text) values(@cmd)	

    	DECLARE hCColid CURSOR LOCAL FAST_FORWARD FOR
    		select column_id from sys.columns where object_id = @objid order by column_id asc

    	OPEN hCColid
    	FETCH hCColid INTO @this_col
    	WHILE (@@fetch_status != -1)
    	begin
    		-- Get the ordinal of the article partition or not.
    		exec @isset = sp_isarticlecolbitset @this_col, @artid
    		if @isset = 0
    		begin
    			if ((@timestamp_subscribed = 1) and
    					exists ( select * from sys.columns where object_id= @objid and column_id = @this_col and system_type_id = 189))
    				select @art_col = @art_col + 1
    		end
    		else
    			select @art_col = @art_col + 1

    		exec @rc = sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
    		if ((@rc = 0)
    			and EXISTS (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed != 1)
    			and (rtrim(@ccoltype) != N'timestamp'))
    		begin
    			select @num_col = @num_col + 1
    					,@bytestr = cast((1 + (@art_col-1) / 8 ) as nvarchar)
    					,@bitstr =  cast( power(2, (@art_col-1) % 8 ) as nvarchar)

    			select @cmd = N'@c' + cast(@this_col as nvarchar(10)) + N' = case substring(@bitmap,' +
    				@bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr +
    				N' then @c' + cast(@this_col as nvarchar(4)) +
    				N' else @c' + cast(@this_col as nvarchar(4)) + N'_old end '

    			if (@num_col = 1)
    			begin
    				select @cmd = N'
    			' + @cmd
    			end
    			else
    			begin
    				select @cmd = N'
    			, ' + @cmd
    			end
    			insert into #proctext(procedure_text) values(@cmd)	
    		end
    		
    		-- fetch next row
    		
    		FETCH hCColid INTO @this_col
    	end
    	CLOSE hCColid
    	DEALLOCATE hCColid
    	
    	-- continue with scripting
    	
    	select @cmd = N'
    end' 	
    	insert into #proctext(procedure_text) values(@cmd)	
    end
    
    -- all done
    
    return 0
END

 
Last revision 2008RTM
See also

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