Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_scriptdelproccore

  No additional text.


Syntax
create procedure sys.sp_scriptdelproccore
(
    @artid int                  -- id of the article we are processing
    ,@format tinyint         -- 1 = CALL, 3 = XCALL, 4 = VCALL
    ,@mode tinyint          -- 1 = regular scripting, 2 = snapshot reconciliation scripting, 3 = drop reconciliation procs only
    ,@publishertype tinyint=1        -- 1 = mssqlserver, 2 = heterogeneous
    ,@publisher sysname = NULL		-- is only non-NULL if @publishertype = 2
    ,@usesqlclr bit=0
)
as
begin
    declare @cmd          nvarchar(4000)
        ,@dest_owner   nvarchar(255)
		 ,@dest_tabname sysname
        ,@src_objid    int
        ,@del_cmd      nvarchar(255)
        ,@dest_proc    nvarchar(524)
        ,@this_col     int
        ,@art_col      int
        ,@isset        int
        ,@isvalidcolumn tinyint
        ,@pubid        int
        ,@typestring   nvarchar(255)
        ,@colname sysname
        ,@spacer       nvarchar(10)
        ,@updsub_check bit
        ,@qwhere_string nvarchar(4000)
        ,@commandprefix nvarchar(5)
        ,@repl_freq tinyint
        ,@publication sysname
        ,@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
        ,@xcallformat tinyint
        ,@vcallformat tinyint
        ,@regularmode tinyint
        ,@reconciliationmode tinyint
        ,@droponly tinyint
        ,@pubtypemssqlserver tinyint
        ,@pubtypeheterogeneous tinyint

    
    -- initialize
    
    set nocount on
    select @callformat = 1
            ,@xcallformat = 3
            ,@vcallformat = 4
            ,@regularmode = 1
            ,@reconciliationmode = 2
            ,@droponly = 3
            ,@pubtypemssqlserver = 1
            ,@pubtypeheterogeneous = 2
            ,@maptimestamp = 1
            ,@hierarchyidtovarbinarymax = 0
            ,@largeuserdefinedtypestovarbinarymax = 0
            ,@spatialtypestovarbinarymax = 0
            ,@article = NULL
            ,@userdefinedtypestobasetypes = 0
            ,@xmltontext = 0
            ,@maxtypestomatchingnonmaxtypes = 0
            ,@newdatetimetypestostrings = 0

    
    -- validate @artid
    
    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, @xcallformat, @vcallformat))
    begin
        raiserror(21402, 16, 8, '@format')
        return (1)
    end
    
    -- validate @mode
    
    if (@mode not in (@regularmode, @reconciliationmode, @droponly))
    begin
        raiserror(21402, 16, 9, '@mode')
        return (1)
    end
    
    -- validate @publishertype
    
    if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
    begin
        raiserror(21402, 16, 10, '@publishertype')
        return (1)
    end

    
    -- validate @publisher
    
    if (@publisher IS NULL) AND (@publishertype = @pubtypeheterogeneous)
    begin
        raiserror(21425, 16, 7)
        return (1)
    end

    if (@publisher IS NOT NULL) AND (@publishertype = @pubtypemssqlserver)
    begin
        raiserror(21424, 16, 7)
        return (1)
    end

    if (@publishertype = @pubtypemssqlserver)
    begin
		select @publisher = publishingservername()
    end

    
    -- create temp table for scripting
    
    create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    
    -- get sysarticles information
    
    select @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
            ,@del_cmd = del_cmd
            ,@pubid = pubid
            ,@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 & 0x0000000200000000) = 0x0000000200000000 -- NewDateTimeTypesToStrings
    begin
        set @newdatetimetypestostrings = 1
    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 & 0x0000008000000000) = 0x0000008000000000 -- SpatialTypesToVarBinaryMax
    begin
        set @spatialtypestovarbinarymax = 1
    end

    
    -- get publication info
    
    select @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
    
    -- do we have the right call format
    
    select @commandprefix = case
                    when (@format = @callformat) then N'CALL'
                    when (@format = @xcallformat) then N'XCALL'
                    when (@format = @vcallformat) then N'VCALL'
                    else null end
    if ( 1 != charindex( @commandprefix, upper(@del_cmd collate SQL_Latin1_General_CP1_CS_AS) ) ) or @del_cmd is null
    begin
		raiserror (14156, 16, 1, N'delete', @commandprefix)
		return 1
    end
    
    -- get destination proc name, the format stored inside sysarticles: XXXX [dbo].[xxxxxxxx]
    
    select @dest_proc = case when (@format = @callformat)
            then substring( @del_cmd, 6, len( @del_cmd ) - 4 )
            else substring( @del_cmd, 7, len( @del_cmd ) - 5 ) end

    --in ccs reconcile mode we can no longer use ;2 as proc name, append something to the end to make it *unique*
    select @dest_proc = case @mode when @regularmode then @dest_proc
                                else substring(@dest_proc, 1, len( @dest_proc ) -1 ) + N'_msrepl_ccs]'
                                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 )
    --only keep custom procs in MSreplication_objects for easier cleanup, ccs rec procs are cleaned up by logreader so no need for this
    if (@mode = @regularmode)
    begin
        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 )
    end

    --droponly is used by ccs to drop reconciliation proc at the time sync_done token is posted
    
    if (@mode = @droponly)
        goto DROPONLY
    insert into #proctext(procedure_text) values( N'go' )
    insert into #proctext( procedure_text ) values (  N'create procedure ' + @dest_proc + N' ')
    
    -- construct parameter list
    
    select @art_col = 1
            ,@spacer = N' '
            ,@cmd = 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
        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
        
        -- process each column based on format
        
        if (@format in (@callformat, @vcallformat))
        begin
            
            -- processing CALL/VCALL formats
            -- For VCALL we are looking for PK non computed column
            
            exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            if @isset = 1
            begin
                
                -- processing PK column
                
                select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserd
