Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScreate_article_logical_record_views

  No additional text.


Syntax

create procedure sys.sp_MScreate_article_logical_record_views @art_nick int
as
    set nocount on
    declare @view_rule nvarchar(max),
            @source_objid int,
            @prefix_tablename nvarchar(270),
            @source_object nvarchar(517),
            @unqual_sourcename nvarchar(270),
            @rgcol nvarchar(270),
            @join_rgcol nvarchar(270),
            @join_clause nvarchar(4000),
            @join_nick int,
            @join_objid int,
            @join_unqual_name nvarchar(270),
            @join_logical_record_view nvarchar(270),
            @logical_record_view nvarchar(270),
            @logical_record_deleted_view_rule nvarchar(max),
            @prefixed_column_list nvarchar(max),
            @partition_column_list nvarchar(max),
            @arbitrary_pubid uniqueidentifier,
            @retcode int,
            @pubid uniqueidentifier,
            @permission nvarchar(max)

    -- Return immediately if no logical record relationships are defined.
    if not exists (select * from dbo.sysmergesubsetfilters
                    where (filter_type & 2) = 2
                    and (join_nickname = @art_nick or art_nickname = @art_nick))
        return 0

    select top 1 @arbitrary_pubid = pubid, @source_objid = objid from dbo.sysmergearticles sma where nickname = @art_nick
    and exists (select * from dbo.sysmergepartitioninfo smpi
					where sma.artid = smpi.artid and sma.pubid = smpi.pubid and smpi.logical_record_parent_nickname is not null)

    select @source_object = QUOTENAME(SCHEMA_NAME(schema_id)) + '.' + QUOTENAME(name) from sys.objects
        where object_id = @source_objid
    select @unqual_sourcename = QUOTENAME(OBJECT_NAME(@source_objid))

    select @rgcol = QUOTENAME(name) from sys.columns where object_id = @source_objid
        and is_rowguidcol = 1

    if @rgcol is NULL
        return 0

    /* Get the explicit list of columns for the base table */
    set @prefix_tablename = @unqual_sourcename + '.'
    exec @retcode = sys.sp_MSgetviewcolumnlist @pubid = @arbitrary_pubid,  @source_objid = @source_objid, @column_list = @prefixed_column_list OUTPUT, @prefix_tablename = @prefix_tablename, @blob_cols_at_the_end = 0, @explicit_column_list = 1
    if @@error <> 0 or @retcode <> 0
        return 1

    exec @retcode = sys.sp_MSgetfiltercolumnlist @arbitrary_pubid, @partition_column_list OUTPUT,
                                                    @source_objid, @unqual_sourcename, 1
    if @@error <> 0 or @retcode <> 0
        return 1

    -- Build the permission check string
    select @permission = 'permissions(' + convert(nvarchar(12),@source_objid) + ') & 0x1b <> 0 '

    declare pubid CURSOR LOCAL FAST_FORWARD FOR
    select pubid from dbo.sysmergearticles where nickname = @art_nick
    FOR READ ONLY
    open pubid
    fetch pubid into @pubid
    while (@@fetch_status <> -1)
    begin
    	select @permission = @permission + 'or {fn ISPALUSER(''' + convert(nvarchar(36), @pubid) + ''')} = 1 '
    	fetch next from pubid into @pubid
    end
	
    if not exists (select * from dbo.sysmergesubsetfilters
                    where (filter_type & 2) = 2
                    and art_nickname = @art_nick)
    begin
        -- This is a top-level article. Generate a view that returns the same rowguid as the parent rowguid.
        -- The top-level article gets called from triggers and hence cannot include the subset_filter_clause
        -- that might contain dynamic filters that cannot be evalauated at trigger execution time.
        select @view_rule = 'select ' + @prefixed_column_list
                + ', logical_record_parent_rowguid = ' + @unqual_sourcename + '.' + @rgcol
                + ' from ' + @source_object + ' ' + @unqual_sourcename + ' where( ' + @permission + ' )'

        select @logical_record_deleted_view_rule = 'select ' + @partition_column_list
                + ', logical_record_parent_rowguid = ' + @unqual_sourcename + '.' + @rgcol
                + ' from deleted ' + @unqual_sourcename
    end
    else
    begin
        -- This is not a top-level article. Its view joins this table with the parent's logical record view
        -- based on the join filter. Note that we can assume that we only have one parent article. In case where
        -- there are 2 separate join filters with the same parent (say in 2 publications), it is okay to get the
        -- top 1 since the multiple join filters between a pair of articles should be same if they are a part of
        -- a logical record.
        select top 1 @join_clause = join_filterclause, @join_nick = join_nickname from dbo.sysmergesubsetfilters
            where art_nickname = @art_nick
            and (filter_type & 2) = 2

        declare @join_artid uniqueidentifier

        select top 1 @join_objid = objid, @join_artid = artid
        from dbo.sysmergearticles where nickname = @join_nick

        select top 1 @join_logical_record_view = 'dbo.' + quotename(object_name(logical_record_view))
        from dbo.sysmergepartitioninfo where artid = @join_artid
        and logical_record_parent_nickname is not null

        select @join_unqual_name = QUOTENAME(OBJECT_NAME(@join_objid))

        if @join_logical_record_view is NULL
            return 0

        select @join_rgcol = QUOTENAME(name) from sys.columns where object_id = @join_objid
        and is_rowguidcol = 1

        if @join_rgcol is NULL
            return 0

        -- The view returns all columns of this table, and the logical_record_parent_rowguid from the parent's
        -- logical record view.
        select @view_rule = 'select ' + @prefixed_column_list
                + ', logical_record_parent_rowguid = ' + @join_unqual_name + '.logical_record_parent_rowguid '
                + '    from ' + @source_object + ' ' + @unqual_sourcename + ', ' + @join_logical_record_view + ' '
                + @join_unqual_name + ' where (' + @join_clause + ') and (' + @permission + ')'

        select @logical_record_deleted_view_rule = 'select ' + @partition_column_list
                + ', logical_record_parent_rowguid = ' + @join_unqual_name + '.logical_record_parent_rowguid
                from deleted ' + @unqual_sourcename + ', ' + @join_logical_record_view + ' '
                + @join_unqual_name + ' where (' + @join_clause + ')'
    end

    select @logical_record_view = 'MSmerge_view_logical_record_' + convert(nvarchar, @art_nick)
    if object_id(@logical_record_view) is not null
        exec('drop view ' + @logical_record_view)

    exec ('create view dbo.' + @logical_record_view + ' as ' + @view_rule)
    if @@error <> 0 return 1

	exec ('grant select on ' + @logical_record_view + ' to public')
	execute dbo.sp_MS_marksystemobject @logical_record_view
    if @@error <> 0 return 1

    declare @artid uniqueidentifier
    select top 1 @artid = artid from dbo.sysmergearticles where nickname = @art_nick
    update dbo.sysmergepartitioninfo set logical_record_view = object_id(@logical_record_view),
                                    logical_record_deleted_view_rule = @logical_record_deleted_view_rule
    where artid = @artid
    and logical_record_parent_nickname is not null

    return 0

 
Last revision 2008RTM
See also

  sp_MScreate_logical_record_views (Procedure)
sp_MSdrop_rladmin (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