Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_scriptupdproccore

  No additional text.


Syntax
create procedure sys.sp_scriptupdproccore
(
    @artid int                  -- id of the article we are processing
    ,@format tinyint         -- 1 = CALL, 2 = MCALL, 3 = XCALL, 4 = VCALL, 5 = SCALL
    ,@mode tinyint          -- 1 = static scripting, 2 = dynamic scripting
    ,@publishertype tinyint=1        -- 1 = mssqlserver, 2 = heterogeneous
    ,@publisher sysname = NULL		-- is only non-NULL if @publishertype = 2
)
as
begin
    declare @cmd nvarchar(4000)
                ,@dest_owner nvarchar(255)
                ,@dest_tabname sysname
                ,@src_objid int
                ,@upd_cmd nvarchar(255)
                ,@dest_proc nvarchar(255)
                ,@this_col int
                ,@art_col int
                ,@pkart_col int
                ,@isset int
                ,@isvalidcolumn tinyint
                ,@pkcomputed int
                ,@colname sysname
                ,@typestring nvarchar(255)
                ,@spacer nvarchar(10)
                ,@pubid int
                ,@allow_queued_tran bit
                ,@isallreplcolpk int
                ,@bytestr nvarchar(10)
                ,@bitstr nvarchar(10)
                ,@param_count  int
                ,@updsub_check bit
                ,@qwhere_string nvarchar(4000)
                ,@fscriptidentity bit
                ,@pk_is_identity bit
                ,@commandprefix nvarchar(5)
                ,@repl_freq tinyint
                ,@publication sysname
                ,@fhasnonpkuniquekeys int
                ,@rc int
                ,@fskipallcolumns bit
                ,@userdefinedtypestobasetypes bit
                ,@xmltontext bit
                ,@maxtypestomatchingnonmaxtypes bit
                ,@newdatetimetypestostrings bit
                ,@hierarchyidtovarbinarymax bit
                ,@largeuserdefinedtypestovarbinarymax bit
                ,@spatialtypestovarbinarymax bit
                ,@schema_option bigint
                ,@maptimestamp bit
                ,@article sysname
                ,@unquoted_proc_name sysname
    
    -- constants
    
                ,@callformat tinyint
                ,@mcallformat tinyint
                ,@xcallformat tinyint
                ,@vcallformat tinyint
                ,@scallformat tinyint
                ,@staticmode tinyint
                ,@dynamicmode tinyint
                ,@pubtypemssqlserver tinyint
                ,@pubtypeheterogeneous tinyint

    
    -- initialize
    
    set nocount on
    select @callformat = 1
            ,@mcallformat = 2
            ,@xcallformat = 3
            ,@vcallformat = 4
			,@scallformat = 5
            ,@staticmode = 1
            ,@dynamicmode = 2
            ,@pubtypemssqlserver = 1
            ,@pubtypeheterogeneous = 2
            ,@userdefinedtypestobasetypes = 0
            ,@xmltontext = 0
            ,@maxtypestomatchingnonmaxtypes = 0
            ,@newdatetimetypestostrings = 0
            ,@hierarchyidtovarbinarymax = 0
            ,@largeuserdefinedtypestovarbinarymax = 0
            ,@spatialtypestovarbinarymax = 0
            ,@maptimestamp = 1
    
    -- validate the article
    
    if not exists( select * from sysarticles where artid = @artid AND (type & 1) = 1 )
    begin
        raiserror (14155, 16, 1 )
        return 1
    end
    
    -- validate @format
    
    if (@format not in (@callformat, @mcallformat, @xcallformat, @vcallformat, @scallformat))
    begin
        raiserror(21402, 16, 11, '@format')
        return (1)
    end
    
    -- validate @mode
    
    if (@mode not in (@staticmode, @dynamicmode))
    begin
        raiserror(21402, 16, 12, '@mode')
        return (1)
    end
    
    -- validate @publishertype
    
    if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
    begin
        raiserror(21402, 16, 13, '@publishertype')
        return (1)
    end
    
    -- validate @publisher
    
    if (@publishertype = @pubtypemssqlserver)
    begin
        
        -- For SQL Server publishers, set @publisher to local server
        
        if (@publisher IS NOT NULL)
        begin
            raiserror(21424, 16, 7)
            return (1)
        end
        select @publisher = upper(publishingservername())
    end
    else if (@publishertype = @pubtypeheterogeneous)
    begin
        if (@publisher IS NULL)
        begin
            raiserror(21425, 16, 7)
            return (1)
        end
    end
    
    -- create tables used for scripting
    
    create table #proctext ( c1 int identity not null, procedure_text nvarchar(4000) collate database_default null )
    if (@mode = @dynamicmode)
    begin
        create table #proctext_params ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default )
        create table #proctext_paramdef ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default )
    end
    
    -- get sysarticles information
    
    select @pubid = pubid
            ,@dest_owner = case when (dest_owner is null)
                    then N'' else quotename( dest_owner ) + N'.' end
            ,@dest_tabname = dest_table
            ,@src_objid = case when (@publishertype = @pubtypemssqlserver) then objid else artid end
            ,@upd_cmd = upd_cmd
            ,@fscriptidentity = case when (@publishertype = @pubtypemssqlserver and (cast(schema_option as int) & 0x4) != 0 ) then 1 else 0 end
            ,@schema_option = convert(bigint, schema_option)
            ,@article = name
    from sysarticles
    where artid = @artid

    if (@schema_option & 0x0000000000000020) = 0x0000000000000020 -- UserDefinedTypesToBaseTypes
    begin
        set @userdefinedtypestobasetypes = 1
    end

    if (@schema_option & 0x0000000010000000) = 0x0000000010000000 -- XmlToNText
    begin
        set @xmltontext = 1
    end

    if (@schema_option & 0x0000000020000000) = 0x0000000020000000 -- MaxTypesToMatchingNonMaxTypes
    begin
        set @maxtypestomatchingnonmaxtypes = 1
    end

    if (@schema_option & 0x0000000000000008) = 0x0000000000000008 -- replicate timestamp as timestamp
    begin
        set @maptimestamp = 0
    end
    if (@schema_option & 0x0000002000000000) = 0x0000002000000000 -- HierarchyIdToVarBinaryMax
    begin
        set @hierarchyidtovarbinarymax = 1
    end

    if (@schema_option & 0x0000001000000000) = 0x0000001000000000 -- LargeUserDefinedTypesToVarBinaryMax
    begin
        set @largeuserdefinedtypestovarbinarymax = 1
    end

    if (@schema_option & 0x0000000200000000) = 0x0000000200000000 -- NewDateTimeTypesToStrings
    begin
        set @newdatetimetypestostrings = 1
    end

    if (@schema_option & 0x0000008000000000) = 0x0000008000000000 -- SpatialTypesToVarBinaryMax
    begin
        set @spatialtypestovarbinarymax = 1
    end

    
    -- do we have the right call format
    
    select @commandprefix = case
                    when (@format = @callformat) then N'CALL'
                    when (@format = @mcallformat) then N'MCALL'
                    when (@format = @xcallformat) then N'XCALL'
                    when (@format = @vcallformat) then N'VCALL'
					when (@format = @scallformat) then N'SCALL'
                    else null end
    if ( 1 != charindex( @commandprefix, upper(@upd_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @upd_cmd is null
    begin
        raiserror (14156, 16, 1, N'update', @commandprefix)
        return 1
    end
    
    -- get publication information
    
    select @allow_queued_tran = allow_queued_tran
            ,@updsub_check = (ISNULL(allow_sync_tran, 0) |ISNULL(allow_queued_tran, 0))
            ,@repl_freq = repl_freq
            ,@publication = name
    from syspublications
    where pubid = @pubid
    
    -- Do not support snapshot publications
    
    if (@repl_freq = 1)
    begin
        raiserror(21515, 16, -1, @publication)
        return 1
    end
    
    -- get destination proc name, the format stored inside sysarticles: XXXX [dbo].[xxxxxxxx]
    
    select @dest_proc = case when (@format = @callformat)
            then substring( @upd_cmd, 6, len( @upd_cmd ) - 4 )
            else substring( @upd_cmd, 7, len( @upd_cmd ) - 5 ) end

    select @unquoted_proc_name = parsename(@dest_proc, 1)

    
    -- script the dropping and creation of the proc
    
        select @cmd = N'if object_id(N''' + sys.fn_replreplacesinglequote(@dest_proc) collate database_default + N''', ''P'') > 0
    drop proc ' + @dest_proc
    insert into #proctext(procedure_text) values( @cmd )

    --once we drop the proc, clean up MSreplication_objects table as well
    insert into #proctext(procedure_text) values( N'go' )
    select @cmd = N'if object_id(N''dbo.MSreplication_objects'') is not null delete from dbo.MSreplication_objects where object_name = N''' + sys.fn_replreplacesinglequote(@unquoted_proc_name) collate database_default + N''''
    insert into #proctext(procedure_text) values( @cmd )

    insert into #proctext(procedure_text) values( N'go' )
    insert into #proctext( procedure_text ) values (  N'create procedure ' + @dest_proc + N' ')
    
    -- construct parameter list
    
    if (@format = @xcallformat)
    begin
        exec sys.sp_scriptupdateparams @src_objid = @src_objid
                                            ,@artid = @artid
                                            ,@artcolcounter = 1
                                            ,@param_count = @param_count output
                                            ,@publishertype = @publishertype
                                            ,@userdefinedtypestobasetypes = @userdefinedtypestobasetypes
                                            ,@xmltontext = @xmltontext
                                            ,@maxtypestomatchingnonmaxtypes = @maxtypestomatchingnonmaxtypes
                                            ,@maptimestamp = @maptimestamp
                                            ,@newdatetimetypestostrings = @newdatetimetypestostrings
                                            ,@hierarchyidtovarbinarymax = @hierarchyidtovarbinarymax
                                            ,@largeuserdefinedtypestovarbinarymax = @largeuserdefinedtypestovarbinarymax
                                            ,@spatialtypestovarbinarymax = @spatialtypestovarbinarymax
    end
    else if (@format = @scallformat)
    begin
        exec sys.sp_scriptupdateparams @src_objid = @src_objid
                                            ,@artid = @artid
                                            ,@artcolcounter = 0
                                            ,@publishertype = @publishertype
                                            ,@userdefinedtypestobasetypes = @userdefinedtypestobasetypes
                                            ,@xmltontext = @xmltontext
                                            ,@maxtypestomatchingnonmaxtypes = @maxtypestomatchingnonmaxtypes
                                            ,@maptimestamp = @maptimestamp
                                            ,@setdefaultnulls = 1
                                            ,@newdatetimetypestostrings = @newdatetimetypestostrings
                                            ,@hierarchyidtovarbinarymax = @hierarchyidtovarbinarymax
                                            ,@largeuserdefinedtypestovarbinarymax = @largeuserdefinedtypestovarbinarymax
                                            ,@spatialtypestovarbinarymax = @spatialtypestovarbinarymax
    end
    else
    begin
        exec sys.sp_scriptupdateparams @src_objid = @src_objid
                    ,@artid = @artid
                                            ,@artcolcounter = 0
                                            ,@publishertype = @publishertype
                                            ,@userdefinedtypestobasetypes = @userdefinedtypestobasetypes
                                            ,@xmltontext = @xmltontext
                                            ,@maxtypestomatchingnonmaxtypes = @maxtypestomatchingnonmaxtypes
                                            ,@maptimestamp = @maptimestamp
                                            ,@newdatetimetypestostrings = @newdatetimetypestostrings
                                            ,@hierarchyidtovarbinarymax = @hierarchyidtovarbinarymax
                                            ,@largeuserdefinedtypestovarbinarymax = @largeuserdefinedtypestovarbinarymax
                                            ,@spatialtypestovarbinarymax = @spatialtypestovarbinarymax
    end
    
    -- For VCALL - additional parameter processing
    
    if (@format = @vcallformat)
    begin
        
        -- add old version parameter
        
        insert into #proctext(procedure_text) values( N',@old_msrepl_tran_version uniqueidentifier ' )
    end
    
    -- For MCALL/VCALL/SCALL - additional parameter processing
    
    if (@format in (@mcallformat, @vcallformat, @scallformat))
    begin
        
        -- script the parameter for changed data bitmap
        -- Note that bitmap size is based on number of article columns
        -- (computed by loop below) not source table columns
        
        select @art_col = 1
        if (@publishertype = @pubtypemssqlserver)
        begin
            declare #hccolid cursor local fast_forward for
                select column_id from sys.columns where object_id = @src_objid order by column_id asc
        end
        else
        begin
            declare #hccolid cursor local fast_forward for
                select colid from IHsyscolumns where id = @src_objid order by colorder asc
        end
        open #hccolid
        fetch #hccolid into @this_col
        while (@@fetch_status != -1)
        begin
           exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
            if (@publishertype = @pubtypemssqlserver)
                select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed !=1) then 1 else 0 end
            else
                select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid ) then 1 else 0 end
            if @isset = 1 and @isvalidcolumn = 1
            begin
                select @art_col = @art_col + 1
            end
            fetch #hccolid into @this_col
        end
        close #hccolid
        deallocate #hccolid
        select @cmd = N',@bitmap binary(' + convert(nvarchar(10),1+(@art_col-1) / 8) + N')'
        insert into #proctext(procedure_text) values( @cmd )
    end
    
    -- continue scripting
    
    insert into #proctext(procedure_text) values ( N'as' )
    insert into #proctext(procedure_text) values ( N'begin' )
    
    -- If we are a part of updating subscriber publication
    
    if (@updsub_check = 1)
    begin
        
        -- Check if we have non PK unique keys
        
        exec @fhasnonpkuniquekeys = sp_repltablehasnonpkuniquekey @tabid = @src_objid
        if (@fhasnonpkuniquekeys = 1)
        begin
            
            -- There are non PK unique keys
            -- update only if updated values of non PK unique key(s) do not exist
            -- We support scripting for XCALL and VCALL
            
            select @cmd = N'
if not exists (select * from ' + @dest_owner + quotename(@dest_tabname) + N' '
            insert into #proctext(procedure_text) values( @cmd )
            if (@format = @xcallformat)
            begin
                exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
                                                                ,@artid = @artid
                                                                ,@prefix = '@c'
                                                                ,@mode = 3
                                                                ,@paramcount = @param_count
                                                                ,@skipindexesonudtcolumns = @userdefinedtypestobasetypes
            end
            else
            begin
                exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
                                                                ,@artid = @artid
                                                                ,@prefix = '@c'
                                                                ,@mode = 2
                                                                ,@skipindexesonudtcolumns = @userdefinedtypestobasetypes
            end
            if (@@error != 0 or @rc != 0)
                return 1
            select @cmd = N')
begin'
            insert into #proctext(procedure_text) values( @cmd )
        end
    end
    
    -- check if PK columns are computed
    -- check if all article columns are PK
    
    exec @pkcomputed = sys.sp_MSareallcolpkcomputed @src_objid, @publishertype
    exec @isallreplcolpk = sys.sp_MSisallreplcolpk @artid, @publishertype
    select @pk_is_identity = 0
    
    --====================================================
    -- The static processing update proc body will be scripted as follows:
    -- CALL format
    --  if not (@c1 = @pkc1 and @c2 = @pkc2 and ... )
    --  begin
    --      script update clause with PK columns
    --  end
    --  else
    --  begin
    --      script update clause without PK columns
    --  end
    --  MCALL/VCALL/SCALL format
    --  if (substring(@bitmap,1,1) & 1 = 1 or substring(@bitmap,1,2) & 2 = 2 or ... )
    --  begin
    --      script update clause with PK columns
    --  end
    --  else
    --  begin
    --      script update clause without PK columns
    --  end
    --  XCALL format
    --  if not (@c5 = @c1 and @c6 = @c2 and ... )
    --  begin
    --      script update clause with PK columns
    --  end
    --  else
    --  begin
    --      script update clause without PK columns
    --  end
    
    -- note that if all the article columns are PK columns, we will
    -- construct the 'update all columns including PK columns' statement
    -- w/o a preceeding IF, and we will NOT construct the 'only update non-pk columns'
    -- part of the procedure
    
    -- For SQL Server 7.0 and 2000 - PK update is logged as DELETE + INSERT
    -- For Yukon - singleton PK updates will be logged as UPDATE
    
    -- For dynamic update scripting, we will support CALL/MCALL/XCALL/VCALL/SCALL.
    -- For CALL format, since we do not know if the columns are updated,
    -- we will script out all the columns.
    
    -- The dynamic processing update proc body will be scripted as follows:
    -- CALL format
    --  select @stmt = 'update  set '
    --  script update clause for each column and append to @stmt
    --  MCALL/VCALL/SCALL format
    --  select @stmt = 'update 
set ' -- if (substring(@bitmap,1,1) & 1 = 1 -- begin -- script update clause for this column and append to @stmt -- end -- ... -- script sp_executesql for this statement with parameter definition and list -- XCALL format -- select @stmt = 'update
set ' -- if not (@c5 = @c1) -- begin -- script update clause for this column and append to @stmt -- end -- ... -- script sp_executesql for this statement with parameter definition and list --==================================================== -- static mode processing -- construct test to see if pk has changed -- only do this if the article has columns not included in the pk if @isallreplcolpk = 0 and @pkcomputed = 0 and @mode = @staticmode begin -- we are replicating more than just pk columns and not all pk columns are computed -- For static processing : construct IF statement to examine colbitmap and determine -- if a primary key column has been updated. -- For static : Check if the all primary keys are identity columns select @art_col = 1 ,@cmd = case when (@format in (@callformat, @xcallformat)) then N'if not (' else N'if (' end ,@pkart_col = 1 ,@spacer = N' ' ,@pk_is_identity = 1 if (@publishertype = @pubtypemssqlserver) begin declare #hccolid cursor local fast_forward for select column_id from sys.columns where object_id = @src_objid order by column_id asc end else begin declare #hccolid cursor local fast_forward for select colid from IHsyscolumns where id = @src_objid order by colorder asc end open #hccolid fetch #hccolid into @this_col -- process each column while (@@fetch_status != -1) begin exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype if (@publishertype = @pubtypemssqlserver) select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed !=1) then 1 else 0 end else select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid ) then 1 else 0 end if @isset = 1 and @isvalidcolumn = 1 begin -- column marked for replication and is not computed exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype if @isset != 0 begin -- We have a PK column -- skip identity columns that are scripted as identity on subscriber if not (@publishertype = @pubtypemssqlserver and columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1 and @fscriptidentity = 1) begin -- Process this PK column select @pk_is_identity = 0 -- script the part for IF clause based on call formats if (@format = @callformat) begin select @cmd = @cmd + @spacer + N'@c'+convert( nvarchar(10), @art_col ) + N' = @pkc' + convert( nvarchar(10), @pkart_col ) select @spacer = N' and ' ,@pkart_col = @pkart_col + 1 end else if (@format in (@mcallformat, @vcallformat, @scallformat)) begin select @bytestr = convert( nvarchar(10), 1 + (@art_col-1) / 8 ) ,@bitstr = convert( nvarchar(10), power(2, (@art_col-1) % 8 ) ) select @cmd = @cmd + @spacer + N'substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' = ' + @bitstr select @spacer = N' or ' end else if (@format = @xcallformat) begin select @cmd = @cmd + @spacer + N'@c'+convert( nvarchar(10), @art_col + @param_count/2) + N' = @c' + convert( nvarchar(10), @art_col ) select @spacer = N' and ' end -- flush if necessary if len( @cmd ) > 3000 begin insert into #proctext(procedure_text) values( @cmd ) select @cmd = N'' end end -- not identity end -- PK column select @art_col = @art_col + 1 end -- column marked for replication and not computed fetch #hccolid into @this_col end -- process each column while block close #hccolid deallocate #hccolid select @cmd = @cmd + N' )' -- Check if we need to script this check -- If all PK column are identity then there is no need if @pk_is_identity = 0 begin insert into #proctext(procedure_text) values( @cmd ) insert into #proctext(procedure_text) values( N'begin' ) end end -- if @isallreplcolpk = 0 and @pkcomputed = 0 and @mode = @staticmode -- construct update statement including PK columns -- we skip this processing block for dynamic processing if (@mode = @staticmode) begin -- static mode processing select @fskipallcolumns=1 ,@art_col = 1 ,@spacer = N' ' if (@publishertype = @pubtypemssqlserver) begin declare #hccolid cursor local fast_forward for select column_id, name from sys.columns where object_id = @src_objid order by column_id asc end else begin declare #hccolid cursor local fast_forward for select colid, name from IHsyscolumns where id = @src_objid order by colorder asc end open #hccolid fetch #hccolid into @this_col, @colname -- process each column while (@@fetch_status != -1) begin exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype if (@publishertype = @pubtypemssqlserver) select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed !=1) then 1 else 0 end else select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid ) then 1 else 0 end if @isset = 1 and @isvalidcolumn = 1 begin -- column marked for replication and is not computed -- skip identity columns that are scripted as identity on subscriber if not (@publishertype = @pubtypemssqlserver and columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1 and @fscriptidentity = 1) begin -- process this column -- if this is the first processing then script the update command if (@fskipallcolumns = 1) begin insert into #proctext(procedure_text) values( N'update ' + @dest_owner + quotename(@dest_tabname) + N' set ' ) select @fskipallcolumns = 0 end -- script the part for PK included update clause based on call formats if (@format = @callformat) begin insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = @c' + convert( nvarchar(10), @art_col ) ) end else if (@format in (@mcallformat, @vcallformat, @scallformat)) begin select @bytestr = convert( nvarchar(10), 1 + (@art_col-1) / 8 ) ,@bitstr = convert( nvarchar(10), power(2, (@art_col-1) % 8 ) ) if sys.fn_isxmlcolumn(@src_objid, @this_col) = 1 and @xmltontext = 0 insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar(10), @art_col ) + N' else convert(xml, ' + quotename(@colname) + N') end' ) else insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar(10), @art_col ) + N' else ' + quotename(@colname) + N' end' ) end else if (@format = @xcallformat) begin insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = @c' + convert( nvarchar(10), @art_col + @param_count/2) ) -- processing for updating subscribers:if this is the row version column : need to add to where clause if ((@updsub_check = 1) and (@colname = N'msrepl_tran_version')) select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar(10), @art_col ) end select @spacer = N',' end -- column not identity select @art_col = @art_col + 1 end -- column marked for replication and not computed fetch #hccolid into @this_col, @colname end -- process each column while block close #hccolid deallocate #hccolid -- script the where clause for this update statement based on calling format -- and if we processed any columns if (@fskipallcolumns = 0) begin if (@format = @xcallformat) begin exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@prefix = N'@c' ,@artcolcounter = 1 ,@publishertype = @publishertype if (@updsub_check = 1) insert into #proctext(procedure_text) values( @qwhere_string ) end else if (@format = @vcallformat) begin exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@publishertype = @publishertype if (@updsub_check = 1) insert into #proctext(procedure_text) values( N' and msrepl_tran_version = @old_msrepl_tran_version ' ) end else begin exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@publishertype = @publishertype end if (@updsub_check != 1) exec sys.sp_MSscript_missing_row_check end -- if (@fskipallcolumns = 0) else begin -- no columns were selected for the update insert into #proctext(procedure_text) values ('return 0') end end -- if (@mode = @staticmode) -- For static mode processing - construct update statement without the PK columns -- for case when PK is not identity and not computed. -- For dynamic mode processing this is the main block and we will script out code -- for all the columns -- Note: @art_col will be updated separately for static mode and dynamic mode if (@isallreplcolpk = 0 and ((@mode = @staticmode and @pk_is_identity = 0 and @pkcomputed = 0) or (@mode = @dynamicmode))) begin -- continue scripting if (@mode = @staticmode) begin -- static mode scripting insert into #proctext(procedure_text) values( N'end' ) insert into #proctext(procedure_text) values( N'else' ) insert into #proctext(procedure_text) values( N'begin' ) end else begin -- dynamic mode scripting insert into #proctext(procedure_text) values(N'declare @stmt nvarchar(4000), @spacervar nvarchar(1)') insert into #proctext(procedure_text) values(N'select @spacervar =N''''') select @pkart_col = 1 end -- process for each column select @art_col = 1 ,@fskipallcolumns=1 ,@spacer = N' ' if (@publishertype = @pubtypemssqlserver) begin declare #hccolid cursor local fast_forward for select column_id, name from sys.columns where object_id = @src_objid order by column_id asc end else begin declare #hccolid cursor local fast_forward for select colid, name from IHsyscolumns where id = @src_objid order by colorder asc end open #hccolid fetch #hccolid into @this_col, @colname while (@@fetch_status != -1) begin exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype if (@publishertype = @pubtypemssqlserver) select @isvalidcolumn = case when exists (select name from sys.columns where object_id=@src_objid and @this_col=column_id and is_computed !=1) then 1 else 0 end else select @isvalidcolumn = case when exists (select name from IHsyscolumns where id=@src_objid and @this_col=colid ) then 1 else 0 end if @isset = 1 begin -- column is marked for replication exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype if @isset = 0 begin -- processing a non PK column -- skip computed columns -- skip identity columns that are scripted as identity on subscriber if not (@publishertype = @pubtypemssqlserver and columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1 and @fscriptidentity = 1) and @isvalidcolumn = 1 begin -- process according to scripting mode if (@mode = @staticmode) begin -- static mode scripting -- if we are processing the first column - script the update statement if (@fskipallcolumns = 1) begin insert into #proctext(procedure_text) values( N'update ' + @dest_owner + quotename(@dest_tabname) + N' set ' ) select @fskipallcolumns = 0 end -- process based on formats if (@format = @callformat) begin -- CALL format insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = @c' + convert( nvarchar(10), @art_col ) ) end -- end CALL format else if (@format in (@mcallformat, @vcallformat, @scallformat)) begin -- MCALL/VCALL/SCALL format select @bytestr = convert( nvarchar(10), 1 + (@art_col-1) / 8 ) ,@bitstr = convert( nvarchar(10), power(2, (@art_col-1) % 8 ) ) -- If the target column is an xml column, wrap it in a covert(xml,) to untype it -- so all return values in the case statements are guaranteed to be uniformly untyped. if sys.fn_isxmlcolumn(@src_objid, @this_col) = 1 and @xmltontext = 0 insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar(10), @art_col ) + N' else convert(xml, ' + quotename(@colname) + N') end' ) else insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = case substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' when ' + @bitstr + N' then ' + N'@c'+ convert( nvarchar(10), @art_col ) + N' else ' + quotename(@colname) + N' end' ) end -- end MCALL/VCALL/SCALL format else if (@format = @xcallformat) begin -- XCALL format insert into #proctext(procedure_text) values ( @spacer + quotename(@colname) + N' = @c' + convert( nvarchar(10), @art_col + @param_count/2) ) -- processing for updating subscribers:if this is the row version column : need to add to where clause if ((@updsub_check = 1) and (@colname = N'msrepl_tran_version')) select @qwhere_string = N' and msrepl_tran_version = @c' + convert( nvarchar(10), @art_col ) end -- end XCALL format end -- end static mode else begin -- dynamic mode scripting -- if we are processing the first column - script the update statement if (@fskipallcolumns = 1) begin insert into #proctext(procedure_text) values( N'select @stmt = N''update ' + sys.fn_replreplacesinglequote(@dest_owner + quotename(@dest_tabname)) collate database_default + N' set ''') select @fskipallcolumns = 0 end -- script the check and the dynamic column update -- based on format if (@format = @callformat) begin -- CALL format insert into #proctext(procedure_text) values ( N'select @stmt = @stmt + @spacervar + N''' + sys.fn_replreplacesinglequote(quotename(@colname)) collate database_default + N''' + N''=@'+ convert( nvarchar(10), @art_col ) + N'''') insert into #proctext(procedure_text) values (N'select @spacervar = N'',''') end -- end CALL format else if (@format in (@mcallformat, @vcallformat, @scallformat)) begin -- MCALL/VCALL/SCALL format select @bytestr = convert( nvarchar(10), 1 + (@art_col-1) / 8 ) ,@bitstr = convert( nvarchar(10), power(2, (@art_col-1) % 8 ) ) insert into #proctext(procedure_text) values ( N'if substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' = ' + @bitstr) insert into #proctext(procedure_text) values (N'begin') insert into #proctext(procedure_text) values ( N'select @stmt = @stmt + @spacervar + N''' + sys.fn_replreplacesinglequote(quotename(@colname)) collate database_default + N''' + N''=@'+ convert( nvarchar(10), @art_col ) + N'''') insert into #proctext(procedure_text) values (N'select @spacervar = N'',''') insert into #proctext(procedure_text) values (N'end') end -- end MCALL/VCALL/SCALL format else if (@format = @xcallformat) begin -- XCALL format insert into #proctext(procedure_text) values ( N'if not ( @c' + convert( nvarchar(10), @art_col + @param_count/2) + N' = @c' + convert( nvarchar(10), @art_col ) + N' ) ') insert into #proctext(procedure_text) values (N'begin') insert into #proctext(procedure_text) values ( N'select @stmt = @stmt + @spacervar + N''' + sys.fn_replreplacesinglequote(quotename(@colname)) collate database_default + N''' + N''=@'+ convert( nvarchar(10), @art_col ) + N'''') insert into #proctext(procedure_text) values (N'select @spacervar = N'',''') insert into #proctext(procedure_text) values (N'end') -- processing for updating subscribers:if this is the row version column : need to add to where clause if ((@updsub_check = 1) and (@colname = N'msrepl_tran_version')) select @qwhere_string = N' and msrepl_tran_version = @' + convert( nvarchar(10), @art_col ) end -- end XCALL format -- script the parameter list for the sp_executesql based on format if (@format in (@callformat, @mcallformat, @vcallformat, @scallformat)) begin -- CALL/MCALL/VCALL/SCALL - @c insert into #proctext_params(procedure_text) values( @spacer + N'@c' + convert( nvarchar(10), @art_col )) end else if (@format = @xcallformat) begin -- XCALL - use second set of column vars insert into #proctext_params(procedure_text) values( @spacer + N'@c' + convert( nvarchar(10), @art_col + @param_count/2 )) end -- script the parameter definition list for sp_executesql select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax , @largeuserdefinedtypestovarbinarymax, @spatialtypestovarbinarymax) insert into #proctext_paramdef(procedure_text) values ( @spacer + N'@' + convert( nvarchar(10), @art_col ) + N' ' + @typestring ) end -- end dynamic mode -- update spacing variable select @spacer = N',' end -- column not marked for identity end -- not a pk column else begin -- processing a PK column -- For static scripting just update art_col counter if (@mode = @dynamicmode) begin -- process dynamic mode here -- if we are processing the first column - script the update statement if (@fskipallcolumns = 1) begin insert into #proctext(procedure_text) values( N'select @stmt = N''update ' + sys.fn_replreplacesinglequote(@dest_owner + quotename(@dest_tabname)) collate database_default + N' set ''') select @fskipallcolumns = 0 end -- process scripting column update -- skip this for computed columns -- skip this for identity columns that are scripted as identity on subscriber if not (@publishertype = @pubtypemssqlserver and columnproperty(@src_objid, col_name( @src_objid, @this_col), 'IsIdentity') = 1 and @fscriptidentity = 1) and @isvalidcolumn = 1 begin -- processing a PK column that is not identity and not computed; -- script the check and the dynamic column update -- since we are dealing with PK column - there will be -- the old value and new value for this column for all -- call formats. When updating - we set to the new value -- and for the where clause we use the old value. -- The parameter list and the parameter definition list -- should contain both old and new values. -- Process based on format if (@format = @callformat) begin -- CALL format insert into #proctext(procedure_text) values ( N'select @stmt = @stmt + @spacervar + N''' + sys.fn_replreplacesinglequote(QUOTENAME(@colname)) collate database_default + N''' + N''=@new'+ convert( nvarchar(10), @art_col ) + N'''') insert into #proctext(procedure_text) values (N'select @spacervar = N'',''') end -- end CALL format else if (@format in (@mcallformat, @vcallformat, @scallformat)) begin -- MCALL/VCALL/SCALL format select @bytestr = convert( nvarchar(10), 1 + (@art_col-1) / 8 ) ,@bitstr = convert( nvarchar(10), power(2, (@art_col-1) % 8 ) ) insert into #proctext(procedure_text) values ( N'if substring(@bitmap,' + @bytestr + N',1) & ' + @bitstr + N' = ' + @bitstr) insert into #proctext(procedure_text) values (N'begin') insert into #proctext(procedure_text) values ( N'select @stmt = @stmt + @spacervar + N''' + sys.fn_replreplacesinglequote(quotename(@colname)) collate database_default + N''' + N''=@new'+ convert( nvarchar(10), @art_col ) + N'''') insert into #proctext(procedure_text) values (N'select @spacervar = N'',''') insert into #proctext(procedure_text) values (N'end') end -- end MCALL/VCALL/SCALL format else if (@format = @xcallformat) begin -- XCALL format insert into #proctext(procedure_text) values ( N'if not ( @c' + convert( nvarchar(10), @art_col + @param_count/2) + N' = @c' + convert( nvarchar(10), @art_col ) + N' ) ') insert into #proctext(procedure_text) values (N'begin') insert into #proctext(procedure_text) values ( N'select @stmt = @stmt + @spacervar + N''' + sys.fn_replreplacesinglequote(quotename(@colname)) collate database_default + N''' + N''=@new'+ convert( nvarchar(10), @art_col ) + N'''') insert into #proctext(procedure_text) values (N'select @spacervar = N'',''') insert into #proctext(procedure_text) values (N'end') -- processing for updating subscribers:if this is the row version column : need to add to where clause if ((@updsub_check = 1) and (@colname = N'msrepl_tran_version')) select @qwhere_string = N' and msrepl_tran_version = @' + convert( nvarchar(10), @art_col ) end -- end XCALL format end -- dynamic column update scripting -- script for parameter list for PK column -- we have to script two parameters - the old column value and the new value -- process according to the format if (@format in (@callformat, @mcallformat, @vcallformat, @scallformat)) begin -- script for the old value - @pkc insert into #proctext_params(procedure_text) values( @spacer + N'@pkc' + convert( nvarchar(10), @pkart_col )) -- skip scripting new value for computed columns if (@isvalidcolumn = 1) begin -- script for the new value - @c insert into #proctext_params(procedure_text) values( N',@c' + convert( nvarchar(10), @art_col )) end -- increment @pkart_col select @pkart_col = @pkart_col + 1; end else if (@format = @xcallformat) begin -- script for the old value - @ insert into #proctext_params(procedure_text) values( @spacer + N'@c' + convert( nvarchar(10), @art_col )) -- script for the new value - @c insert into #proctext_params(procedure_text) values( N',@c' + convert( nvarchar(10), @art_col + @param_count/2)) end -- script for parameter definition for PK column -- we have to script two parameters definitions -- one for the old column value and one for the new value select @typestring = sys.fn_gettypestring( @src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @la rgeuserdefinedtypestovarbinarymax, @spatialtypestovarbinarymax) -- script definition for the old value - @ insert into #proctext_paramdef(procedure_text) values ( @spacer + N'@' + convert( nvarchar(10), @art_col ) + N' ' + @typestring ) -- skip scripting new value for computed columns if (@isvalidcolumn = 1) begin -- script definition for the new value - @new insert into #proctext_paramdef(procedure_text) values ( N',@new' + convert( nvarchar(10), @art_col ) + N' ' + @typestring ) end -- update spacing variable for dynamic scripting mode select @spacer = N',' end -- if (@mode = @dynamicmode) end -- PK column -- update art_col for valid columns if (@isvalidcolumn = 1) select @art_col = @art_col + 1 end -- column marked for replication fetch #hccolid into @this_col, @colname end -- cursor while loop close #hccolid deallocate #hccolid -- script the where clause for this update statement -- if any columns were processed if (@fskipallcolumns = 0) begin if (@mode = @staticmode) begin -- static mode processing if (@format = @xcallformat) begin exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@prefix = N'@c' ,@artcolcounter = 1 ,@publishertype = @publishertype if (@updsub_check = 1) insert into #proctext(procedure_text) values( @qwhere_string ) end else if (@format = @vcallformat) begin exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@publishertype = @publishertype if (@updsub_check = 1) insert into #proctext(procedure_text) values( N' and msrepl_tran_version = @old_msrepl_tran_version ' ) end else begin exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@publishertype = @publishertype end if (@updsub_check != 1) exec sys.sp_MSscript_missing_row_check insert into #proctext(procedure_text) values( N'end' ) end -- if (@mode = @staticmode) else begin -- dynamic mode processing declare @startwhereclausescope int ,@endwhereclausescope int -- where clause scripting inherently does not handle escape character -- processing. Since our where clause will be a dynamic statement -- we will need to process the single quotes in for escape characters. -- We mark the beginning and end of the where clause and then -- process it for escape character handling insert into #proctext(procedure_text) values (N'select @stmt = @stmt + N'' ') select @startwhereclausescope = max(c1) from #proctext -- the where clause will need to use the article ordinal. -- Use prefix '@' to be consistent with type def string exec sys.sp_scriptpkwhereclause @src_objid = @src_objid ,@artid = @artid ,@prefix = N'@' ,@artcolcounter = 1 ,@publishertype = @publishertype if (@updsub_check = 1) begin -- for updating subscribers -add the check for version if (@format = @vcallformat) begin insert into #proctext(procedure_text) values(N' and msrepl_tran_version = @oldversion ' ) end else if (@format = @xcallformat) begin insert into #proctext(procedure_text) values( @qwhere_string ) end end select @endwhereclausescope = max(c1) from #proctext update #proctext set procedure_text = sys.fn_replreplacesinglequote(procedure_text) collate database_default where c1 > @startwhereclausescope and c1 <= @endwhereclausescope insert into #proctext(procedure_text) values (N'''') -- script the call to sql_executesql and parameter definitions insert into #proctext(procedure_text) values( N'exec sp_executesql @stmt, N'' ') insert into #proctext(procedure_text) select procedure_text from #proctext_paramdef order by c1 asc if (@updsub_check = 1 and @format = @vcallformat) insert into #proctext(procedure_text) values( N',@oldversion uniqueidentifier ') -- add the parameter list for sp_executesql insert into #proctext(procedure_text) values (N''',') insert into #proctext(procedure_text) select procedure_text from #proctext_params order by c1 asc if (@updsub_check = 1 and @format = @vcallformat) insert into #proctext(procedure_text) values( N',@old_msrepl_tran_version ') -- add missing row check if (@updsub_check != 1) exec sys.sp_MSscript_missing_row_check end -- end dynamic processing mode end -- if (@fskipallcolumns = 0) else begin -- no columns were selected for the update insert into #proctext(procedure_text) values ('return 0') if (@mode = @staticmode) begin insert into #proctext(procedure_text) values( N'end' ) end end end -- if @isallreplcolpk = 0 and both modes check -- End the if exists block for Queued publications if (@updsub_check = 1) and (@fhasnonpkuniquekeys = 1) begin select @cmd = N' end' insert into #proctext(procedure_text) values( @cmd ) end -- end the scripting insert into #proctext(procedure_text) values ( N'end' ) -- script the inserting of proc entry in MSreplication_objects now that proc is created insert into #proctext(procedure_text) values( N'go' ) select @cmd = N'if columnproperty(object_id(N''dbo.MSreplication_objects''), N''article'', ''AllowsNull'') is not null ' insert into #proctext(procedure_text) values( @cmd ) select @cmd = 'insert dbo.MSreplication_objects (object_name, publisher, publisher_db, publication, article, object_type) values ( + N''' + sys.fn_replreplacesinglequote(@unquoted_proc_name) collate database_default + N''' ' + N', N''' + sys.fn_replreplacesinglequote(@publisher) collate database_default + N''' ' + N', N''' + sys.fn_replreplacesinglequote(db_name()) collate database_default + N''' ' + N', N''' + sys.fn_replreplacesinglequote(@publication) collate database_default + N''' ' + N', N''' + sys.fn_replreplacesinglequote(@article) collate database_default + N''' ' + N',''P'')' select @cmd = 'exec (''' + sys.fn_replreplacesinglequote(@cmd) + ''')' insert into #proctext(procedure_text) values( @cmd ) -- flush to client select procedure_text from #proctext order by c1 asc -- all done return (0) end
 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_scriptdynamicupdproc (Procedure)
sp_scriptmappedupdproc (Procedure)
sp_scriptsupdproc (Procedure)
sp_scriptupdproc (Procedure)
sp_scriptvupdproc (Procedure)
sp_scriptxupdproc (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