efinedtypestovarbinarymax, @spatialtypestovarbinarymax)
                select @cmd = @cmd + @spacer + N'@pkc' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
                select @art_col = @art_col + 1
                        ,@spacer = N','
            end -- PK column
        end -- CALL/VCALL format
        else
        begin
            
            -- processing XCALL format
            
            -- If this is a computed column, it is only valid if it is part of the primary key
            
            if @isvalidcolumn = 0
                exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            else
                exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
            if @isset = 1
            begin
                
                -- processing column marked for replication that is not computed
                
                select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserd
efinedtypestovarbinarymax, @spatialtypestovarbinarymax)
                select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar(10), @art_col ) + N' ' + @typestring
                
                -- 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, @art_col )
                select @art_col = @art_col + 1
                        ,@spacer = N','
            end -- end column replicated and not computed
        end -- XCALL format
        
        -- flush command as necessary
        
        if len( @cmd ) > 3000
        begin
            insert into #proctext(procedure_text) values( @cmd )
            select @cmd = N' '
        end
        fetch hccolid into @this_col, @colname
    end -- while cursor block
    close hccolid
    deallocate hccolid
    insert into #proctext(procedure_text) values( @cmd )
    
    -- VCALL format : add version parameter for the regular scripting mode
    
    if (@format = @vcallformat) and (@mode = @regularmode)
    begin
        insert into #proctext(procedure_text) values( N',@msrepl_tran_version uniqueidentifier ')
    end
    insert into #proctext(procedure_text) values( N'as ')
    insert into #proctext(procedure_text) values( N'begin ')
    
    -- script the delete statetment
    
    insert into #proctext(procedure_text) values( N'delete ' + @dest_owner + quotename(@dest_tabname) )
    
    -- script the where clause
    
    if (@format in (@callformat, @vcallformat))
    begin
        
        -- processing CALL/VCALL formats
        
        exec sys.sp_scriptpkwhereclause @src_objid = @src_objid
                            ,@artid = @artid
                            ,@publishertype = @publishertype
    end
    else
    begin
        
        -- processing XCALL format
        
        exec sys.sp_scriptpkwhereclause @src_objid = @src_objid
                            ,@artid = @artid
                            ,@prefix = N'@c'
                            ,@artcolcounter = 1
                            ,@publishertype = @publishertype
    end
    
    -- additional processing based on mode and format
    
    if (@mode = @regularmode)
    begin
        
        -- regular mode processing
        
        if (@updsub_check = 0)
        begin
            
            -- processing for SQL Server publisher
            
            if (@publishertype = @pubtypemssqlserver)
            begin
                
                -- regular articles : add missing row check
                
                exec sys.sp_MSscript_missing_row_check
            end
        end
        else
        begin
            
            -- updating subscriber article
            
            if (@format = @xcallformat)
                insert into #proctext(procedure_text) values( @qwhere_string )
            else if (@format = @vcallformat)
                insert into #proctext(procedure_text) values( N' and msrepl_tran_version = @msrepl_tran_version ' )
        end
    end -- end regular mode
    
    -- 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
    
    --only keep custom procs in MSreplication_objects for easier cleanup, ccs rec procs are cleaned up by logreader so no need for this
    if (@mode = @regularmode)
    begin
        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 = N'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 )
    end
DROPONLY:
    
    -- 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_MSpost_auto_proc (Procedure)
sp_replcleanupccsprocs (Procedure)
sp_replpostsyncstatus (Procedure)
sp_scriptdelproc (Procedure)
sp_scriptvdelproc (Procedure)
sp_scriptxdelproc (Procedure)
sp_script_reconciliation_delproc (Procedure)
sp_script_reconciliation_vdelproc (Procedure)
sp_script_reconciliation_xdelproc (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