Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_articlesynctranprocs

  No additional text.


Syntax
create procedure sys.sp_articlesynctranprocs
(
	@publication sysname,         -- publication name
	@article sysname,              -- article name
	@fautogen_id bit = 1, -- indicates wether or not to auto generate sprocs
	@alter bit = 0  -- if 1, alter instead of create
)
AS
BEGIN
    SET NOCOUNT ON

    -- Declarations.
    DECLARE @pubid int
                    ,@artid int
                    ,@retcode int
                    ,@ins_proc_id int
                    ,@upd_proc_id int
                    ,@del_proc_id int
                    ,@upd_trig_id int
                    ,@ins_proc sysname       -- name of sproc supporting Sync Tran inserts associated with this article
                    ,@upd_proc sysname       -- name of sproc supporting Sync Tran updates associated with this article
                    ,@del_proc sysname       -- name of sproc supporting Sync Tran deletes associated with this article
                    ,@upd_trig sysname
                    ,@allow_queued_tran bit
                    ,@conflict_table_id int
                    ,@ins_conflict_proc int
                    ,@objname sysname
                    ,@owner sysname
	

    
    -- Security Check -- DBO for creation or alter.
    
    exec @retcode = sys.sp_MSreplcheck_publish
    if @@ERROR <> 0 or @retcode <> 0
        return(1)

    -- Parameter Check: @article. The @article name cannot be NULL and must conform
    -- to the rules for identifiers.
    IF @article IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@article', 'sp_articlesynctranprocs')
        RETURN (1)
    END

    EXECUTE @retcode = sys.sp_validname @article
    IF @retcode <> 0
        RETURN(1)

    -- Parameter Check: @publication.
    -- The @publication name cannot be NULL and must conform to the rules
    -- for identifiers.
    IF @publication IS NULL
    BEGIN
        RAISERROR (14043, 16, -1, '@publication', 'sp_articlesynctranprocs')
        RETURN (1)
    END

    EXECUTE @retcode = sys.sp_validname @publication
    IF @retcode <> 0
        RETURN (1)

    -- Retrieve pubid & artid
    SELECT 	@pubid = a.pubid
			,@artid = a.artid
			,@allow_queued_tran = p.allow_queued_tran
    FROM sysarticles a join syspublications p
        on a.pubid = p.pubid
        and p.name = @publication
        and a.name = @article

    IF @pubid IS NULL OR @artid IS NULL
    BEGIN
        if @pubid IS NULL RAISERROR (20026, 16, 1, @publication)
        if @artid IS NULL RAISERROR (20027, 16, 1, @article)
        RETURN (1)
    END

    BEGIN TRAN
    SAVE TRAN sp_articlesynctranprocs

    -- if autogen is set, get proc names, then either create or alter them depending on @alter
    if @fautogen_id = 1
    begin
        -- Process according to the alter flag
        if @alter = 0
        begin
            -- in case of create, build unique name now
            select @ins_proc   = 'sp_MSsync_ins_'+ SUBSTRING(RTRIM(@article), 1, 100) + '_' + rtrim(convert(varchar, @pubid))
                    ,@upd_proc   = 'sp_MSsync_upd_'+ SUBSTRING(RTRIM(@article), 1, 100) + '_' + rtrim(convert(varchar, @pubid))
                    ,@del_proc   = 'sp_MSsync_del_'+ SUBSTRING(RTRIM(@article), 1, 100) + '_' + rtrim(convert(varchar, @pubid))
                    ,@upd_trig   = 'sp_MSsync_upd_trig_'+ SUBSTRING(RTRIM(@article), 1, 100) + '_' + rtrim(convert(varchar, @pubid))

            -- check uniqueness of names and revert to ugly guid-based name if friendly name already exists
            if exists (select name from sys.objects where name in (@ins_proc, @upd_proc, @del_proc))
            	or object_id(@upd_trig, 'TR') is not NULL
            begin
                declare @guid_name nvarchar(36)
                select @guid_name =  convert (nvarchar(36), newid())
                -- remove '-' from guid name because rpc can't handle '-'
                select @guid_name = replace (@guid_name,'-','_')
                select @ins_proc = 'sp_MSsync_ins_' + @guid_name
                        ,@upd_proc = 'sp_MSsync_upd_' + @guid_name
                        ,@del_proc = 'sp_MSsync_del_' + @guid_name
                        ,@upd_trig = 'sp_MSsync_upd_trig_' + @guid_name
            end
        end
        else
        begin
            -- in case of alter, get name from sysarticleupdates
            select @ins_proc = object_name(sync_ins_proc),
                    @upd_proc = object_name(sync_upd_proc),
                    @del_proc = object_name(sync_del_proc),
                    @upd_trig = object_name(sync_upd_trig)
            from sysarticleupdates
            where pubid = @pubid
                and artid = @artid
        end

        -- Parameter Check: @ins_proc, @upd_proc, @del_proc, @upd_trig
        -- The sproc names cannot be NULL and must conform to the rules
        -- for identifiers
        IF @ins_proc IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@ins_proc', 'sp_articlesynctranprocs')
            goto UNDO
        END
        EXECUTE @retcode = sys.sp_validname @ins_proc
        IF @retcode <> 0
            goto UNDO
        IF @upd_proc IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@upd_proc', 'sp_articlesynctranprocs')
            goto UNDO
        END
        EXECUTE @retcode = sys.sp_validname @upd_proc
        IF @retcode <> 0
            goto UNDO
        IF @del_proc IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@del_proc', 'sp_articlesynctranprocs')
            goto UNDO
        END
        EXECUTE @retcode = sys.sp_validname @del_proc
        IF @retcode <> 0
            goto UNDO
        IF @upd_trig IS NULL
        BEGIN
            RAISERROR (14043, 16, -1, '@upd_trig', 'sp_articlesynctranprocs')
            goto UNDO
        END
        EXECUTE @retcode = sys.sp_validname @upd_trig
        IF @retcode <> 0
            goto UNDO
	
        -- in case of create, check pre-existance and drop as needed
        if @alter = 0
        begin
            
            -- The synctran objects are always created in the context
            -- of the schema that owns the soures table, so use this
            -- owner when dropping existing objects
            
            select @owner = schema_name(OBJECTPROPERTY(objid, N'SchemaId'))
            from sysarticles
            where pubid = @pubid
                and artid = @artid
            select  @ins_proc_id = sync_ins_proc,
                        @upd_proc_id = sync_upd_proc,
                        @del_proc_id = sync_del_proc,
                        @upd_trig_id = sync_upd_trig,
                        @conflict_table_id = conflict_tableid,
                        @ins_conflict_proc = ins_conflict_proc
            from sysarticleupdates
            where pubid = @pubid
                and artid = @artid

            if @ins_proc_id is not null
            begin
                select @objname = object_name(@ins_proc_id)
                exec @retcode = sys.sp_MSdrop_object
                                @object_name = @objname,
                                @object_owner = @owner
                if @@error <> 0 or @retcode <> 0
                begin
                    ROLLBACK tran sp_articlesynctranprocs
                    commit tran
                    RETURN (1)
                end
            end
	
            if @upd_proc_id is not null
            begin
                select @objname = object_name(@upd_proc_id)
                exec @retcode = sys.sp_MSdrop_object
                            @object_name = @objname,
                            @object_owner = @owner
                if @@error <> 0 or @retcode <> 0
                begin
                    ROLLBACK tran sp_articlesynctranprocs
                    commit tran
