Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_compensating_insert

  No additional text.


Syntax
create procedure sys.sp_MSscript_compensating_insert
(
    @publication sysname 	-- publication name
    ,@article sysname 		-- article name
    ,@objid int			-- table object id
    ,@proctype tinyint		-- what are we scripting 		
    -- 0 = use new_pk
    -- 1 = use old_pk
    -- 2 = use old nonpkkeys
    -- 3 = use new nonpkkeys
    -- 4 = use oldallkeys
    -- 5 = use newallkeys
    ,@fdodeclare bit = 1	-- 0 = do not script declares for non PK unique key processing
)
as
begin
    declare @cmd nvarchar(4000)
                ,@artid int
                ,@pubid int
                ,@dest_table sysname
                ,@dest_owner nvarchar(260)
                ,@colname      sysname
                ,@ccoltype     sysname
                ,@this_col     int
                ,@rc           int
                ,@num_col	  int
                ,@qualname nvarchar(540)
                ,@cast_str nvarchar(4000)
                ,@column_string nvarchar(4000)
                ,@filter_clause nvarchar(4000)
                ,@table_name sysname
                ,@owner_name sysname
                ,@ins_cmd nvarchar(255)
                ,@collen int
                ,@first_time bit
                ,@fhasnonpkuniquekeys int
                ,@fprocesshfilter bit
    declare @pkfetch table ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    
    -- constants
    
    declare @typeusenew_pk tinyint
                ,@typeuseold_pk tinyint
                ,@typeuseoldnonpkkeys tinyint
                ,@typeusenewnonpkkeys tinyint
                ,@typeuseoldallkeys tinyint
                ,@typeusenewallkeys tinyint
    
    -- initialize constants
    
    select @typeusenew_pk = 0
                ,@typeuseold_pk = 1
                ,@typeuseoldnonpkkeys = 2
                ,@typeusenewnonpkkeys = 3
                ,@typeuseoldallkeys = 4
                ,@typeusenewallkeys = 5
                ,@fprocesshfilter = 0
    
    -- validate @proctype
    
    if (@proctype not in (@typeusenew_pk,@typeuseold_pk,@typeuseoldnonpkkeys,
            @typeusenewnonpkkeys,@typeuseoldallkeys,@typeusenewallkeys))
    begin
        -- raiserror invalid proctype
        return 1
    end
    
    -- initialize the vars we will use
    
    select @pubid = pubid from syspublications where name = @publication
    select @artid = artid, @dest_table = dest_table, @dest_owner = dest_owner,
            @ins_cmd = ins_cmd, @filter_clause = cast(filter_clause as nvarchar(4000))
    from sysarticles
    where name = @article and pubid = @pubid
    select @dest_owner = case when (@dest_owner IS NULL) then N''
                                else quotename(@dest_owner) + N'.' end
            ,@fhasnonpkuniquekeys = 0
    exec sp_MSget_qualified_name @objid, @qualname OUTPUT
    
    -- initialize more vars if we have filter clause
    
    if( @filter_clause is not null and datalength( @filter_clause ) > 0 )
    begin
        select @fprocesshfilter = 1
                ,@table_name = name
                ,@owner_name = schema_name(schema_id)
        from sys.objects
        where object_id = @objid
        
        -- prepare the filter clause
        
        exec @rc = sys.sp_MSsubst_filter_names @owner_name, @table_name, @filter_clause output
        if @rc <> 0 or @@error <> 0
            return 1
    end
    
    -- Do we have non PK unique keys
    
    exec @fhasnonpkuniquekeys = sp_repltablehasnonpkuniquekey @tabid = @objid
    
    -- If we are generating refreshing commands
    -- we will be using cursors
    
    if (@proctype in (@typeuseoldnonpkkeys,@typeusenewnonpkkeys
                            ,@typeuseoldallkeys,@typeusenewallkeys))
    begin
        
        -- generate cursor to collect PK for selected rowset
        -- and then perform delete compensation followed by
        -- insert compensation
        
        declare @cmd2 nvarchar(4000)
                    ,@cmd3 nvarchar(4000)
                    ,@spacer nvarchar(5)
                    ,@typestring sysname
                    ,@isset int
                    ,@wheremode tinyint
                    ,@suffix nvarchar(10)
        declare @pkvars table ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
        declare @pkcols table ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)

        
        -- if the article has no non PK unique keys
        -- we should not be processing for @typeuseoldnonpkkeys,@typeusenewnonpkkeys
        
        if (@fhasnonpkuniquekeys = 0 and
            @proctype in (@typeuseoldnonpkkeys,@typeusenewnonpkkeys))
        begin
            -- error we should not generate these cases if there are no non PK unique keys
            return 1
        end
        
        -- build some strings for PK columns and PK variables (@pkc1..)
        
        select @cmd = N''
                ,@cmd2 = N''
                ,@cmd3 = N''
                ,@spacer = N''
        declare #hccolid cursor local fast_forward for
            select column_id, name from sys.columns where object_id = @objid order by column_id asc
        open #hccolid
        fetch #hccolid into @this_col, @colname
        while (@@fetch_status != -1)
        begin
            exec @isset = sp_MSiscolpk @objid, @this_col
            if @isset != 0 and (@colname is not null)
            begin
					select @typestring = sys.fn_gettypestring(@objid, @this_col, 1, 0, 0, 0, 1, 0, 0, 0, 0) -- LUDT+Spatial merge conflict triggger
                select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar, @this_col ) + N' ' + @typestring
                        ,@cmd2 = @cmd2 + @spacer + quotename(@colname)
                        ,@cmd3 = @cmd3 + @spacer + N'@pkc' + convert( nvarchar, @this_col )
                select @spacer = N','

                if len( @cmd ) > 3000
                begin
                    insert into @pkvars(procedure_text) values( @cmd )
                    select @cmd = N''
                end
                if len( @cmd2 ) > 3000
                begin
                    insert into @pkcols(procedure_text) values( @cmd2 )
                    select @cmd2 = N''
                end
                if len( @cmd3 ) > 3000
                begin
                    insert into @pkfetch(procedure_text) values( @cmd3 )
                    select @cmd3 = N''
                end
            end
            fetch #hccolid into @this_col, @colname
        end
        close #hccolid
        deallocate #hccolid
        if len(@cmd) > 0
            insert into @pkvars(procedure_text) values( @cmd )
        if len(@cmd2) > 0
            insert into @pkcols(procedure_text) values( @cmd2 )
        if len(@cmd3) > 0
            insert into @pkfetch(procedure_text) values( @cmd3 )
        
        -- script the PK variable declare now
        
        if (@fdodeclare = 1)
        begin
            select @cmd = N'
                declare '
            insert into #proctext(procedure_text) values( @cmd )
            insert into #proctext(procedure_text)
                select procedure_text from @pkvars order by c1 asc
        end
        
        -- script the cursor declare now
        
        select @cmd = N'
            declare #hccompins cursor local fast_forward for
                select distinct '
        insert into #proctext(procedure_text) values( @cmd )
        insert into #proctext(procedure_text)
            select procedure_text from @pkcols order by c1 asc
        select @cmd = N'
            from ' + @qualname
        insert into #proctext(procedure_text) values( @cmd )
        
        -- script the where clause for the cursor
 -- based on @proctype
        
        if (@proctype = @typeuseoldnonpkkeys)
        begin
            select @wheremode = 7
                    ,@suffix = N'_old'
        end
        else if (@proctype = @typeusenewnonpkkeys)
        begin
            select @wheremode = 7
                    ,@suffix = NULL
        end
        else if (@proctype = @typeuseoldallkeys)
        begin
            select @wheremode = 6
                    ,@suffix = N'_old'
        end
        else if (@proctype = @typeusenewallkeys)
        begin
            select @wheremode = 6
                    ,@suffix = NULL
        end
        exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
                            ,@artid = @artid
                            ,@prefix = N'@c'
                            ,@suffix = @suffix
                            ,@mode = @wheremode
        
        -- if we have horizontal filters
        -- include them in the where clause
        
        if (@fprocesshfilter=1)
        begin
            insert into #proctext(procedure_text) values (' and (' + @filter_clause + ')')
        end
        
        -- script the cursor open and fetch
        
        select @cmd = N'
            open #hccompins
            fetch #hccompins into '
        insert into #proctext(procedure_text) values( @cmd )
        insert into #proctext(procedure_text)
            select procedure_text from @pkfetch order by c1 asc
        select @cmd = N'
            while (@@fetch_status != -1)
            begin '
        insert into #proctext(procedure_text) values( @cmd )
        
        -- we are processing a refresh process
        -- issue a compensating delete for the row
        -- select in the cursor
        
        select @cmd = N'
            ' + '
            ' + '-- Issue a delete command for row selected in cursor
            ' + '-- '
        insert into #proctext(procedure_text) values( @cmd )
        select @cmd = N'
            select @cmd = N''DELETE ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
                + sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N' '' + '
        insert into #proctext(procedure_text) values( @cmd )
        exec @rc = sp_replscriptuniquekeywhereclause @tabid = @objid
                            ,@artid = @artid
                            ,@prefix = N'@pkc'
                            ,@suffix = NULL
                            ,@mode = 8
        
        -- script the send for this command
        
        exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
        
        -- continue scripting
        
        select @cmd = N'
            ' + '
            ' + '-- Issue a compensating insert for row selected in cursor
            ' + '-- '
        insert into #proctext(procedure_text) values( @cmd )
    end -- if the article has no non PK unique keys
    else
    begin
        
        -- Processing for generation for PK cases only
        -- Check if we have filter clause
        
        if (@fprocesshfilter=1)
        begin
            
            -- script a if exists wrapper using filter clause
            -- so that we send only the necessary rows
            
            select @cmd = N'
			if exists (select * from ' + @qualname
            insert into #proctext(procedure_text) values( @cmd )
            if (@proctype = @typeuseold_pk)
                exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0, 'del'
            else
                exec sp_MSscript_where_clause @objid, @artid, 'new_pk_q', NULL, 0, 'ins'
            select @cmd = N' and (' + @filter_clause + ') )
			begin '
            insert into #proctext(procedure_text) values( @cmd )
        end
    end
    
    -- Prepare the compensating command. Since we will be using
    -- an nvarchar(max) variable for the compensating command,
    -- there are no fixed limits for the length of the command
    
    -- use the insert custom command if available
    
    if (@ins_cmd = N'SQL')
    begin
        select @cmd = N'
            select @cmd = N''INSERT INTO ' + sys.fn_replreplacesinglequote(@dest_owner) collate database_default
            + sys.fn_replreplacesinglequote(quotename(@dest_table)) collate database_default + N''' +
            '' SELECT '' + '
    end
    else
    begin
        select @cmd = N'
            select @cmd = N''EXEC ' + sys.fn_replreplacesinglequote(substring(@ins_cmd, 5, len(@ins_cmd) - 4)) collate database_default + N' '' + '
    end
    insert into #proctext(procedure_text) values( @cmd )
    
    -- enumerate the columns
    
    select @num_col = 0
    declare #hccolid cursor local fast_forward for
        select column_id, max_length from sys.columns where object_id = @objid order by column_id asc
    open #hccolid
    fetch #hccolid into @this_col, @collen
    while (@@fetch_status != -1)
    begin
        exec @rc = sp_MSget_colinfo @objid, @this_col, @artid, 1, @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
            if rtrim(@ccoltype) not like N'timestamp'
            begin
                select @num_col = @num_col + 1
                
                -- Prepare the column string based on column type
                --				
                if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('ntext','text'))
                begin
                    select @cast_str = case
                        when (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'ntext')
                            then N' ISNULL(''N'''''' + sys.fn_replreplacesinglequote(cast(' + quotename(@colname) + N' as nvarchar(max))) collate database_default + '''''''', ''null'') '
                            else N' ISNULL('''''''' + sys.fn_replreplacesinglequote(cast(' + quotename(@colname) + N' as nvarchar(max))) collate database_default + '''''''', ''null'') '
                        end	
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('varchar','nvarchar','char','nchar'))
                begin	
                    select @cast_str = case
                        when (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('nvarchar', 'nchar'))
                            then N' ISNULL(''N'''''' + sys.fn_replreplacesinglequote(' + quotename(@colname) + N') collate database_default + '''''''', ''null'') '
                            else N' ISNULL('''''''' + sys.fn_replreplacesinglequote(' + quotename(@colname) + N') collate database_default + '''''''', ''null'') '
                        end	
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'image')
                begin
                    select @cast_str = N' ISNULL(sys.fn_varbintohexsubstring(1, cast(' + quotename(@colname) + N' as varbinary(max)),1,0) collate database_default, ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
                begin
                    select @cast_str = N' ISNULL(sys.fn_varbintohexsubstring(1,' + quotename(@colname) + N',1,0) collate database_default, ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
                begin
                    select @cast_str = N' ISNULL(CAST(' + quotename(@colname) + N' as nvarchar), ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
                begin
                    select @cast_str = N' ISNULL(CONVERT(nvarchar(60),' + quotename(@colname) + N',2), ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
                begin
                    select @cast_str = N' ISNULL(CONVERT(nvarchar(40),' + quotename(@colname) + N',2), ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
                begin
                    select @cast_str = N' ISNULL('''''''' + CAST(' + quotename(@colname) + N' as nvarchar(40)) + '''''''', ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
                begin
                    select @cast_str = N' ISNULL('''''''' + CONVERT(nvarchar(40), ' + quotename(@colname) + N', 112) + N'' '' +  CONVERT(nvarchar(40), ' + quotename(@colname) + N', 114) + '''''''', ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('date','time','datetime2','datetimeoffset'))
                begin
                    select @cast_str = N' ISNULL('''''''' + CONVERT(nvarchar(40), ' + quotename(@colname) + N', 121) + '''''''', ''null'') '
                end
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
                begin
                    select @cast_str = N' ISNULL(sys.fn_sqlvarbasetostr(' + quotename(@colname) + N' ) collate database_default, ''null'') '
                end					
                else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
                begin
                    select @cast_str = N' ISNULL(''N'''''' + CAST(' + quotename(@colname) + N' as nvarchar(max)) + '''''''', ''null'') '
                end
                else
                begin
                    select @cast_str = N' ISNULL(CAST(' + quotename(@colname) + N' as nvarchar), ''null'') '
                end
                
                -- Handling general fixed type column cases
                
                if (@num_col = 1)
                begin
                    select @column_string = N'
            ' + @cast_str
                end
                else
                begin
                    select @column_string = N'
            + '','' + ' + @cast_str
                end
                
                -- script out the column string
                
                insert into #proctext(procedure_text) values( @column_string )
            end -- if not like timestamp
        end -- replicated column that is not computed
        
        -- Server has query processing limitation on number of expressions
        -- Refer to 285603 - So break down the select to every 100 columns
        -- processed so far
        
        if (@num_col > 1 and (@num_col % 100) = 0)
        begin
            
            -- Done processing all the columns
            -- complete the compensating command where clause
            
            select @cmd = N'
        from ' + @qualname
            insert into #proctext(procedure_text) values( @cmd )
            
            -- script the where clause
            
            if (@proctype in (@typeusenew_pk,@typeuseold_pk))
            begin
                
                -- only PK unique key
                
                if (@proctype = @typeuseold_pk)
                exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0, 'del'
                else
                exec sp_MSscript_where_clause @objid, @artid, 'new_pk_q', NULL, 0, 'ins'
            end
            else
            begin
                
                -- we are in the cursor for qualifying row
                
                exec sp_scriptpkwhereclause @src_objid = @objid
                            ,@artid = @artid
                            ,@prefix = N'@pkc'
                            ,@artcolcounter = 2
            end
            
            -- prepare the command string for processing further columns
            
            select @cmd = N'

        select @cmd = @cmd  '
            insert into #proctext(procedure_text) values( @cmd )
        end
        
        -- process the next column
        
        fetch #hccolid into @this_col, @collen
    end
    close #hccolid
    deallocate #hccolid
    
    -- Done processing all the columns
    -- complete the compensating command where clause
    
    if (@num_col > 1 and (@num_col % 100) != 0)
    begin
        select @cmd = N'
        from ' + @qualname
        insert into #proctext(procedure_text) values( @cmd )
        
        -- script the where clause
        
        if (@proctype in (@typeusenew_pk,@typeuseold_pk))
        begin
            
            -- only PK unique key
            
            if (@proctype = @typeuseold_pk)
            exec sp_MSscript_where_clause @objid, @artid, 'upd version', NULL, 0, 'del'
            else
            exec sp_MSscript_where_clause @objid, @artid, 'new_pk_q', NULL, 0, 'ins'
        end
        else
        begin
            
            -- we are in the cursor for qualifying row
            
            exec sp_scriptpkwhereclause @src_objid = @objid
                        ,@artid = @artid
                        ,@prefix = N'@pkc'
                        ,@artcolcounter = 2
        end
    end
    
    -- Script the compensating send
    
    exec sp_MSscript_compensating_send @pubid, @artid, 0, 1
    
    -- More scripting for refresh command processing modes
    
    if (@proctype in (@typeuseoldnonpkkeys,@typeusenewnonpkkeys
                            ,@typeuseoldallkeys,@typeusenewallkeys))
    begin
        
        -- script the cursor fetch
        
        select @cmd = N'
            fetch #hccompins into '
        insert into #proctext(procedure_text) values( @cmd )
        insert into #proctext(procedure_text)
        select procedure_text from @pkfetch order by c1 asc
        
        -- script the cursor close and deallocate
        
        select @cmd = N'
            end
            close #hccompins
            deallocate #hccompins '
        insert into #proctext(procedure_text) values( @cmd )
    end
    else
    begin
        
        -- PK cases only
        -- post processing for horizontal filter case
        
        if (@fprocesshfilter=1)
        begin
            select @cmd = N'
        		end'
            insert into #proctext(procedure_text) values( @cmd )
        end
    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscript_delete_pubwins (Procedure)
sp_MSscript_insert_pubwins (Procedure)
sp_MSscript_update_pubwins (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