Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreateandsetarticleprocs

  No additional text.


Syntax
create procedure sys.sp_MScreateandsetarticleprocs    (
    @pubid                              uniqueidentifier,
    @artid                              uniqueidentifier,
    @objectname             sysname,
    @ownername                  sysname,
    @ins_procname               sysname,
    @ins_batch_procname sysname,
    @upd_procname               sysname,
    @upd_batch_procname sysname,
    @sel_procname               sysname,
    @del_procname               sysname,
    @sel_metadata_procname      sysname,
    @view_selprocname   sysname,
    @conflict_proc              sysname,
    @generate_downlevel_procs bit = 0,
    @update_symergearticles bit = 1)

as
    declare @retcode    smallint
    declare @dbname     sysname
    declare @command    nvarchar(4000)
    declare @objid      int
    declare @sync_objid int

    -- Create the procs, the calling code is responsible for dropping the procs if they pre-exist
    set @dbname = db_name()
    select @objid = objid, @sync_objid= sync_objid FROM dbo.sysmergearticles WHERE artid = @artid AND pubid = @pubid

    set @command= 'SET ANSI_NULLS ON SET QUOTED_IDENTIFIER ON'
    exec (@command)
    if @@ERROR<>0 goto FAILURE

    if (@ins_procname IS NOT NULL and object_id(@ins_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakeinsertproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@ins_procname)  + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']' + ', @generate_downlevel_p
rocs = ' + convert(nchar(1), @generate_downlevel_procs)
        exec @retcode = sys.xp_execresultset @command, @dbname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        exec @retcode = sys.sp_MS_marksystemobject  @ins_procname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@ins_procname) + ' to public'
        exec (@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@ins_batch_procname IS NOT NULL and object_id(@ins_batch_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakebatchinsertproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@ins_batch_procname)  + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']'
        exec @retcode = sys.xp_execresultset @command, @dbname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        exec @retcode = sys.sp_MS_marksystemobject  @ins_batch_procname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@ins_batch_procname) + ' to public'
        exec (@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@upd_procname IS NOT NULL and object_id(@upd_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakeupdateproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@upd_procname) + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']' + ', @generate_downlevel_pr
ocs = ' + convert(nchar(1), @generate_downlevel_procs)
        exec @retcode = sys.xp_execresultset @command, @dbname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        exec @retcode = sys.sp_MS_marksystemobject  @upd_procname
        if @@ERROR<>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@upd_procname) + ' to public'
        exec(@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@upd_batch_procname IS NOT NULL and object_id(@upd_batch_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakebatchupdateproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@upd_batch_procname) + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']'
        exec @retcode = sys.xp_execresultset @command, @dbname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
      exec @retcode = sys.sp_MS_marksystemobject  @upd_batch_procname
        if @@ERROR<>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@upd_batch_procname) + ' to public'
        exec(@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@sel_procname IS NOT NULL and object_id(@sel_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakeselectproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@sel_procname) + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']' + ', @generate_downlevel_pr
ocs = ' + convert(nchar(1), @generate_downlevel_procs)
		exec @retcode = sys.sp_execresultset_nvarcharmaxrows @command
        if @@ERROR<>0 or @retcode<>0
            goto FAILURE
        exec @retcode = sys.sp_MS_marksystemobject  @sel_procname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@sel_procname) + ' to public'
        exec (@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@del_procname IS NOT NULL and object_id(@del_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakedeleteproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@del_procname) + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']'
        exec @retcode = sys.xp_execresultset @command, @dbname
        if @@ERROR<>0 or @retcode<>0
            goto FAILURE
        exec @retcode = sys.sp_MS_marksystemobject  @del_procname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@del_procname) + ' to public'
        exec (@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@sel_metadata_procname IS NOT NULL and object_id(@sel_metadata_procname) is NULL)
    begin
        set @command = 'sys.sp_MSmakemetadataselectproc ' + QUOTENAME(@objectname) + ' , ' + QUOTENAME(@ownername) + ' , ' + quotename(@sel_metadata_procname) + ', [' + convert(nchar(36), @pubid) + ']' + ', [' + convert(nchar(36), @artid) + ']'
        exec @retcode = sys.xp_execresultset @command, @dbname
        if @@ERROR<>0 or @retcode<>0
            goto FAILURE
        exec @retcode = sys.sp_MS_marksystemobject  @sel_metadata_procname
        if @@ERROR<>0 OR @retcode <>0 goto FAILURE
        select @command = 'grant exec on ' + quotename(@sel_metadata_procname) + ' to public'
        exec (@command)
        if @@ERROR<>0 goto FAILURE
    end

    if (@view_selprocname IS NOT NULL and object_id(@view_selprocname) is NULL)
    begin
        declare @viewownername  sysname
        declare @viewname               sysname
        declare @rgcol                  sysname

        if @sync_objid is NULL or @sync_objid = 0
            select @sync_objid = @objid

        select @viewownername = SCHEMA_NAME(schema_id), @viewname = name from sys.objects where object_id = @sync_objid
        select @rgcol = name from sys.columns where object_id = @objid and is_rowguidcol = 1
        exec @retcode=sys.sp_MSmakeviewproc @viewname, @viewownername, @view_selprocname, @rgcol, @pubid, @artid
        if @@ERROR<>0 or @retcode<>0 goto FAILURE
    end

    if (@conflict_proc IS NOT NULL and object_id(@conflict_proc) is NULL)
    begin
        declare @conflict_table sysname
        declare @conflict_table_schema sysname
        declare @qualified_conflict_table nvarchar(517)

        select @conflict_table= conflict_table FROM dbo.sysmergearticles WHERE artid = @artid AND pubid = @pubid
        -- In yukon this should always be dbo
        select @conflict_table_schema = 'dbo'
        select @qualified_conflict_table = quotename(@conflict_table_schema) + '.' + quotename(@conflict_table)
        if object_id(@qualified_conflict_table) is not NULL
        begin
            set @command = 'sys.sp_MSmakeconflictinsertproc ' + QUOTENAME(@conflict_table) + ' , ' + QUOTENAME(@conflict_table_schema) + ' , ' + quotename(@conflict_proc)  + ' , ' + convert(nvarchar,@objid)
            set @command = @command + ', [' + convert(nchar(36), @pubid) + ']'
            exec @retcode = sys.xp_execresultset @command, @dbname
            if @@ERROR<>0 OR @retcode<>0 or object_id(@conflict_proc) is NULL
                goto FAILURE
            exec @retcode = sys.sp_MS_marksystemobject @conflict_proc
            if @@ERROR<>0 or @retcode<>0
                goto FAILURE
			select @command = 'grant exec on ' + quotename(@conflict_proc) + ' to public'
            exec (@command)
            if @@ERROR<>0 goto FAILURE
        end
    end

    if @update_symergearticles = 1
    begin
        update dbo.sysmergearticles
            set insert_proc = @ins_procname,
                select_proc = @sel_procname,
                metadata_select_proc = @sel_metadata_procname,
                update_proc = @upd_procname,
                view_sel_proc = @view_selprocname,
                ins_conflict_proc = @conflict_proc,
                delete_proc = @del_procname
            where artid = @artid and pubid=@pubid
        IF @@ERROR<>0 goto FAILURE
    end
    return 0

FAILURE:
    return 1



 
Last revision 2008RTM
See also

  sp_MSdrop_rlrecon (Procedure)
sp_MSmakearticleprocs (Procedure)
sp_MSsetartprocs (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