Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_scriptinsproccore

  No additional text.


Syntax
create procedure sys.sp_scriptinsproccore
(
    @artid int                  -- id of the article we are processing
	,@format tinyint         	-- 1 = CALL, 5 = SCALL
    ,@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
                ,@ins_cmd      nvarchar(255)
                ,@dest_proc    nvarchar(524)
                ,@this_col     int
                ,@art_col      int
                ,@isset         int
                ,@isvalidcolumn tinyint
                ,@pubid       int
                ,@identity_insert bit
                ,@fscriptidentity bit
                ,@rc int
                ,@colname sysname
                ,@typestring   nvarchar(255)
                ,@spacer       nvarchar(1)
                ,@updsub_check bit
                ,@isallreplcolpk int
                ,@repl_freq tinyint
                ,@publication sysname
                ,@userdefinedtypestobasetypes bit
                ,@xmltontext bit
                ,@maxtypestomatchingnonmaxtypes bit
                ,@schema_option bigint
                ,@newdatetimetypestostrings bit
                ,@hierarchyidtovarbinarymax bit
                ,@largeuserdefinedtypestovarbinarymax bit
                ,@spatialtypestovarbinarymax bit
                ,@maptimestamp bit
                ,@article sysname
                ,@unquoted_proc_name sysname
                ,@commandprefix sysname

    
    -- constants
    
                ,@regularmode tinyint
                ,@reconciliationmode tinyint
                ,@droponly tinyint
                ,@pubtypemssqlserver tinyint
                ,@pubtypeheterogeneous tinyint
                ,@callformat tinyint
                ,@scallformat tinyint

    
    -- initialize
    
    set nocount on
    select     @regularmode = 1
                ,@reconciliationmode = 2
                ,@droponly = 3
                ,@pubtypemssqlserver = 1
                ,@pubtypeheterogeneous = 2
                ,@userdefinedtypestobasetypes = 0
                ,@xmltontext = 0
                ,@maxtypestomatchingnonmaxtypes = 0
                ,@hierarchyidtovarbinarymax = 0
                ,@largeuserdefinedtypestovarbinarymax = 0
                ,@spatialtypestovarbinarymax = 0
                ,@maptimestamp = 1
                ,@newdatetimetypestostrings = 0
                ,@article = NULL
                ,@commandprefix = NULL
                ,@callformat = 1
                ,@scallformat = 5
    
    -- 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, @scallformat))
    begin
        raiserror(21402, 16, 11, '@format')
        return (1)
    end
    
    -- validate @mode
    
    if (@mode not in (@regularmode, @reconciliationmode, @droponly))
    begin
        raiserror(21402, 16, 6, '@mode')
        return (1)
    end
    
    -- validate @publishertype
    
    if (@publishertype not in (@pubtypemssqlserver, @pubtypeheterogeneous))
    begin
        raiserror(21402, 16, 7, '@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

    
    -- Validate @mode
    
    if ((@publishertype = @pubtypeheterogeneous) and (@mode = 2))
    begin
        raiserror(21634, 16, 7,  N'@mode', N'snapshot reconciliation scripting (2)', N'regular scripting (1)')
        return (1)
    end

    if (@publishertype = @pubtypemssqlserver)
    begin
		select @publisher = publishingservername()
    end
    
    -- create temp table for command fragments and insert column list
    
    create table #proctext ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #collisttab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null, collist bit)
    
    -- 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
            ,@ins_cmd = ins_cmd
            ,@pubid = pubid
            ,@schema_option = convert(bigint,schema_option)
            ,@fscriptidentity = case when (@publishertype = @pubtypemssqlserver and (cast(schema_option as int) & 0x4) != 0 ) then 1 else 0 end
            ,@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

    
    -- do we have the right call format
    
    select @commandprefix = case @format
	                when @callformat then N'CALL'
					when @scallformat then N'SCALL'
                	else null
                end

    if (1 != charindex( @commandprefix, upper(@ins_cmd collate SQL_Latin1_General_CP1_CS_AS) ))
    	or @ins_cmd is null
    begin
        raiserror (14156, 16, 1, N'insert', @commandprefix)
        return 1
    end
    
    -- Check to see if identity insert must be turned on
    -- i.e. Does the table has identity that are included in the partition?
    
    if (@publishertype = @pubtypemssqlserver)
    begin
        exec sys.sp_MSis_identity_insert @publication = null
                ,@article = null
                ,@identity_insert = @identity_insert output
                ,@artid = @artid
                ,@mode = 2
    end
    else
        select @identity_insert = 0
    
    -- get publication information
    
    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
    
    -- get destination proc name, the format stored inside sysarticles: XXXX [dbo].[xxxxxxxx]
    
    select @dest_proc = case @format
			                when @callformat then substring( @ins_cmd, 6, len( @ins_cmd ) - 4 )
			                when @scallformat then substring( @ins_cmd, 7, len( @ins_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' '
    
    -- prepare cursor based on publisher type
    
    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
        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

        
        -- is the column marked for replication
        --  is the column valid
        
        if @isvalidcolumn = 0
        begin
            if @mode = @reconciliationmode
                exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            else
                set @isset = 0
        end
        else
        begin
            exec @isset = sys.sp_isarticlecolbitset @this_col, @artid, @publishertype
        end

        if @isset = 1
        begin
            
            -- processing a column marked for replication and it is not computed
            
            select @typestring = sys.fn_gettypestring (@src_objid, @this_col, @publishertype, @userdefinedtypestobasetypes, @xmltontext, @maxtypestomatchingnonmaxtypes, @maptimestamp, @newdatetimetypestostrings, @hierarchyidtovarbinarymax, @largeuserdefin
edtypestovarbinarymax, @spatialtypestovarbinarymax)
            select @cmd = @cmd + @spacer + N'@c' + convert( nvarchar(10), @art_col ) + N' ' + @typestring

			if @format = @scallformat
            begin
				select @cmd = @cmd + ' = null'
            end

            select @art_col = @art_col + 1
                    ,@spacer = N','
            
            -- flush command as necessary
            
            if len( @cmd ) > 3000
            begin
                insert into #proctext(procedure_text) values( @cmd )
                select @cmd = N' '
            end
        end -- column marked for replication and not computed
        fetch hccolid into @this_col
    end -- while cursor block
    close hccolid
    deallocate hccolid
    insert into #proctext(procedure_text) values( @cmd )
    insert into #proctext(procedure_text) values( N'as ')
    insert into #proctext(procedure_text) values( N'begin ')
    
    -- Regular Mode
    
    -- The scripting is simple for general tran article (insert statement).
    -- For updating subscriber article - there is a possibility that the row
    -- already exists - we will add a row_does_not_exist check :
    -- if not exists (select * from [table] where [column1] = @c1)
    -- begin
    --      insert statement
    -- end
    
    
    -- Snapshot Reconciliation Mode
    
    -- The scripting is done as follows
    -- if exists (select * from [table] where [column1] = @c1)
    -- begin
    --      update statement
    -- end
    -- else
    -- begin
    --      insert statement
    -- end
    
    -- we will script an update if the row already exists.
    -- If we are replicating only PK columns, then the update is a noop.
    
    
    -- continue processing based on the mode
    
    if (@mode = @regularmode)
    begin
        
        -- regular mode
        
        if (@updsub_check = 1)
        begin
            
            -- updating subscriber article
            -- script out the if not row exists clause
            
            insert into #proctext(procedure_text) values (N'if not exists (select * from ' + @dest_owner + quotename(@dest_tabname) + N' ')
            exec @rc = sp_replscriptuniquekeywhereclause @tabid = @src_objid
                                                        ,@artid = @artid
                                                        ,@prefix = '@c'
                                                        ,@mode = 1
                                                        ,@skipindexesonudtcolumns = @userdefinedtypestobasetypes

            if (@@error != 0 or @rc != 0)
                return 1
            insert into #proctext(procedure_text) values( N')' )
            insert into #proctext(procedure_text) values (N'begin')
        end -- end (@updsub_check = 1)
    end -- end regular mode
    else
    begin
        
        -- reconciliation mode
        -- scripting for if the row already exists, apply as update
        
        insert into #proctext(procedure_text)
            values( N'if exists ( select * from ' + @dest_owner + quotename(@dest_tabname) )
        exec sys.sp_scriptreconwhereclause @src_objid, @artid, @publishertype
        insert into #proctext(procedure_text) values( N')' )
        insert into #proctext(procedure_text) values (N'begin')
        
        -- Check if we are replicating PK columns only
        
        exec @isallreplcolpk = sys.sp_MSisallreplcolpk @artid, @publishertype

        if @isallreplcolpk = 0
        begin
            
            -- replicating non PK columns as well
            -- construct update
            
            select @art_col = 1
                    ,@spacer = N' '
            insert into #proctext(procedure_text) values( N'update ' + @dest_owner + quotename(@dest_tabname) + N' set ' )
            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
                
                -- is the column marked for replication
                --  is the column valid
                
                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
                    
                    -- processing a column marked for replication and it is not computed
                    
                    exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype

                    if @isset = 0
                    begin
                        
                        -- not a PK column - check if we have a identity column that is being scripted as identity
                        -- we have to skip these identity columns
                        
                        if not (@fscriptidentity = 1 and @publishertype = @pubtypemssqlserver and
                            columnproperty(@src_objid, @colname, 'IsIdentity') = 1)
                        begin
                            
                            -- not a PK column and not a column scripted as identity
                            
                            insert into #proctext(procedure_text) values(@spacer + quotename(@colname) + N' = @c' + convert( nvarchar(10), @art_col ))
                            select @spacer = N','
                        end
                    end -- end not a PK column
                    select @art_col = @art_col + 1
                end -- column marked for replication and not computed
                else if @isset = 1 and @isvalidcolumn = 0
                begin
                    exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
                    if @isset = 1
                    begin
                        select @art_col = @art_col + 1
                    end
                end
                fetch hccolid into @this_col, @colname
            end -- end while cursor block
            close hccolid
            deallocate hccolid
            exec sys.sp_scriptreconwhereclause @src_objid, @artid, @publishertype
        end -- replicating non PK columns as well
        else
        begin
            
            -- all article columns are included in the PK, & PK already exists, do nothing
            
            insert into #proctext(procedure_text ) values( N'return' )
        end -- replicating only PK columns
        
        -- continue 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 -- end reconciliation mode
    
    -- More preparation for insert statement
    -- Generate strings for col names and variables
    
    select @art_col = 0
            ,@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
        
        -- is the column marked for replication
        --  is the column valid
        
        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 it is not computed
            
            select @art_col = @art_col + 1
            insert into #collisttab(procedure_text, collist) values( @spacer + quotename(@colname), 1 )
            insert into #collisttab(procedure_text, collist) values( @spacer + N'@c' + cast(@art_col as nvarchar(10)), 0 )
            select @spacer = N','
        end --  column marked for replication and not computed
        else if @isset = 1 and @isvalidcolumn = 0 and @mode = @reconciliationmode
        begin
            -- Increment the column\parameter counter if this is a computed
            -- primary key column
            exec @isset = sys.sp_MSiscolpk @src_objid, @this_col, @publishertype
            if @isset = 1
                set @art_col = @art_col + 1
        end
        fetch hccolid into @this_col, @colname
    end -- end while cursor block
    close hccolid
    deallocate hccolid
    
    -- set identity_insert on
    
    if @identity_insert = 1
    begin
        insert into #proctext(procedure_text) values( N'set identity_insert ' + @dest_owner + quotename(@dest_tabname) + ' on' )
    end
    
    -- script the insert statement now
    
    insert into #proctext(procedure_text) values(N'insert into ' +  @dest_owner + quotename(@dest_tabname)  + N'( ' )
    insert into #proctext(procedure_text)
        select procedure_text from #collisttab where collist = 1 order by c1 asc
    insert into #proctext(procedure_text) values( N' )' )
    
    -- script the values() clause
    
    if (@art_col > 0)
    begin
        insert into #proctext(procedure_text) values( N'values ( ' )
        insert into #proctext(procedure_text)
            select procedure_text from #collisttab where collist = 0 order by c1 asc
        insert into #proctext(procedure_text) values( N' ) ')
    end
    drop table #collisttab
    
    -- set identity_insert off
    
    if @identity_insert = 1
    begin
        insert into #proctext(procedure_text) values( N'set identity_insert ' + @dest_owner + quotename(@dest_tabname) + ' off' )
    end
    
    -- continue scripting
    
    if (@mode = @regularmode)
    begin
        
        -- regular scripting
        
        if (@updsub_check = 1)
            insert into #proctext(procedure_text) values( 'end')
    end
    else
    begin
        
        -- reconciliation scripting
        
        insert into #proctext(procedure_text) values (N'end')
    end
    
    -- end 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:
    
    -- send fragments 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_scriptinsproc (Procedure)
sp_scriptreconwhereclause (Procedure)
sp_scriptsinsproc (Procedure)
sp_script_reconciliation_insproc (Procedure)
sp_script_reconciliation_sinsproc (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