Home Microsoft SQL Server DigiMailing iRN Contact


  No additional text.

create procedure sys.sp_MScreatebeforetable
    @objid int
    declare     @command            nvarchar(4000)
    declare     @objidstr           nvarchar(12)
    declare     @dbname             sysname
    declare     @oldname            sysname
    declare     @newname            sysname
    declare     @nameguid           uniqueidentifier
    declare     @before_rowguidname nvarchar(258)
    declare     @retcode            int
    declare     @tablenick          int
    declare     @bitablename        sysname
    declare     @biupdviewname      sysname

    set nocount on

    set @before_rowguidname = NULL

-- If no publication including this table needs before images, just return
    if not exists (select * from dbo.sysmergepublications p, dbo.sysmergearticles a where
            a.objid = @objid and p.pubid = a.pubid and p.keep_before_values = 1)
        return (0)
    select @tablenick = max(nickname) from dbo.sysmergearticles where objid = @objid
    if @tablenick is null return (1)

-- If a before image table already exists for this table, we need to drop it and create a new one
    select @oldname = max(o.name) from sys.objects o, dbo.sysmergearticles a where
        a.objid = @objid and o.object_id = a.before_image_objid
    if @oldname is not null
        exec @retcode = sys.sp_MShelpalterbeforetable @objid, @oldname
        if @@error<>0 or @retcode<>0 return (1)
-- If this table is not involved with any filters or join filters, don't bother
    if not exists (select * from dbo.sysmergesubsetfilters where (art_nickname = @tablenick
        or join_nickname = @tablenick)  and (filter_type & 1) = 1) and
        not exists (select * from dbo.sysmergearticles where nickname = @tablenick and
            datalength (subset_filterclause) > 1)

-- Generate a unique name for our new table
    set @nameguid = newid()
    exec @retcode = sys.sp_MSguidtostr @nameguid, @newname out
    if @@ERROR <>0 OR @retcode <>0 return (1)
    set @bitablename = 'MSmerge_bi_' + @newname

-- Call xp_execresultset with helper function command
    set @objidstr = convert(nvarchar(12), @objid)
    set @dbname = db_name()

    set @command = 'exec sys.sp_MShelpcreatebeforetable '  + @objidstr + ', ''' + @bitablename + ''''
    exec @retcode = sys.xp_execresultset @command, @dbname
    if @@error<>0 or @retcode<>0 return (1)

    select @before_rowguidname=quotename(name) from sys.columns where object_id=@objid and is_rowguidcol=1

    if @before_rowguidname is not NULL
        exec ('grant select (' + @before_rowguidname + ') on ' + @bitablename + ' to public')
        if @@ERROR<>0 return (1)

    -- create a view in which generation can be updated and we can delete and let this view be accesible to pal of
    -- any publication. This is necessary because a pal user of pub A may be doing makegeneration for article in pub B
    set @biupdviewname = 'MSmerge_biupdvw_' + @newname
    set @command = 'create view dbo.'  + @biupdviewname + ' as select * from ' + @bitablename +
        ' where exists (select * from dbo.sysmergepublications where {fn ISPALUSER(pubid)} = 1)'

    exec @retcode = sys.xp_execresultset @command, @dbname
    if @@error<>0 or @retcode<>0
        return (1)

    /* grant select to system_delete column */
    -- grant only to the PAL role instead of to public
    declare @role sysname
    declare @pubid uniqueidentifier
    declare publications_list CURSOR LOCAL FAST_FORWARD
        for select p.pubid from dbo.sysmergearticles a, dbo.sysmergepublications p
            where objid = @objid and p.pubid = a.pubid and UPPER(p.publisher)  collate database_default = UPPER(publishingservername()) collate database_default and p.publisher_db = db_name()

    open publications_list
    fetch publications_list into @pubid
    while @@fetch_status <> -1
        exec @retcode = dbo.sp_MSrepl_FixPALRole @pubid, @role output
        if @retcode <> 0 or @@ERROR<>0
            return (1)

	   	select @command = 'grant select (system_delete, generation), update(generation), delete, insert on ' + @bitablename + ' to ' + quotename(@role)
        exec (@command)
        if @@ERROR<>0 return (1)

        exec ('grant select(generation), update(generation), delete on ' + @biupdviewname + ' to public')
        if @@ERROR<>0 return (1)

        fetch publications_list into @pubid
    close publications_list
    deallocate publications_list

    exec sys.sp_MS_marksystemobject @bitablename
    if @@ERROR<>0 return (1)

    exec sys.sp_MS_marksystemobject @biupdviewname
    if @@ERROR<>0 return (1)

    update dbo.sysmergearticles set before_image_objid = object_id( @bitablename ), before_upd_view_objid = object_id(@biupdviewname)
        where objid = @objid


Last revision 2008RTM
See also

  sp_MSaddmergetriggers (Procedure)
sp_MSdrop_rlrecon (Procedure)
sp_MSpublicationview (Procedure)


  Query a named instance
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
Zoekmachine optimalisatie
SQL Servers hidden objects
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
sIFR; de combinatie tussen HTML en Flash