Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_MSbelongs

  No additional text.


Syntax

-- Modify temp table. No security check needed.
create procedure sys.sp_MSbelongs
    @publisher      sysname,
    @publisher_db   sysname,
    @publication    sysname,
    @tablenick      int,
    @rowguid        uniqueidentifier,
    @retval         int output,
    @nested         int = 0
AS
    declare @artid uniqueidentifier
    declare @join_guid uniqueidentifier
    declare @last_joinid int
    declare @join_id int
    declare @join_nick int
    declare @probe_id int
    declare @last_probe int
    declare @join_nickstr nvarchar(10)
    declare @pubid uniqueidentifier
    declare @guidstring nvarchar(38)
    declare @subset_filter nvarchar(4000)
    declare @qualified_tablename nvarchar(517)
    declare @unqualified_tablename nvarchar(266)
    declare @qualified_join_table nvarchar(517)
    declare @unqualified_join_table nvarchar(266)
    declare @boolean nvarchar(4000)
    declare @retcode smallint

    select @pubid = pubid from dbo.sysmergepublications where name = @publication and UPPER(publisher)=UPPER(@publisher) and publisher_db=@publisher_db

    /* Check for case of all rows  - do I trust subset_type ? */
    select @boolean = subset_filterclause, @artid = artid from dbo.sysmergearticles where
        pubid = @pubid and nickname = @tablenick

    if ((@boolean is null or @boolean = ' ') and not exists
        (select * from dbo.sysmergesubsetfilters where art_nickname = @tablenick and (filter_type & 1) = 1))
        begin
        set @retval = 0
        return
        end
    if @nested = 0
        begin
        create table #found (flag int NOT NULL)
        create table #probe (probe_id int identity NOT NULL, tablenick int NOT NULL,
                rowguid uniqueidentifier ROWGUIDCOL default newsequentialid() not null, tested int NOT NULL)
        insert into #found values (0)
        set @last_probe = 0
        end
    else
        begin
        select @last_probe = max(probe_id) from #probe
        end

    /* pubid is already available */

    exec @retcode = sys.sp_MStablenamefromnick @tablenick, @qualified_tablename out, @pubid, @unqualified_tablename out

    if  @@ERROR<>0 or @retcode<>0 return (1)
    set @guidstring = '''' + convert(nchar(36), @rowguid) + ''''

    /* If there is boolean filter, check for it being satisfied */
    if @boolean is not null and @boolean <> ' '
        begin
        exec ('if exists (select * from ' + @qualified_tablename + ' ' + @unqualified_tablename + ' where rowguidcol = ' +
            @guidstring + ' and (' + @boolean + ')) update #found set flag = 1')
        if @@ERROR<>0 return (1)
        select @retval = flag from #found
        if @retval = 1 goto EndLabel
        end

    /* Loop over join filters, populating #probe */
    select @join_id = min(join_filterid) from dbo.sysmergesubsetfilters where pubid = @pubid and art_nickname = @tablenick and (filter_type & 1) = 1
    while @join_id is not null
        begin
        select @boolean = join_filterclause, @join_nick = join_nickname from dbo.sysmergesubsetfilters where pubid = @pubid and join_filterid = @join_id and (filter_type & 1) = 1
        exec @retcode = sys.sp_MStablenamefromnick @join_nick, @qualified_join_table out, @pubid, @unqualified_join_table out
        if @@ERROR<>0 or @retcode<>0 return (1)
        set @join_nickstr = convert(nchar(10), @join_nick)

        /* execute a query to put these into the #probe table */
        exec ('insert into #probe (tablenick, rowguid, tested) select distinct ' + @join_nickstr +
            ', ' + @unqualified_join_table + '.rowguidcol, 0 from ' + @qualified_tablename + ' ' + @unqualified_tablename + ', ' + @qualified_join_table + ' ' + @unqualified_join_table + '
            where ' + @unqualified_tablename + '.rowguidcol = ' + @guidstring + ' and (' + @boolean + ')
            and not exists (select * from #probe where tablenick = ' + @join_nickstr +
      ' and rowguidcol = ' + @unqualified_join_table + '.rowguidcol) ' )
        if @@ERROR<>0
            begin
                return (1)
            end

        /* get to next join filter and repeat */
        set @last_joinid = @join_id
        select @join_id = min(join_filterid) from dbo.sysmergesubsetfilters where pubid = @pubid and art_nickname = @tablenick and join_filterid > @last_joinid and (filter_type & 1) = 1
        end
    /* Loop over probe, making recursive call */
    select @probe_id = min(probe_id) from #probe where probe_id > @last_probe and tested = 0
    while @probe_id is not null
        begin
        select @join_nick = tablenick, @join_guid = rowguidcol from #probe where probe_id = @probe_id
        set @last_probe = @probe_id

        /* update tested flag on this row so we don't try it again while recursing */
        update #probe set tested = 1 where probe_id = @probe_id

        /* Make recursive call. If it belongs, we are done. */
        exec @retcode = sys.sp_MSbelongs @publisher, @publisher_db, @publication, @join_nick, @join_guid, @retval output, 1
        if @@ERROR<>0 OR @retcode<>0
            begin
                return (1)
            end

        if @retval = 1 goto EndLabel
        /* get next probe_id and repeat */
        select @probe_id = min(probe_id) from #probe where probe_id > @last_probe and tested = 0
        end

    /* All Done, delete temps if not nested */
EndLabel:
    if @nested = 0
        begin
        drop table #found
        drop table #probe
        end
    return


 
Last revision 2008RTM
See also

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