Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSscript_insert_statement

  No additional text.


Syntax
create procedure sys.sp_MSscript_insert_statement(
    @objid int,
    @artid int,
    @identity_insert bit = 0,
    @queued_pub bit = 0
    )
as
BEGIN
    declare @cmd          nvarchar(4000)
                ,@qualname     nvarchar(517)
                ,@tablename     nvarchar(517)
                ,@colname      sysname
                ,@ccoltype     sysname
                ,@this_col     int
                ,@rc           int
                ,@num_col      int
                ,@column_string nvarchar(4000)
                ,@var_string nvarchar(4000)
                ,@hfilterprocid int
                ,@sync_objid int
                ,@identityscriptedasbasedatatypeonsubscriber bit

    
    -- Check if we have horizontal partition filter
    
    select @hfilterprocid = filter, @sync_objid = sync_objid from dbo.sysarticles where artid = @artid
    if (@hfilterprocid = 0)
    begin
        
        -- horizontal partional filter not defined, the qualified name should be the source object
        
        exec sys.sp_MSget_qualified_name @objid, @qualname OUTPUT
        select @tablename = @qualname
    end
    else
    begin
        
        -- horizontal partional filter not defined, the qualified name should be the view object
        
        exec sys.sp_MSget_qualified_name @sync_objid, @qualname OUTPUT
        exec sys.sp_MSget_qualified_name @objid, @tablename OUTPUT
    end
    
    -- For identity column processing - see if base data is being scripted on subscriber
    -- For queued or failover this will never be true, we need to really
    -- check it only for pure immediate case
    
    select @identityscriptedasbasedatatypeonsubscriber = 0
    if (@queued_pub = 0) and (@identity_insert = 1)
    begin
        -- immediate updating only with an identity column
        select @identityscriptedasbasedatatypeonsubscriber = case when (cast(schema_option as int) & 0x4 = 0) then 1 else 0 end
        from sysarticles where artid = @artid
    end
    
    -- start scripting
    
    select @cmd = N'
    ' + N'
    ' + N'-- detection/conflict resolution stage
    ' + N'--'
    
    -- Queued specific
    
    if (@queued_pub = 1)
    begin
        select @cmd = @cmd + N'
    if (@execution_mode = @QPubWins)
        save tran cftpass'
    end
    insert into #proctext(procedure_text) values( @cmd)
    
    -- set identity_insert on if subscriber is also using it as identity
    
    if (@identity_insert = 1 and @identityscriptedasbasedatatypeonsubscriber = 0)
    begin
        select @cmd = N'
    set identity_insert ' + @tablename + ' on'
        insert into #proctext(procedure_text) values( @cmd )
    end
    
    -- prepare the insert statement now
    
    select @cmd = N'
    insert into ' + @qualname + N'( '
    insert into #proctext(procedure_text) values( @cmd )
    -- Generate strings for col names and variables
    select @num_col = 0
            ,@cmd = N''

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

    open #hCColid
    fetch #hCColid into @this_col
    while (@@fetch_status != -1)
    begin
        exec @rc = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
        if @rc = 0  and exists (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
        begin
            -- skip the column if it is timestamp or it is an identity that is being scripted as base data on subscriber
            if (@ccoltype != N'timestamp') and
                not(columnproperty(@objid, @colname, 'IsIdentity') = 1 and @identityscriptedasbasedatatypeonsubscriber=1)
            begin
                select @num_col = @num_col + 1
                if @num_col > 1
                    select @cmd = @cmd + N', '
 select @cmd = @cmd + quotename(@colname)
                exec sys.sp_MSflush_command @cmd output, 1
            end
        end
        fetch #hCColid into @this_col
    end
    close #hCColid
    
    -- Script end of colmn names
    
    select @cmd = N' )'
    insert into #proctext(procedure_text) values( @cmd )
    
    -- Script column value string
    
    if @num_col > 0
    begin
        select @cmd = N'
    values ( '
        insert into #proctext(procedure_text) values( @cmd )
        -- Script column value string
        select @num_col = 0
                ,@cmd = N''

        open #hCColid
        fetch #hCColid into @this_col
        while (@@fetch_status != -1)
        begin
            exec @rc = sys.sp_MSget_colinfo @objid, @this_col, @artid, 0, @colname output, @ccoltype output
            if @rc = 0  and exists (select name from sys.columns where object_id=@objid and column_id=@this_col and is_computed<>1)
            begin
                -- skip the column if it is timestamp or it is an identity that is being scripted as base data on subscriber
                if (@ccoltype != N'timestamp') and
                    not(columnproperty(@objid, @colname, 'IsIdentity') = 1 and @identityscriptedasbasedatatypeonsubscriber=1)
                begin
                    select @num_col = @num_col + 1
                    if @num_col > 1
                        select @cmd = @cmd + N', '
                    select @cmd = @cmd + N'@c' + cast(@this_col as nvarchar(4))
                    exec sys.sp_MSflush_command @cmd output, 1
                end
            end
            fetch #hCColid into @this_col
        end
        close #hCColid
        -- Script end of column value string
        select @cmd = N' )
    '
        insert into #proctext(procedure_text) values( @cmd )
    end
    else
    begin
        -- This is to set @@rowcount.
        insert into #proctext(procedure_text) values( N'
    select @retcode = @retcode')
    end
    
    -- set the rowcount and error
    
    select @cmd = N'
    select @rowcount = @@ROWCOUNT, @error = @@ERROR
    '
    insert into #proctext(procedure_text) values( @cmd )
    
    -- set indentity_insert off if subscriber is also using it as identity
    
    if (@identity_insert = 1 and @identityscriptedasbasedatatypeonsubscriber = 0)
    begin
        select @cmd = N'
    set identity_insert ' + @tablename + ' off'
        insert into #proctext(procedure_text) values( @cmd )
    end
    
    -- Queued specific
    
    if (@queued_pub = 1)
    begin
        select @cmd = N'
    if (@execution_mode = @QPubWins)
        rollback tran cftpass'
        insert into #proctext(procedure_text) values( @cmd )
    end
    
    -- all done
    
    deallocate #hCColid
    return 0
END

 
Last revision 2008RTM
See also

  sp_MSdrop_repltran (Procedure)
sp_MSscript_sync_ins_proc (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash