Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSsetartprocs

  No additional text.


Syntax
-- NOTE: call this proc only on the publisher. On the subscriber call sp_MSmakearticleprocs
create procedure sys.sp_MSsetartprocs
    (@publication       sysname,
    @article            sysname,
    @force_flag         int = 0,
    @pubid              uniqueidentifier = NULL) --publication name and article name can't guarantee uniqueness, in rare cases
                                                 --where republisher has the same pub/art names
as
    declare @ownername sysname
    declare @objectname sysname
    declare @conflict_proc sysname
    declare @conflict_table sysname
    declare @snapshot_ready int
    declare @ins_procname sysname
    declare @sel_procname sysname
    declare @del_procname sysname
    declare @sel_metadata_procname sysname
    declare @upd_procname sysname
    declare @ins_batch_procname sysname
    declare @upd_batch_procname sysname
    declare @view_selprocname nvarchar(290)
    declare @viewname sysname
    declare @artid uniqueidentifier
    declare @objid int
    declare @retcode smallint
    declare @dbname sysname
    declare @generate_downlevel_procs bit

    declare @procsuffix nvarchar(100)
    declare @sync_objid int
    declare @qual_conflict_table nvarchar(300)


    set nocount on
    /*
    ** Check to see if current publication has permission
    */
    exec @retcode=sys.sp_MSreplcheck_publish
    if @retcode<>0 or @@ERROR<>0 return (1)

    if object_id('sysmergepublications') is NULL
    begin
        RAISERROR(20054 , 16, -1)
        return (1)
    end

    if @pubid is NULL
    begin
        if @force_flag = 1
        begin
            -- don't qualify that must be publisher when we are forcing remake at subscribers
            select @pubid = pubid, @snapshot_ready=snapshot_ready
                from dbo.sysmergepublications
                where name = @publication and
                pubid in (select pubid from dbo.sysmergearticles where name=@article)
        end
        else
        begin
            select @pubid = pubid, @snapshot_ready=snapshot_ready
                from dbo.sysmergepublications
                where name = @publication and
                upper(publisher collate SQL_Latin1_General_CP1_CS_AS) = upper(publishingservername() collate SQL_Latin1_General_CP1_CS_AS) and
                publisher_db=db_name()
        end
    end
    else
    begin
        select @snapshot_ready=snapshot_ready
            from dbo.sysmergepublications
            where pubid = @pubid
    end

    select @artid= artid, @objid = objid, @conflict_table= conflict_table
            FROM dbo.sysmergearticles WHERE name = @article AND pubid = @pubid

    if @artid IS NULL
    BEGIN
        RAISERROR (20027, 16, -1, @article)
        RETURN (1)
    END

    select @generate_downlevel_procs = 0
    if (sys.fn_MSmerge_islocalpubid(@pubid) = 1)
    begin
        if exists (select 1 from dbo.sysmergepublications where pubid = @pubid and backward_comp_level<90)
            select @generate_downlevel_procs = 1
    end

    -- get owner name, and table name
    select @objectname = name, @ownername = SCHEMA_NAME(schema_id)  from sys.objects where object_id = @objid

    select @procsuffix = sys.fn_MSmerge_getartprocsuffix(@artid, @pubid)

    -- Conflict insert proc is common to both regular and lightweight subscribers
    -- to be consistent with upgrade code by checking snapshot_ready>0
    select @qual_conflict_table = 'dbo.' + @conflict_table
    if @snapshot_ready>0 and @conflict_table is not NULL and object_id(@qual_conflict_table) is not NULL
        select @conflict_proc = 'MSmerge_cft_sp_' + @procsuffix
    else
        select @conflict_proc = NULL


    /* Drop the article procs if they preexist */
    exec @retcode = sys.sp_MSdroparticleprocs @pubid, @artid, 0 -- don't update proc names
    if @@ERROR<>0 OR @retcode<>0 goto FAILURE

    -- do this first. This is needed since we want to do the update of sysmergearticles at the end
    if @generate_downlevel_procs = 1
    begin
        declare @ins_procname_bc sysname
        declare @upd_procname_bc sysname
        declare @sel_procname_bc sysname

        /* Downlevel agents will use sp_ins_ prefix for the proc names */
        select @ins_procname_bc = 'sp_ins_' + @procsuffix
        select @upd_procname_bc = 'sp_upd_' + @procsuffix
        select @sel_procname_bc = 'sp_sel_' + @procsuffix

        exec @retcode=sys.sp_MScreateandsetarticleprocs
                @pubid = @pubid,
                @artid = @artid,
                @objectname = @objectname,
                @ownername = @ownername,
                @ins_procname = @ins_procname_bc,
                @ins_batch_procname = NULL,
                @upd_procname = @upd_procname_bc,
                @upd_batch_procname = NULL,
                @sel_procname = @sel_procname_bc,
                @del_procname = NULL,
                @sel_metadata_procname = NULL,
                @view_selprocname = NULL,
                @conflict_proc = NULL,
                @generate_downlevel_procs = 1,
                @update_symergearticles = 0
        if @retcode<>0 or @@ERROR<>0 goto FAILURE
    end

    select @ins_procname = 'MSmerge_ins_sp_' + @procsuffix
    select @upd_procname = 'MSmerge_upd_sp_' + @procsuffix

    -- Don't generate the batch insert and update procs if the article has a xml
    -- column. XML column needs streaming and we don't do batch updates and inserts when we have
    -- to do streaming.
    select @sync_objid = sync_objid from dbo.sysmergearticles where artid = @artid and pubid=@pubid
    select @ins_batch_procname = NULL
    select @upd_batch_procname = NULL
    if not exists (select * from sys.columns where object_id = @sync_objid and system_type_id = 241)
    begin
       select @ins_batch_procname = @ins_procname + '_batch'
       select @upd_batch_procname = @upd_procname + '_batch'
    end

    select @sel_procname = 'MSmerge_sel_sp_' + @procsuffix
    select @del_procname = 'MSmerge_del_sp_' + @procsuffix
    select @sel_metadata_procname = 'MSmerge_sel_sp_' + @procsuffix + '_metadata'
    set @view_selprocname = 'MSmerge_sel_' + @procsuffix

    -- create the procs passing in the proc names
    exec @retcode=sys.sp_MScreateandsetarticleprocs
            @pubid = @pubid,
            @artid = @artid,
            @objectname = @objectname,
            @ownername = @ownername,
            @ins_procname = @ins_procname,
            @ins_batch_procname     = @ins_batch_procname,
            @upd_procname = @upd_procname,
            @upd_batch_procname     = @upd_batch_procname,
            @sel_procname = @sel_procname,
            @del_procname = @del_procname,
            @sel_metadata_procname = @sel_metadata_procname,
            @view_selprocname = @view_selprocname,
            @conflict_proc = @conflict_proc,
            @generate_downlevel_procs = 0
    if @retcode<>0 or @@ERROR<>0 goto FAILURE


    return 0

FAILURE:
    RAISERROR(20636, 16, -1, @article)
    return 1


 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
sp_MSpublicationview (Procedure)
sp_MSregenerate_mergetriggersprocs (Procedure)
sp_MSResetTriggerProcs (Procedure)
sp_repladdcolumn (Procedure)
sp_repldropcolumn (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