Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreatebeforetable

  No additional text.


Syntax
create procedure sys.sp_MScreatebeforetable
    @objid int
AS
    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
    begin
        exec @retcode = sys.sp_MShelpalterbeforetable @objid, @oldname
        if @@error<>0 or @retcode<>0 return (1)
        return(0)
    end
-- 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)
        return(0)

-- 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
    begin
        exec ('grant select (' + @before_rowguidname + ') on ' + @bitablename + ' to public')
        if @@ERROR<>0 return (1)
    end

    -- 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
    begin
        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
    end
    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

    return(0)

 
Last revision 2008RTM
See also

  sp_MSaddmergetriggers (Procedure)
sp_MSdrop_rlrecon (Procedure)
sp_MSpublicationview (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