Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSmaketrancftproc

  No additional text.


Syntax
create procedure sys.sp_MSmaketrancftproc
(
    @article sysname,
    @publication sysname,
    @is_debug bit=0,
    @alter bit = 0 -- if 1 script alter, otherwise script create
)
as
BEGIN
    declare @source_table nvarchar(1000)
				,@owner nvarchar(1000)
				,@procname nvarchar(1000)
				,@qualprocname nvarchar(1000)
				,@source_objid int
				,@artid int
				,@pubid int
				,@conflict_tableid int
				,@conflict_table nvarchar(1000)
				,@conflict_proc_id int
				,@indid int
				,@indkey int
				,@ind_col_name nvarchar(1000)
				,@qualname nvarchar(1000)
				,@destqualname nvarchar(1000)
				,@destowner nvarchar(1000)
				,@dbname nvarchar(1000)
				,@retcode smallint
				,@retain_varname int

    declare @colid      int
                ,@colname nvarchar(1000)
                ,@coltype nvarchar(1000)
                ,@ccoltype nvarchar(1000)
                ,@termlen    int
                ,@splitlen int

    declare @argtabempty    bit
                ,@seltabempty   bit
                ,@sel2tabempty  bit
                ,@valtabempty   bit
                ,@paramtabempty bit
                ,@where_clausetabempty bit
                ,@decltabempty bit
                ,@assigntabempty bit
                ,@compinsertabempty bit

    declare @argterm    nvarchar(4000)
                ,@selterm   nvarchar(4000)
                ,@sel2term  nvarchar(4000)
                ,@updterm   nvarchar(4000)
                ,@valterm   nvarchar(4000)
                ,@paramterm nvarchar(4000)
                ,@where_term nvarchar(4000)
                ,@declterm  nvarchar(4000)
                ,@assignterm nvarchar(4000)
                ,@compinsterm nvarchar(4000)

    declare @cmd        nvarchar(4000)

    set nocount on
    
    -- prepare the proc name and get the other parameters
    
    select @artid = a.artid, @pubid = a.pubid, @source_table = object_name(a.objid),
            @source_objid = a.objid, @destowner = a.dest_owner
    from sysarticles a, syspublications p
    where a.name = @article and
        p.name = @publication and
        a.pubid = p.pubid
    
    -- Get the schema owner name for the object
    
    select @owner = schema_name(schema_id)
    from sys.objects
    where object_id= @source_objid
    
    -- Prepare the proc name
    -- The source table should be owner qualified
    
    select @source_table = QUOTENAME(@owner) + N'.' + QUOTENAME(@source_table)
    if @alter = 0
    begin
        exec @retcode = sys.sp_MSgettranconflictname @publication=@publication,
                                    @source_object= @source_table,
                                    @str_prefix='sp_MScft_',
                                    @conflict_table=@procname OUTPUT
    end
    --in case of alter, proc should already exists
    else
    begin
        select @procname = object_name(ins_conflict_proc) from sysarticleupdates
        where artid = @artid and pubid = @pubid
    end

	set @qualprocname = QUOTENAME(@owner)+ N'.'+ QUOTENAME(@procname)
    
    -- The conflict table should exist before we do any conflict procs
    
    select @conflict_tableid = conflict_tableid,
            @conflict_table = OBJECT_NAME(conflict_tableid)
    from sysarticleupdates
    where artid = @artid and pubid = @pubid
    if ( @conflict_tableid is NULL)
        return (1)
    
    -- To check if specified object exists in current database
    
    select @qualname = case when (@owner is null or @owner = ' ') then QUOTENAME(@conflict_table)
                            else QUOTENAME(@owner) + N'.' + QUOTENAME(@conflict_table) end
    if (object_id(@qualname) is NULL)
        return (1)
    
    -- The source table should have an unique index
    
    exec @indid = sys.sp_MStable_has_unique_index @source_objid
    if (@indid = 0)
        return (1)
    
    -- Get all the columns participating in the index of the source table
    
    create table #indcoltab ( colname sysname collate database_default )
    select @indkey = 1;
    while (@indkey <= 16)
    begin
        select @ind_col_name = index_col(@source_table, @indid, @indkey)
        if (@ind_col_name is not NULL)
            insert into #indcoltab(colname) values(@ind_col_name)
        else
            select @indkey = 16

        select @indkey = @indkey + 1
    end
    
    -- prepare destination table name (required for decentralized conflict processing)
    
    select @destqualname = case when (@destowner is null or @destowner = ' ')
                        then QUOTENAME(@conflict_table)
                        else QUOTENAME(@destowner) + N'.' + QUOTENAME(@conflict_table) end
    
    -- build the lists
    
    select @argtabempty = 1
        ,@valtabempty = 1
        ,@paramtabempty = 1
        ,@seltabempty = 1
        ,@sel2tabempty = 1
        ,@decltabempty = 1
        ,@assigntabempty = 1
        ,@where_clausetabempty = 1
        ,@compinsertabempty = 1

    create table #argtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #valtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #paramtab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #seltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #sel2tab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #decltab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #assigntab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #where_clausetab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null)
    create table #compinstab ( c1 int identity NOT NULL, procedure_text nvarchar(4000) collate database_default null, termlen int null)

    
    -- some predefined declares and assignments
    
    select @cmd = N'
    declare @reinit_code int, @subwins_code int, @pubwins_code int, @qcfttabrowid uniqueidentifier
            ,@retcode smallint, @compcmd nvarchar(max), @centralized_conflicts bit'
    insert into #decltab(procedure_text) values(@cmd)
    select @decltabempty = 0

    select @cmd = N'
    select @reinit_code = 3
            ,@subwins_code = 2
            ,@pubwins_code = 1
            ,@qcfttabrowid = NEWID()'
    insert into #assigntab(procedure_text) values(@cmd)
    select @cmd = N'
    select @centralized_conflicts = centralized_conflicts
    from dbo.syspublications where pubid = ' + cast(@pubid as nvarchar)
    insert into #assigntab(procedure_text) values(@cmd)
    select @assigntabempty = 0
    
    -- cursor for enumerating each column
    -- to build the terms for various lists
    
    declare #argcursor cursor local FAST_FORWARD FOR
            select column_id, max_length
            from sys.columns
            where is_computed = 0 and object_id=@conflict_tableid
            order by column_id
    FOR READ ONLY

    select @retain_varname = 0
    open #argcursor
    fetch #argcursor into @colid, @termlen
    while (@@FETCH_STATUS = 0)
    begin
        
        -- Get the column name and column type
        
        exec sys.sp_MSget_type @conflict_tableid, @colid, @colname output, @coltype OUTPUT
        if (@@ERROR<>0 or @retcode<>0)
            return (1)
        
        -- skip this specific column or if type is not returned
        
        if ((@coltype IS NULL) or (rtrim(@coltype) like N'timestamp') or (LOWER(@colname) = 'qcfttabrowid'))
        begin
            -- do the next fetch and continue
            fetch #argcursor into @colid, @termlen
            continue
        end
        exec sys.sp_MSget_colinfo @conflict_tableid, @colid, NULL, 0, NULL, @ccoltype output
        if (@@ERROR<>0 or @retcode<>0)
            return (1)
        
        -- parameterize the vars that are the column values of the source
        -- table. For the columns that are specific to the conflict table
        -- retain specific names for the vars
        
        if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'origin_datasource')
            select @retain_varname = @colid

        select @argterm = case when (@retain_varname = 0)
                then N'@param' + cast(@colid as nvarchar)
                else N'@' + @colname end

        select @valterm = quotename(@colname)
                ,@paramterm = @argterm
        select @updterm = @valterm + N' = ' + @argterm

        if (@retain_varname = 0)
        begin
            if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
            begin
                select @selterm = @paramterm + N' = convert(xml, ' + @valterm + ') '
                        ,@sel2term = @paramterm + N' = case when ' + @paramterm +
                            N' is NULL then convert(xml, ' + @valterm + N') else ' + @paramterm + N' end'
            end
            else
            begin
                select @selterm = @paramterm + N' = ' + @valterm
                        ,@sel2term = @paramterm + N' = case when ' + @paramterm +
                            N' is NULL then ' + @valterm + N' else ' + @paramterm + N' end'
            end
        end
        else
        begin
            select @selterm = NULL
                    ,@sel2term = NULL
        end

        select @argterm = @argterm + N' ' + @coltype
        
        -- Check if this is part of primary key / unique index
        
        if (@colname in ( select colname from #indcoltab ) )
        begin
            -- this key assignment becomes part of where clause
            select @where_term = @updterm
            select @updterm = NULL
                    ,@selterm = NULL
                    ,@sel2term = NULL
        end
        else
            select @where_term = NULL
        
        -- special columns - process them as local var
        
        if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'insertdate' )
        begin
            select @declterm = N'
    declare ' + @argterm
            select @assignterm = N'
    select ' + @paramterm + N' = GETDATE()'
            select @argterm = NULL
        end
        else if (LOWER(@colname collate SQL_Latin1_General_CP1_CS_AS) = 'pubid' )
        begin
            select @declterm = N'
    declare ' + @argterm
            select @assignterm = N'
    select ' + @paramterm + N' = ' + cast(@pubid as nvarchar)
            select @argterm = NULL
        end
        else
        begin
            select @declterm = NULL
                    ,@assignterm = NULL
        end
        
        -- build the term for compensating insert
        
        if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'varchar')
        begin
            select @compinsterm = N' '''''' + sys.fn_replreplacesinglequote(' + @valterm + N') collate database_default + '''''''' '
                    ,@termlen = case when (@termlen = -1) then 3500 else @termlen + 4 end
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nvarchar')
        begin
            select @compinsterm = N' N'''''' + sys.fn_replreplacesinglequote(' + @valterm + N') collate database_default + '''''''' '
                    ,@termlen = case when (@termlen = -1) then 3500 else (@termlen * 2) + 4 end
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'char')
        begin
            select @compinsterm = N' '''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @valterm + N') as nvarchar(4000))) collate database_default + '''''''' '
                    ,@termlen = @termlen  + 4
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'nchar')
        begin
            select @compinsterm = N' N'''''' + sys.fn_replreplacesinglequote(CAST(RTRIM(' + @valterm + N') as nvarchar(4000))) collate database_default + '''''''' '
                    ,@termlen = (@termlen * 2)  + 4
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('binary','varbinary'))
        begin
            select @compinsterm = N' '' + sys.fn_varbintohexstr(' + @valterm + N') collate database_default '
                    ,@termlen = case when (@termlen = -1) then 3500 else (@termlen * 2) + 6 end
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('bit','bigint','int','smallint','tinyint','decimal','numeric'))
        begin
            select @compinsterm = N' '' + CAST(' + @valterm + N' as nvarchar) '
                    ,@termlen = 40
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('float','real'))
        begin
            select @compinsterm = N' '' + CONVERT(nvarchar(60),' + @valterm + N', 2) '
                    ,@termlen = 60
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('money','smallmoney'))
        begin
            select @compinsterm = N' '' + CONVERT(nvarchar(40),' + @valterm + N', 2) '
                    ,@termlen = 40
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'uniqueidentifier')
        begin
            select @compinsterm = N' '''''' + CAST(' + @valterm + N' as nvarchar(40)) + '''''''' '
                    ,@termlen = 40
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('datetime','smalldatetime'))
        begin
            select @compinsterm = N' '''''' + CONVERT(nvarchar(40), ' + @valterm + N', 112) + N'' ''  + CONVERT(nvarchar(40), ' + @valterm + N', 114) + '''''''' '
                    ,@termlen = 40
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) in ('date','time','datetime2','datetimeoffset'))
        begin
            select @compinsterm = N' '''''' + CONVERT(nvarchar(40), ' + @valterm + N', 121) + '''''''' '
                    ,@termlen = 40
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'sql_variant')
        begin
            select @compinsterm = N' '' + sys.fn_sqlvarbasetostr(' + @valterm + N' ) collate database_default '
                    ,@termlen = case when (@termlen = -1) then 3500 else @termlen + 4 end
        end
        else if (lower(@ccoltype collate SQL_Latin1_General_CP1_CS_AS) = 'xml')
        begin
            select @compinsterm = N' N'''''' + CAST(' + @valterm + N' as nvarchar(max)) + '''''''' '
                    ,@termlen = case when (@termlen = -1) then 3500 else (@termlen * 2) + 4 end
        end
        else
        begin
            select @compinsterm = N' '' + CAST(' + @valterm + N' as nvarchar) '
                    ,@termlen = @termlen  + 4
        end
        
        -- Now append to the various lists
        
        if (@argterm is NOT NULL)
        begin
            if (@argtabempty = 1)
            begin
                select @argtabempty = 0
                        ,@cmd = N'
    ' + @argterm
            end
            else
                select @cmd = N',
    ' + @argterm
            insert into #argtab(procedure_text) values(@cmd)
        end
        if (@valterm is NOT NULL)
        begin
            if (@valtabempty = 1)
            begin
                select @valtabempty = 0
                        ,@cmd = @valterm
            end
            else
                select @cmd = N', ' + @valterm
            insert into #valtab(procedure_text) values(@cmd)
        end
        if (@paramterm is NOT NULL)
        begin
            if (@paramtabempty = 1)
            begin
                select @paramtabempty = 0
                        ,@cmd = @paramterm
            end
            else
                select @cmd = N', ' + @paramterm
            insert into #paramtab(procedure_text) values(@cmd)
        end
        if (@selterm is NOT NULL)
        begin
            if (@seltabempty = 1)
            begin
                select @seltabempty = 0
                        ,@cmd = N'
        ' + @selterm
            end
            else
                select @cmd = N'
        ,' + @selterm
            insert into #seltab(procedure_text) values(@cmd)
        end
        if (@sel2term is NOT NULL)
        begin
            if (@sel2tabempty = 1)
            begin
                select @sel2tabempty = 0
                        ,@cmd = N'
        ' + @sel2term
            end
            else
                select @cmd = N'
        ,' + @sel2term
            insert into #sel2tab(procedure_text) values(@cmd)
        end
        if (@where_term is NOT NULL)
        begin
            if (@where_clausetabempty = 1)
            begin
                select @where_clausetabempty = 0
                        ,@cmd = @where_term
            end
            else
                select @cmd = N' AND
            ' + @where_term
            insert into #where_clausetab(procedure_text) values(@cmd)
        end
        if (@declterm is NOT NULL)
        begin
            select @cmd = @declterm + N'
    '
            insert into #decltab(procedure_text) values(@cmd)
        end
        if (@assignterm is NOT NULL)
        begin
            select @cmd = @assignterm + N'
    '
            insert into #assigntab(procedure_text) values(@cmd)
        end
        if (@compinsterm is NOT NULL)
        begin
            if (@compinsertabempty = 1)
            begin
                select @compinsertabempty = 0
                        ,@cmd = N' + ISNULL(''' + @compinsterm + N', ''null'')'
            end
            else
                select @cmd = N' + ISNULL('',' + @compinsterm + N', '',null'')'
            insert into #compinstab(procedure_text, termlen) values(@cmd, (@termlen + len(@cmd)))
        end
        
        -- do the next fetch of columns cursor
        
        fetch #argcursor into @colid, @termlen
    end -- while loop of columns cursor
    close #argcursor
    deallocate #argcursor
    drop table #indcoltab
    
    -- generation phase
    
    BEGIN TRAN
    save tran sp_MSmaketrancftproc
    
    -- create temp table to select the command text out of
    
    if exists (select * from sys.objects where name = 'tempcmd' and schema_id = schema_id('dbo'))
            drop table dbo.tempcmd
    create table dbo.tempcmd ( c1 int identity NOT NULL, cmdtext nvarchar(4000) NULL)
    
    -- create header
    
    insert into  dbo.tempcmd(cmdtext)
		values(case @alter when 0 then N'create' else N'alter' end + ' procedure '+@qualprocname + N'(
		')
    
    -- insert the arglist
    
    insert into dbo.tempcmd(cmdtext) select procedure_text from #argtab order by c1
    insert into dbo.tempcmd(cmdtext) values(N' ,@subcriber sysname = NULL, @subdb sysname = NULL )
as
begin
')
    
    -- insert the declare list
    
    insert into dbo.tempcmd(cmdtext) select procedure_text from #decltab order by c1
    insert into dbo.tempcmd(cmdtext) values(N'
')
    
    -- script out security check
    
    insert into dbo.tempcmd(cmdtext) values(N'
    ' + N'
    ' + N'-- Check for security
    ' + N'
    exec @retcode = sys.sp_MSreplcheck_pull @publication = N'''+ sys.fn_replreplacesinglequote(@publication) collate database_default + N'''
    if (@retcode != 0 or @@error != 0)
        return -1
    ')
    
    -- insert the assignment list (for declared vars)
    
    insert into dbo.tempcmd(cmdtext) select procedure_text from #assigntab order by c1
    
    -- do the select for the case where we need to retain values of publisher
    
    insert into dbo.tempcmd(cmdtext) values(N'
    if (@reason_code = @subwins_code)
    begin
        select ')
    insert into dbo.tempcmd(cmdtext) select procedure_text from #seltab order by c1
    insert into dbo.tempcmd(cmdtext) values(N'
        from ' + @source_table + N' where ')
    insert into dbo.tempcmd(cmdtext) select procedure_text from #where_clausetab order by c1
    insert into dbo.tempcmd(cmdtext) values(N'
    end')

    insert into dbo.tempcmd(cmdtext) values(N'
    else
    begin
        select ')

    insert into dbo.tempcmd(cmdtext) select procedure_text from #sel2tab order by c1
    insert into dbo.tempcmd(cmdtext) values(N'
        from ' + @source_table + N' where ')
    insert into dbo.tempcmd(cmdtext) select procedure_text from #where_clausetab order by c1
    insert into dbo.tempcmd(cmdtext) values(N'
    end
')
    
    -- insert the conflict row in the publisher cft table
    
    insert into dbo.tempcmd(cmdtext) values(N'
    insert into ' + @qualname + N'(')
    insert into dbo.tempcmd(cmdtext) select procedure_text from #valtab order by c1
    insert into dbo.tempcmd(cmdtext) values(N',[qcfttabrowid])
    values (')
    insert into dbo.tempcmd(cmdtext) select procedure_text from #paramtab order by c1
    insert into dbo.tempcmd(cmdtext) values(N',@qcfttabrowid)
')
    
    -- generate compensating command decentralized logging
    -- depending on the number of columns, we will split the compensating
    -- command into several compensating commands
    
    select @compinsertabempty = 1
            ,@splitlen = 0
    select @cmd = N'
        if (@centralized_conflicts = 0)
        begin
            select @compcmd = N''insert into ' + sys.fn_replreplacesinglequote(@destqualname) collate database_default + N' ( '
    insert into dbo.tempcmd(cmdtext) values(@cmd)
    select @splitlen = @splitlen + len(@cmd)
    
    -- build the first part of compensating command for each column using the values list
    
    declare #htempcur cursor local for
        select sys.fn_replreplacesinglequote(procedure_text) from #valtab order by c1
    for read only

    open #htempcur
    fetch #htempcur into @compinsterm
    while (@@fetch_status = 0)
    begin
        insert into dbo.tempcmd(cmdtext) select @compinsterm
        select @splitlen = @splitlen + len(@cmd)
        
        -- if we have processed enough terms then split the command
        
        if (@splitlen > 3500)
        begin
            
            -- script where clause
            
            select @cmd = N'''
        from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid'
            insert into dbo.tempcmd(cmdtext) values(@cmd)
            
            -- script the compensating command to log
            
            select @cmd = N'
        exec @retcode = sys.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, '
            + CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',1,0,'
            + CAST(@compinsertabempty as nvarchar(4)) + N'
        if (@@error != 0 or @retcode != 0)
            return 1
        select @compcmd = N'''
            insert into dbo.tempcmd(cmdtext) values(@cmd)
            
            -- reinitialize the loop params
            
            select @compinsertabempty = 0
                    ,@splitlen = 0
        end
        
        -- fetch next value
        
        fetch #htempcur into @compinsterm
    end
    close #htempcur
    deallocate #htempcur
    
    -- script the values part of the insert
    
    insert into dbo.tempcmd(cmdtext) values(N', [qcfttabrowid] ) values ('' ')
    select @splitlen = @splitlen + len(@cmd)
            ,@termlen = 0
    
    -- build the last part of compensating command for each column using the compinstab list
    
    declare #htempcur cursor local for
        select procedure_text, termlen from #compinstab order by c1
    for read only

    open #htempcur
    fetch #htempcur into @compinsterm, @termlen
    while (@@fetch_status = 0)
    begin
        
        -- if we have processed enough terms then split the command
        
        if (@splitlen + @termlen < 3500)
        begin
            insert into dbo.tempcmd(cmdtext) select @compinsterm
            select @splitlen = @splitlen + @termlen
        end
        else
        begin
            
            -- script where clause
            
            select @cmd = N'
        from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid'
            insert into dbo.tempcmd(cmdtext) values(@cmd)
            
            -- script the compensating command to log
            
            select @cmd = N'
        exec @retcode = sys.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, '
            + CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',1,0,'
            + CAST(@compinsertabempty as nvarchar(4)) + N'
        if (@@error != 0 or @retcode != 0)
            return 1

        select @compcmd = N'' '''
            insert into dbo.tempcmd(cmdtext) values(@cmd)
            
            -- reinitialize the loop params
            
            insert into dbo.tempcmd(cmdtext) select @compinsterm
            select @splitlen = @termlen, @compinsertabempty = 0
        end
        
        -- fetch next compinsert term
        
        fetch #htempcur into @compinsterm, @termlen
    end
    close #htempcur
    deallocate #htempcur
    
    -- script the final compensating command
    
    select @cmd = N' + '', '''''' + CAST([qcfttabrowid] as nvarchar(40)) + '''''''' + N'' ) ''
        from ' + @qualname + N' where qcfttabrowid = @qcfttabrowid and tranid = @tranid'
    insert into dbo.tempcmd(cmdtext) values(@cmd)

    select @cmd = N'
        exec @retcode = sys.sp_MSadd_compensating_cmd @subcriber, @subdb, @compcmd, '
            + CAST(@artid as nvarchar(10)) + N', ' + CAST(@pubid as nvarchar(10)) + N',0,0,'
            + CAST(@compinsertabempty as nvarchar(4)) + N'
        if (@@error != 0 or @retcode != 0)
            return 1 '
    insert into dbo.tempcmd(cmdtext) values(@cmd)
    insert into dbo.tempcmd(cmdtext) values(N'
    end
end')
    
    -- process based on debug flag
    
    if (@is_debug = 0)
    begin
        
        -- Now we select out the command text pieces in proper order so that our caller,
        -- xp_execresultset will execute the command that creates the stored procedure.
        
        select @dbname = db_name()
                ,@cmd = N'select cmdtext from dbo.tempcmd order by c1'
        exec @retcode = sys.xp_execresultset @cmd, @dbname
        if (@@error != 0 or @retcode != 0)
        begin
            -- roll back the tran
            rollback tran sp_MSmaketrancftproc
            return (1)
        end
        
        -- Check if we create the proc and update sysarticleupdates
        
        select @conflict_proc_id = object_id from sys.objects where name = @procname and type = 'P '
        if (@conflict_proc_id is NULL or @conflict_proc_id = 0)
        begin
            -- roll back the tran
            rollback tran sp_MSmaketrancftproc
            return (1)
        end
        else
        begin
            update dbo.sysarticleupdates set ins_conflict_proc = @conflict_proc_id
                where artid = @artid and pubid = @pubid
            if @@error <> 0
            begin
            -- roll back the tran
                rollback tran sp_MSmaketrancftproc
                return (1)
            end
            
            -- mark the proc as system object
            
            if (@owner in ('dbo','INFORMATION_SCHEMA'))
            begin
                exec @retcode = dbo.sp_MS_marksystemobject @qualprocname
                if (@@error != 0 or @retcode != 0)
                begin
                    -- roll back the tran
                    rollback tran sp_MSmaketrancftproc
                    return (1)
                end
            end
        end
    end
    else
    begin
        
        -- debug mode - just return the script as resultset
        
        select cmdtext from dbo.tempcmd order by c1
    end
    
    -- commit tran
    
    COMMIT TRAN
    
    -- drop the temp tables
    
    drop table dbo.tempcmd
    drop table #argtab
    drop table #valtab
    drop table #paramtab
    drop table #seltab
    drop table #sel2tab
    drop table #decltab
    drop table #assigntab
    drop table #where_clausetab
    drop table #compinstab
    
    -- all done
    
    return (0)
END

 
Last revision 2008RTM
See also

  sp_articlesynctranprocs (Procedure)
sp_MSdrop_repltran (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