RETURN (1)
                end
            end
	
            if @del_proc_id is not null
            begin
                select @objname = object_name(@del_proc_id)
                exec @retcode = sys.sp_MSdrop_object
                            @object_name = @objname,
                            @object_owner = @owner
                if @@error <> 0 or @retcode <> 0
                begin
                    ROLLBACK tran sp_articlesynctranprocs
                    commit tran
                    RETURN (1)
                end
            end
	
            if @upd_trig_id is not null
            begin
                select @objname = object_name(@upd_trig_id)
                exec @retcode = sys.sp_MSdrop_object
                                @object_name = @objname,
                                @object_owner = @owner
                if @@error <> 0 or @retcode <> 0
                begin
                    ROLLBACK tran sp_articlesynctranprocs
                    commit tran
                    RETURN (1)
                end
            end
	
            if (@conflict_table_id is not null)
            begin
                select @objname = object_name(@conflict_table_id)
                exec @retcode = sys.sp_MSdrop_object
                                @object_name = @objname,
                                @object_owner = @owner
                if (@@error != 0 or @retcode != 0)
                begin
                    ROLLBACK tran sp_articlesynctranprocs
                    commit tran
                    RETURN (1)
                end
            end
	
            if (@ins_conflict_proc is not null)
            begin
                select @objname = object_name(@ins_conflict_proc)
                exec @retcode = sys.sp_MSdrop_object
                                @object_name = @objname,
                                @object_owner = @owner
                if (@@error != 0 or @retcode != 0)
                begin
                    ROLLBACK tran sp_articlesynctranprocs
                    commit tran
                    RETURN (1)
                end
            end
        end  -- if @alter = 0

        EXECUTE @retcode =  sys.sp_MSgen_sync_tran_procs @publication, @article,
                    @ins_proc, @upd_proc, @del_proc, @upd_trig, @alter
        IF @retcode <> 0
        BEGIN
            ROLLBACK tran sp_articlesynctranprocs
            commit tran
            RETURN (1)
        END

        -- get proc ids for sysartucleupdates only for create, not alter
        if @alter = 0
        begin
            --retrieve sproc id's, fail if they don't exist
            if (@owner is null)
            begin
                select @ins_proc_id = object_id (@ins_proc)
                            ,@upd_proc_id = object_id (@upd_proc)
                            ,@del_proc_id = object_id (@del_proc)
                            ,@upd_trig_id = object_id (@upd_trig)
            end
            else
            begin
                select @ins_proc_id = object_id (quotename(@owner)collate database_default + N'.' + quotename(@ins_proc)collate database_default)
                            ,@upd_proc_id = object_id (quotename(@owner)collate database_default + N'.' + quotename(@upd_proc)collate database_default)
                            ,@del_proc_id = object_id (quotename(@owner)collate database_default + N'.' + quotename(@del_proc)collate database_default)
                            ,@upd_trig_id = object_id (quotename(@owner)collate database_default + N'.' + quotename(@upd_trig)collate database_default)
            end

            IF (@ins_proc_id IS NULL) OR (@upd_proc_id IS NULL) OR (@del_proc_id IS NULL) OR (@upd_trig_id IS NULL)
            BEGIN
                if @ins_proc_id IS NULL RAISERROR (20500, 16, 1, @ins_proc)
                if @upd_proc_id IS NULL RAISERROR (20500, 16, 1, @upd_proc)
                if @del_proc_id IS NULL RAISERROR (20500, 16, 1, @del_proc)
                if @upd_trig_id IS NULL RAISERROR (20500, 16, 1, @upd_trig)
                ROLLBACK tran sp_articlesynctranprocs
                commit tran
                RETURN (1)
            END
        end -- if @alter = 0
        
        -- perform insert into sysarticleupdates, or update if row exists
        -- need to mark this as a system table, so this sproc can live in master db
        if not exists (select * from sysarticleupdates where pubid = @pubid and artid = @artid)
        begin
            INSERT sysarticleupdates(pubid, artid, sync_ins_proc, sync_upd_proc, sync_del_proc, autogen, sync_upd_trig)
            VALUES (@pubid, @artid, @ins_proc_id, @upd_proc_id, @del_proc_id, @fautogen_id, @upd_trig_id)
        end
        else
        begin
            update sysarticleupdates
            set sync_ins_proc = @ins_proc_id,
                    sync_upd_proc = @upd_proc_id,
                    sync_del_proc = @del_proc_id,
                    sync_upd_trig = @upd_trig_id
            where pubid = @pubid and artid = @artid
        end
        IF @@ERROR <> 0
            goto UNDO
    end -- if @fautogen_id = 1
    
    -- Generate the conflict table and conflict proc for Queued Tran case
    
    if (@allow_queued_tran = 1)
    begin
        exec @retcode = sys.sp_MSmakeconflicttable @article
										, @publication
										, 0    -- @creation_mode : 0 = for publisher, 1 = for subscriber (snapshot)
										, 0    -- @is_debug bit = 0
										, @alter
                                        , 1 -- @usesqlclr
        IF @@ERROR <> 0 OR @retcode <> 0
            goto UNDO
        exec @retcode = sys.sp_MSmaketrancftproc @article, @publication
										, 0  -- @is_debug bit = 0
										, @alter
        IF @@ERROR <> 0 OR @retcode <> 0
            goto UNDO
    end
    COMMIT TRAN
    RETURN (0)
UNDO:
    ROLLBACK tran sp_articlesynctranprocs
    commit tran
    RETURN (1)
END

 
Last revision 2008RTM
See also

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