Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MStran_autoproc

  No additional text.


Syntax

create procedure sys.sp_MStran_autoproc
(
	@artid int
	,@mode tinyint = 0 -- 0 = no column change, 1 = column has been add/drop/altered, 2 = forced refresh of procs for the article
	,@ddlcmd nvarchar(max) = NULL -- NOT NULL for mode in 0,1; NULL for mode = 2
)
as
begin
    declare @retcode int
                ,@pubid int
                ,@schema_option binary(8)
                ,@auto_gen int
                ,@ins_cmd nvarchar(510)
                ,@del_cmd nvarchar(510)
                ,@upd_cmd nvarchar(510)
                ,@partial_cmds int
                ,@sql_cmds_type int
                ,@pubname sysname
                ,@artname sysname
                ,@schema_script nvarchar(4000)
                ,@allow_dts bit
                ,@allow_sync_tran bit
                ,@allow_queued_tran bit
                ,@dbname sysname
                ,@autogen_option int
                ,@p2p_option int
                ,@for_p2p_ddl bit
                ,@enabled_for_p2p_cd bit = 0
                ,@p2p_cd_option int = 0x8
                ,@modenocolumnchange tinyint
                ,@modecolumnchange tinyint
                ,@modearticleprocrefresh tinyint
                ,@has_ts bit
                ,@has_ident bit

    
    -- initialize
    
    set nocount on
    select @retcode = 0
            ,@autogen_option = 0x2
            ,@auto_gen = 0 -- auto generate custom procs
            ,@partial_cmds = 0
            ,@sql_cmds_type = 11
            ,@allow_sync_tran = 0
            ,@allow_queued_tran = 0
            ,@dbname = db_name()
            ,@p2p_option = 0x1
            ,@for_p2p_ddl = 0
            ,@modenocolumnchange = 0
            ,@modecolumnchange = 1
            ,@modearticleprocrefresh = 2
            ,@has_ts = 0
            ,@has_ident = 0
    
    -- validate
    
    if (@mode in (@modenocolumnchange, @modecolumnchange, @modearticleprocrefresh))
    begin
        if ((@mode = @modearticleprocrefresh) and @ddlcmd is not null)
        begin
            raiserror(21344, 16, -1, N'@ddlcmd')
            return 1
        end
    end
    else
    begin
        raiserror(21344, 16, -1, N'@mode')
        return 1
    end

    select 	@pubid = a.pubid
        ,@pubname = p.name
        ,@artname = a.name
        ,@auto_gen = (convert(int, a.schema_option) & @autogen_option)
        ,@allow_dts = p.allow_dts
        ,@ins_cmd = a.ins_cmd
        ,@del_cmd = a.del_cmd
        ,@upd_cmd = a.upd_cmd
        ,@allow_sync_tran = p.allow_sync_tran
        ,@allow_queued_tran = p.allow_queued_tran
        ,@for_p2p_ddl = (p.options & @p2p_option)
        ,@enabled_for_p2p_cd = (p.options & @p2p_cd_option)
        ,@has_ts = case when (objectproperty(a.objid, 'TableHasTimestamp') = 1) then 1 else 0 end
        ,@has_ident = case when (objectproperty(a.objid, 'TableHasIdentity') = 1) then 1 else 0 end
    from sysextendedarticlesview a join syspublications p
        on a.pubid = p.pubid
    where artid = @artid

    if (@allow_dts = 1 and @mode in (@modenocolumnchange, @modecolumnchange))
        goto SCRIPTONLY

    if @ddlcmd is not NULL
    begin
        exec @retcode = sys.sp_replpostcmd @partial_cmds, @pubid, @artid, @sql_cmds_type, @ddlcmd
        if(@retcode <> 0) or (@@error <> 0)
            return 1
    end

    
    -- if mode is no column change - we are done
    
    if @mode = @modenocolumnchange
        return 0
    
    -- we are here since either a column changed or we are doing a forced refresh
    -- auto-gen custom proc or execute custom script if mode is column change
    -- or proc refresh
    
    if (@auto_gen > 0)-- auto-gen custom scripts
    begin
        if(UPPER(LEFT(LTRIM(@ins_cmd), 4) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('CALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@ins_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('SCALL' collate SQL_Latin1_General_CP1_CS_AS))
        begin
            exec @retcode = sys.sp_MSpost_auto_proc @pubid = @pubid, @artid = @artid, @procmapid = 1, @for_p2p_ddl = @for_p2p_ddl
            if(@retcode <> 0) or (@@error <> 0)
                return 1
        end
        if(UPPER(LEFT(LTRIM(@del_cmd), 4) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('CALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@del_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('XCALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@del_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('VCALL' collate SQL_Latin1_General_CP1_CS_AS))
        begin
            exec @retcode = sys.sp_MSpost_auto_proc @pubid = @pubid, @artid = @artid, @procmapid = 2, @for_p2p_ddl = @for_p2p_ddl
            if(@retcode <> 0) or (@@error <> 0)
                return 1
        end
        if(UPPER(LEFT(LTRIM(@upd_cmd), 4) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('CALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@upd_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('MCALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@upd_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('XCALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@upd_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('VCALL' collate SQL_Latin1_General_CP1_CS_AS))
        	or (UPPER(LEFT(LTRIM(@upd_cmd), 5) collate SQL_Latin1_General_CP1_CS_AS) = UPPER('SCALL' collate SQL_Latin1_General_CP1_CS_AS))
        begin
            exec @retcode = sys.sp_MSpost_auto_proc @pubid = @pubid, @artid = @artid, @procmapid = 3, @for_p2p_ddl = @for_p2p_ddl
            if(@retcode <> 0) or (@@error <> 0)
                return 1
        end
    end
    if @allow_sync_tran = 1 or @allow_queued_tran = 1
    begin
        
        -- post script to alter conflict table on subscriber for queued
        
        if @allow_queued_tran = 1
        begin
            exec @retcode = sys.sp_MSpost_auto_proc @pubid=@pubid, @artid=@artid, @procmapid=5, @pubname=@pubname, @artname=@artname
            if(@retcode <> 0) or (@@error <> 0)
                return 1
        end
        
        -- post script to alter sync triggers on subscriber for queued/immediate update
        -- sp_MSget_synctran_commands also post alter table on sysarticlecolumns at subscriber side for queued.
        
        declare @alter bit
        --set @alter = 1 only for ddl cmds, since that's the only time #tran_columnstable (tmp table to hold changed column info) exists.
        set @alter = case @mode when 1 then 1 else 0 end
        exec @retcode = sys.sp_MSpost_auto_proc @pubid=@pubid, @artid=@artid, @procmapid=4, @pubname=@pubname, @artname=@artname,
                    @dbname=@dbname, @has_ts=@has_ts, @has_ident=@has_ident, @alter=@alter        						
        if(@retcode <> 0) or (@@error <> 0)
            return 1
    end

SCRIPTONLY:
    if (@mode in (@modenocolumnchange, @modecolumnchange) and (@auto_gen = 0 or @allow_dts = 1))
    begin
        select @schema_script = custom_script from sysarticles where artid = @artid
        if @schema_script is not NULL
        begin
            exec @retcode = sys.sp_addscriptexec @publication = @pubname, @scriptfile = @schema_script
            if @retcode<>0 or @@ERROR<>0
                return 1

            --now unregister the script so to force user to keep it up to date
            --i.e. register again before next DDL
            exec @retcode = sp_register_custom_scripting 'CUSTOM_SCRIPT', NULL, @pubname, @artname
            if @retcode<>0 or @@ERROR<>0
                return 1
        end
        else if (upper(@ins_cmd) not in (N'NONE', N'SQL') or upper(@del_cmd) not in (N'NONE', N'SQL')  or upper(@upd_cmd) not in (N'NONE', N'SQL') )
        begin
            --autogen_proc is off, but at least one of ins/del/upd_cmd is using proc, need to have a custom_script to refresh them
            raiserror(21814, 16, 1, @pubname, @artname)		
            return 1
        end
    end
    
    -- all done
    
    return 0
end

 
Last revision 2008RTM
See also

  sp_MSbuild_single_post (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MStran_alterschemaonly (Procedure)
sp_MStran_altertable (Procedure)
sp_MStran_altertrigger (Procedure)
sp_vupgrade_updatingpublicationarticle (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