Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MScontractsubsnb

  No additional text.


Syntax

create procedure sys.sp_MScontractsubsnb
    (@pubid uniqueidentifier,
     @tablenick int,
     @basetable nvarchar(517))
AS
    declare     @filter_clause  nvarchar(4000)
    declare     @join_nick int
    declare     @qualified_jointable      nvarchar(517)
    declare     @unqualified_jointable      nvarchar(270)
    declare     @filterid int
    declare     @retcode int
    declare     @tablenickstr nvarchar(10)
    declare 	@unqual_basetable sysname
	declare		@command nvarchar(max)

    set @tablenickstr = convert(nchar(10), @tablenick)

	select @unqual_basetable = name from sys.objects where object_id=object_id(@basetable)

    /* First, try to remove rows from notbelong based on the article filter, if there is one */
    select @filter_clause = subset_filterclause from dbo.sysmergearticles where
        pubid = @pubid and nickname = @tablenick
    if len(@filter_clause) > 0
        begin
        exec ('delete from #notbelong with (paglock) where tablenick = ' + @tablenickstr + ' and rowguid in
                (select RowGuidCol from ' + @basetable + ' where ' + @filter_clause + ')' )
        if @@error<>0 return(1)
        end

    /* Now loop over any join filters that have this as the base_table */
    select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters
        where pubid = @pubid and art_nickname = @tablenick and (filter_type & 1) = 1
    while @filterid is not null
        begin
        /* Get joining table and filter clause */
        select @join_nick = join_nickname, @filter_clause = join_filterclause
                from dbo.sysmergesubsetfilters where pubid = @pubid and join_filterid = @filterid and (filter_type & 1) = 1
        exec @retcode = sys.sp_MStablenamefromnick @join_nick, @qualified_jointable out, @pubid, @unqualified_jointable out
        if @@error<>0 or @retcode<>0 return(1)

        /* Exec query to remove rows from #notbelong that still belong to partial */
        select @command = 'delete from #notbelong with (paglock) where tablenick = ' + @tablenickstr + ' and rowguid in
                (select ' + quotename(@unqual_basetable) + '.RowGuidCol from ' + @basetable + ' ' + quotename(@unqual_basetable) + ', ' +
                @qualified_jointable + ' ' + @unqualified_jointable + ' where (' + @filter_clause + ') and ' + @unqualified_jointable +
                '.RowGuidCol not in (select rowguid from #notbelong))'
		exec(@command)
        if @@error<>0 return(1)

        /* Find the next filter that might apply */
        select @filterid = min(join_filterid) from dbo.sysmergesubsetfilters
            where pubid = @pubid and art_nickname = @tablenick and join_filterid > @filterid and (filter_type & 1) = 1
        end
        return(0)

 
Last revision 2008RTM
See also

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