May 15, 2012

sp_MSgetlastrecgen (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetlastrecgen(uniqueidentifier @repid)

MetaData:

   
create procedure sys.sp_MSgetlastrecgen  
    (@repid uniqueidentifier)  
as  
    declare @retcode    int  
  
    --   
    --  do permission checking  
     --   
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck @repid = @repid  
    if @retcode<>0 or @@ERROR<>0 return (1)  
  
    if object_id('MSmerge_contents') is NULL and   
       object_id('MSmerge_rowtrack') is NULL  
    begin  
        RAISERROR(20054 , 16, -1)  
        return (1)  
    end  
  
    if (@repid is null)  
    begin  
        RAISERROR(14043, 16, -1, '@repid', 'sp_MSgetlastrecgen')  
        return (1)  
    end  
  
    select recgen, recguid from dbo.sysmergesubscriptions where subid = @repid  
    return (0)
Indsendt af kl. No comments:

sp_MSgetonerowlightweight (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetonerowlightweight(int @tablenick
, uniqueidentifier @rowguid
, uniqueidentifier @pubid)

MetaData:

 create procedure sys.sp_MSgetonerowlightweight  
    @tablenick    int,  
    @rowguid    uniqueidentifier,  
    @pubid        uniqueidentifier  
as  
    declare @retcode    smallint  
    declare @procname    sysname  
    declare @postfix    nchar(32)  
  
    exec @retcode = sys.sp_MSreplcheck_subscribe   
    if @retcode <> 0 or @@error <> 0 return 1  
          
    select @postfix= procname_postfix  
        from dbo.sysmergearticles   
        where nickname = @tablenick and pubid = @pubid  
      
    set @procname= quotename('MSmerge_lws_sp_multi_' + @postfix)  
  
    exec @retcode= @procname @action = 1, @rowguid = @rowguid  
    if @@error<>0 or @retcode<>0 return (1)  
  
    return (0)
Indsendt af kl. No comments:

sp_MSgetpeerwinnerrow (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetpeerwinnerrow(nvarchar @originator_id
, nvarchar @row_id
, nvarchar @conflict_table)

MetaData:

 create procedure sys.sp_MSgetpeerwinnerrow  
(  
    @originator_id nvarchar(32) = '%', -- int  
    @row_id nvarchar(19) = '%',   --  timestamp  
    @conflict_table nvarchar(270)  
)  
as   
begin  
    set nocount on  
    declare @retcode int = 0  
                ,@cmd nvarchar(4000)   
                ,@whcmd nvarchar(4000) = N'__$is_winner = 1 '  
  
    select @cmd = N'select ''__$origin_datasource_srvname'' = h.originator_node,  ''__$origin_datasource_db'' = h.originator_db, * from ' + ISNULL(NULLIF(QUOTENAME(PARSENAME(@conflict_table, 2)) + N'.', N'.'), N'') + QUOTENAME(PARSENAME(@conflict_table, 1))  
      
    --   
    --  Security Check.  
     --   
    exec @retcode = sys.sp_MSreplcheck_publish  
    if @@ERROR <> 0 or @retcode <> 0  
        return(1)  
    --   
    --  passed the security check  
    --   
  
    if (@originator_id != N'%')  
    begin  
            select @whcmd = @whcmd + N' and __$originator_id = ' + quotename(@originator_id, '''')  
    end  
    if (@row_id != N'%')  
    begin  
            select @whcmd = @whcmd + N' and ((__$change_id is not null and __$change_id = ' + @row_id  
                    + N') or(__$change_id is null and __$row_id in (select __$change_id from '   
                    + ISNULL(NULLIF(QUOTENAME(PARSENAME(@conflict_table, 2)) + N'.', N'.'), N'') + QUOTENAME(PARSENAME(@conflict_table, 1))   
                    + N' where __$row_id = ' + @row_id + N')))'  
    end  
    select @cmd = @cmd + N' c join MSpeer_originatorid_history h on c.__$origin_datasource = h.originator_id where ' + @whcmd  
  
    --   
    --  execute the generated select  
    --   
    execute (@cmd)  
    --   
    --  all done  
    --   
end
Indsendt af kl. No comments:

sp_MSgetpeertopeercommands (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetpeertopeercommands(nvarchar @publication
, nvarchar @article
, varbinary @snapshot_lsn)

MetaData:

 create procedure sys.sp_MSgetpeertopeercommands  
(  
    @publication    sysname,  
    @article        sysname = N'all',  
    @snapshot_lsn    varbinary(16)     = NULL,  
    @script_txt     nvarchar(MAX)     = NULL OUTPUT  
)  
as  
begin  
    declare @retcode                     int,  
            @publisher                     sysname,  
            @publisher_db                 sysname,  
            @publisher_db_version        int,  
            @is_p2p                     bit,  
            @pubid                        int,  
            @artid                        int,  
            @current_article            sysname,  
            @dest_table                    sysname,  
            @dest_owner                    sysname,  
            @min_artid                    int,  
            @command                    nvarchar(4000),  
            @originator                    sysname,  
            @originator_db                sysname,  
            @originator_publication        sysname,  
            @originator_publication_id    int,  
            @originator_db_version        int,  
            @originator_lsn                varbinary(16),  
            @originator_lsn_str            varchar(2000),  
            @originator_version        int,  
            @originator_id                int  
      
    declare @OPT_ENABLED_FOR_P2P int  
  
    select @OPT_ENABLED_FOR_P2P = 0x1  
      
    declare @art_commands table (artid int NOT NULL, commands nvarchar(4000) collate database_default null, id int identity NOT NULL)  
      
    --  Security Check  
    exec @retcode = sys.sp_MSreplcheck_publish  
    if @@error <> 0 or @retcode <> 0  
    begin  
        return 1  
    end  
      
       --  set the publishd and database name  
    select @publisher         = publishingservername(),  
            @publisher_db     = db_name()  
              
    --  Parameter Check:  @publication  
    if @publication IS NULL  
    begin  
        raiserror(14043, 16, -1, '@publication', 'sp_MSgetpeertopeercommands')  
        return 1  
    end  
      
    --  validate @publication  
    exec @retcode = sys.sp_validname @publication  
    if @@error <> 0 OR @retcode <> 0  
        return 1  
          
    --  get publication metadata  
    select @pubid = pubid,  
            @is_p2p = (options & @OPT_ENABLED_FOR_P2P)  
        from syspublications   
        where name = @publication  
    if @pubid is null  
    begin  
        raiserror (20026, 11, -1, @publication)  
        return 1  
    end  
  
    --  exit with no error if we are not a PeerToPeer publication  
    if @is_p2p = 0  
    begin  
        select @script_txt = NULL  
        return 0  
    end  
  
    --  retrieve the database version   
    exec sys.sp_MSgetdbversion @current_version = @publisher_db_version output  
    if @@error <> 0 OR @retcode <> 0  
        return 1  
      
    --   
     * This section creates commands to be posted to the distribution database  
     * that will insert all necessary MSsubscription_articles entries at the  
     * subscriber database. These entries are only used to validate topology,  
     * publication and subscription configurations. The commands should only  
     * be added for the non-database-restore case. Also, keep in mind that   
     * these commands will not be forwarded throughout the topology...  
     * Remember that we must include extended articles as well (SP, FN, V)  
      --   
    declare publication_art_cursor cursor local fast_forward for  
        select sa.artid,  
                sa.name,  
                sa.dest_table,  
                isnull(sa.dest_owner, N'dbo')  
            from sysextendedarticlesview sa  
            where sa.pubid = @pubid  
              and (@article = N'all'   
                      or name = @article)  
    for read only  
      
    open publication_art_cursor  
  
    fetch publication_art_cursor into @artid, @current_article, @dest_table, @dest_owner  
                                  
    while (@@fetch_status <> -1)  
    begin  
        select @command = N'if (@@microsoftversion >= 0x09000000)' +   
                              N' begin' +  
                                N' exec sys.sp_MSaddsubscriptionarticles @publisher=N' + quotename(@publisher, N'''')  +   
                                                                        N',@publisher_db=N' + quotename(@publisher_db, N'''')  +   
                                                                        N',@publication=N' + quotename(@publication, N'''')  +   
                                                                        N',@artid=' + cast(@artid as nvarchar)  +  
                                                                        N',@article=N' + quotename(@current_article, N'''')  +  
                                                                        N',@dest_table=N' + quotename(@dest_table, N'''')  +  
                                                                        N',@dest_owner=N' + quotename(@dest_owner, N'''')  +  
                            N' end'  
  
        --  if this is NOT the first article for this subscription   
        --  (in other words this is an incremental add article) then  
        --  we will validate the individual article immediately after  
        --  adding it's sub_article meta-data. if this is not the case  
        --  (and this is the first article for the sub) then we will  
        --  perform one validation that will verify all articles instead  
        if @article != N'all'  
        begin  
            select @command = @command + N' ' +  
                            N'if (@@microsoftversion >= 0x09000000)' +   
                              N' begin' +  
                                N' exec sys.sp_MSdetectinvalidpeersubscription @publisher=N' + quotename(@publisher, N'''')  +   
                                                                            N',@publisher_db=N' + quotename(@publisher_db, N'''')  +   
                                                                            N',@publication=N' + quotename(@publication, N'''')  +   
                                                                            N',@article=N' + quotename(@current_article, N'''')  +  
                                                                            N',@dest_table=N' + quotename(@dest_table, N'''')  +  
                                                                            N',@dest_owner=N' + quotename(@dest_owner, N'''')  +  
                                                                            N',@type=''ADD''' +  
                              N' end'  
        end  
          
        if @command is not null  
        begin  
            if @script_txt is NULL  
            begin  
                insert into @art_commands values (@artid, @command)  
            end  
            else  
            begin  
                select @script_txt = @script_txt + @command + N' '  
            end  
        end  
  
        fetch publication_art_cursor into @artid, @current_article, @dest_table, @dest_owner  
    end  
  
    close publication_art_cursor  
    deallocate publication_art_cursor  
  
    --  if this is the subscriptions first article then   
    --  we will perform the extensive article checks that  
    --  validates the entire publication and it's articles.  
    if @article = N'all'  
    begin  
        --  now insert a command to verify that after the commands above are applied there  
        --  are no invalid publication/subscription combinations in the subscriber db  
        select @command = N'if (@@microsoftversion >= 0x09000000)' +   
                              N' begin' +  
                                  N' exec sys.sp_MSdetectinvalidpeerconfiguration @publisher=N' + quotename(@publisher, N'''')  +   
                                                                            N',@publisher_db=N' + quotename(@publisher_db, N'''')  +   
                                                                            N',@publication=N' + quotename(@publication, N'''')  +   
                            N' end'  
  
        if @command is not null  
        begin  
            if @script_txt is NULL  
            begin  
                insert into @art_commands values (@artid, @command)  
            end  
            else  
            begin  
                select @script_txt = @script_txt + @command + N' '  
            end  
        end  
    end  
      
    --   
     * This section creates commands to be posted to the distribution database  
     * that will insert all necessary init records into the LSN table.  
      --   
  
    --  retrieve the min artid so that we can use it when posting PeerToPeer   
    --  commands that do not originate from this server/publisherdb/publication  
    select @min_artid = min(artid) from sysextendedarticlesview where pubid = @pubid and (@article = N'all' or name = @article)  
  
    --  collect peer to peer lsns for a specific publication meta-data on this   
    --  machine. note that here we use @publication instead of @pubid because the   
    --  @pubid could possibly have a different value at each originator database.  
    declare peer_art_cursor cursor local fast_forward for  
        select mspl.originator,  
                mspl.originator_db,  
                mspl.originator_publication,  
                mspl.originator_publication_id,  
                mspl.originator_db_version,  
                mspl.originator_lsn,  
                mspl.originator_version,  
                mspl.originator_id  
            from MSpeer_lsns mspl  
            where mspl.originator_publication = @publication  
    for read only  
      
    open peer_art_cursor  
  
    fetch peer_art_cursor into @originator, @originator_db, @originator_publication,  
                                @originator_publication_id,    @originator_db_version,    @originator_lsn, @originator_version, @originator_id  
  
    while (@@fetch_status <> -1)  
    begin  
        select @originator_lsn_str = sys.fn_varbintohexstr (@originator_lsn)  
  
            if @originator_id is NULL  
                select @originator_id = 0  
          
        select @command = N'if (@@microsoftversion >= 0x0A000000)' +   
                              N' begin' +  
                                  N' exec sys.sp_MSaddpeerlsn @originator=N' + quotename(@originator, N'''') +   
                                                        N',@originator_db=N' + quotename(@originator_db, N'''') +   
                                                        N',@originator_publication=N' + quotename(@originator_publication, N'''') +  
                                                        N',@originator_publication_id=' + cast(@originator_publication_id as nvarchar) +   
                                                        N',@originator_db_version='  + cast(@originator_db_version as nvarchar) +   
                                                        N',@originator_lsn=0x0' +  
                                                        N',@originator_version=' + cast(@originator_version as nvarchar) +  
                                                        N',@originator_id=' + cast(@originator_id as nvarchar) +  
                            N' end' +  
                          N' else if (@@microsoftversion >= 0x09000000)' +   
                              N' begin' +  
                                  N' exec sys.sp_MSaddpeerlsn @originator=N' + quotename(@originator, N'''') +   
                                                        N',@originator_db=N' + quotename(@originator_db, N'''') +   
                                                        N',@originator_publication=N' + quotename(@originator_publication, N'''') +  
                                                        N',@originator_publication_id=' + cast(@originator_publication_id as nvarchar) +   
                                                        N',@originator_db_version='  + cast(@originator_db_version as nvarchar) +   
                                                        N',@originator_lsn=0x0' +  
                            N' end'  
          
        select @command = N' if (@@microsoftversion >= 0x09000000)' +   
                                  N' begin' +  
                                      N' exec sys.sp_MSpeertopeerfwdingexec @command=N''' + replace(@command, N'''', N'''''') + N'''' +  
                                                            N',@publication=N' + quotename(@originator_publication, N'''') +  
                                  N' end'  
  
        if @command is not null  
        begin  
            if @script_txt is NULL  
            begin  
                insert into @art_commands values (@min_artid, @command)  
            end  
            else  
            begin  
                select @script_txt = @script_txt + @command + N' '  
            end  
        end  
  
        --  when adding the lsn for the publisher we want to update it   
        --  to the current snapshot lsn. this is because the snapshot may   
        --  contain bcp files with data that we will not want to reapply.  
        --  keep in mind that this should only be done for regular snapshot  
        --  and not the auto-nosync case (there are no bcp files for autonosync)  
        --  also do not forward this command since it is only meant to adjust  
        --  the lsn for the case where a bcp file was applied via snapshot.  
        if @script_txt is NULL  
            and UPPER(@originator) = UPPER(@publisher)  
            and @originator_db = @publisher_db  
            and @originator_publication_id = @pubid  
            and @originator_db_version = @publisher_db_version  
        begin  
            select @originator_lsn_str = sys.fn_varbintohexstr (@snapshot_lsn)  
  
            select @command = N'if (@@microsoftversion >= 0x09000000)' +   
                              N' begin' +  
                                  N' exec sys.sp_MSupdatepeerlsn @originator=N' + quotename(@originator, N'''') +   
                                                        N',@originator_db=N' + quotename(@originator_db, N'''') +  
                                                        N',@originator_publication_id=' + cast(@originator_publication_id as nvarchar) +   
                                                        N',@originator_db_version='  + cast(@originator_db_version as nvarchar) +   
                                                        N',@originator_lsn='  + @originator_lsn_str +  
                            N' end'  
  
            insert into @art_commands values (@min_artid, @command)  
        end  
          
        fetch peer_art_cursor into @originator, @originator_db, @originator_publication,  
                                @originator_publication_id,    @originator_db_version,    @originator_lsn, @originator_version, @originator_id  
    end  
  
    close peer_art_cursor  
    deallocate peer_art_cursor  
      
    --  return all commands to the client  
    if @script_txt is NULL  
    begin  
        select *   
               from @art_commands   
               order by id  
    end  
      
    return 0  
end
Indsendt af kl. No comments:

sp_MSgetpeerlsns (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetpeerlsns(nvarchar @publication
, bit @xlockrows)

MetaData:

 create procedure sys.sp_MSgetpeerlsns  
(  
    @publication    sysname,  
     @xlockrows         bit  
)  
as  
begin  
    set nocount on  
  
    declare @retcode     int,  
            @dbversion    int  
      
    --  security check for subscriber  
    exec @retcode = sys.sp_MSreplcheck_subscribe   
    if @@error <> 0 or  @retcode <> 0   
    begin  
        return 1  
    end  
  
    --  retrieve the current subscriber database version  
    exec @retcode = sys.sp_MSgetdbversion @current_version = @dbversion output   
    if @retcode <> 0 or @@error <> 0  
    begin  
        return 1  
    end  
      
    --  user wants to place an exclusive lock on the retrieved rows for   
    --  the duration of the transaction in which this SP is called...  
    if @xlockrows = 1   
    begin  
        --  note that here we order by pk to ensure lock order  
        select distinct    UPPER(originator),  
                originator_db,  
                originator_publication_id,  
                originator_db_version,  
                originator_lsn,  
                id  
            from MSpeer_lsns with (XLOCK, ROWLOCK, INDEX(nci_MSpeer_lsns))  
            where originator_publication = @publication  
                    --  and they can not be local water marks or they must be from a differnt db version  
                    and (originator             != UPPER(publishingservername())  
                        or originator_db             != db_name()  
                        or originator_db_version     != @dbversion)  
            order by id  
        if @@error <> 0  
            return 1  
    end  
    else  
    begin  
        --  same query as above without the xlock, rowlock hint and no order by  
        select distinct    UPPER(originator),  
                originator_db,  
                originator_publication_id,  
                originator_db_version,  
                originator_lsn  
            from MSpeer_lsns with (INDEX(nci_MSpeer_lsns))  
            where originator_publication = @publication  
                    --  and they can not be local water marks or they must be from a differnt db version  
                    and (originator             != UPPER(publishingservername())  
                        or originator_db             != db_name()  
                        or originator_db_version     != @dbversion)  
        if @@error <> 0  
            return 1  
    end  
  
    return 0  
end
Indsendt af kl. No comments:

sp_MSgetpeerconflictrow (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetpeerconflictrow(nvarchar @originator_id
, nvarchar @origin_datasource
, nvarchar @tran_id
, nvarchar @row_id
, nvarchar @conflict_table)

MetaData:

 create procedure sys.sp_MSgetpeerconflictrow   
(  
    @originator_id nvarchar(32) = '%' --  int  
    ,@origin_datasource nvarchar(32) = '%' -- int  
    ,@tran_id nvarchar(32) = '%' --  varbinary(16)  
    ,@row_id nvarchar(32) = '%' --  timestamp  
    ,@conflict_table nvarchar(270) --  [owner].[tabname]     
)  
as   
begin  
    set nocount on  
    declare @retcode int = 0  
            ,@cmd nvarchar(4000)   
            ,@whcmd nvarchar(4000) = N'where c.__$is_winner = 0 '  
      
    select @cmd = N'select w.__$winner_origin_datasource_srvname, w.__$winner_origin_datasource_db, ''__$origin_datasource_srvname'' = h.originator_node,  ''__$origin_datasource_db'' = h.originator_db, c.* from '   
                + ISNULL(NULLIF(QUOTENAME(PARSENAME(@conflict_table, 2)) + N'.', N'.'), N'') + QUOTENAME(PARSENAME(@conflict_table, 1))  
                + N' c join MSpeer_originatorid_history h on c.__$origin_datasource = h.originator_id'  
                + N' left join (select distinct ''__$winner_origin_datasource_srvname'' = hw.originator_node, ''__$winner_origin_datasource_db'' = hw.originator_db, cw.__$change_id, cw.__$row_id from '  
                + ISNULL(NULLIF(QUOTENAME(PARSENAME(@conflict_table, 2)) + N'.', N'.'), N'') + QUOTENAME(PARSENAME(@conflict_table, 1))  
                + N' cw join MSpeer_originatorid_history hw on cw.__$origin_datasource = hw.originator_id where cw.__$is_winner = 1) as w on '  
                + N' ((w.__$change_id is not null and w.__$change_id = c.__$row_id) or (w.__$change_id is null and w.__$row_id = c.__$change_id)) '  
                  
    --   
    --  Security Check.  
     --   
    exec @retcode = sys.sp_MSreplcheck_publish  
    if @@ERROR <> 0 or @retcode <> 0  
        return(1)  
    --   
    --  passed the security check  
    --   
  
    if (@originator_id != N'%')  
    begin  
            select @whcmd = @whcmd + N' and __$originator_id = ' + quotename(@originator_id, '''')  
    end  
    if (@origin_datasource != N'%')  
    begin  
            select @whcmd = @whcmd + N' and __$origin_datasource = ' + quotename(@origin_datasource, '''')  
    end  
    if (@tran_id != N'%')  
    begin  
            select @whcmd = @whcmd + N' and __$tranid = ' + quotename(@tran_id, '''')  
    end  
    if (@row_id != N'%')  
    begin  
            select @whcmd = @whcmd + N' and __$row_id = ' + @row_id  
    end  
    select @cmd = @cmd + @whcmd  
  
    --   
    --  execute the generated select  
    --   
    execute (@cmd)  
    --   
    --  all done  
    --   
end
Indsendt af kl. No comments:

sp_MSgetonerow (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetonerow(int @tablenick
, uniqueidentifier @rowguid
, uniqueidentifier @pubid)

MetaData:

   
create procedure sys.sp_MSgetonerow  
    (@tablenick int,  
     @rowguid uniqueidentifier,  
     @pubid uniqueidentifier = NULL)  
as  
    declare @retcode    smallint  
    declare @procname   sysname  
  
    --   
    --  Check to see if current publication has permission  
     --   
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid = @pubid, @tablenick = @tablenick  
    if (@retcode <> 0) or (@@error <> 0)  
        return 1  
      
    select @procname = 'dbo.' + select_proc from dbo.sysmergearticles where nickname = @tablenick and pubid=@pubid  
      
    exec @retcode = @procname @maxschemaguidforarticle = NULL, @type =1, @rowguid=@rowguid  
    IF @@ERROR<>0 or @retcode<>0 RETURN (1)  
    return (0)
Indsendt af kl. No comments:

sp_MSgetmetadatabatch90new (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetmetadatabatch90new(uniqueidentifier @pubid
, int @tablenick
, uniqueidentifier @rowguid1
, uniqueidentifier @rowguid2
, uniqueidentifier @rowguid3
, uniqueidentifier @rowguid4
, uniqueidentifier @rowguid5
, uniqueidentifier @rowguid6
, uniqueidentifier @rowguid7
, uniqueidentifier @rowguid8
, uniqueidentifier @rowguid9
, uniqueidentifier @rowguid10
, uniqueidentifier @rowguid11
, uniqueidentifier @rowguid12
, uniqueidentifier @rowguid13
, uniqueidentifier @rowguid14
, uniqueidentifier @rowguid15
, uniqueidentifier @rowguid16
, uniqueidentifier @rowguid17
, uniqueidentifier @rowguid18
, uniqueidentifier @rowguid19
, uniqueidentifier @rowguid20
, uniqueidentifier @rowguid21
, uniqueidentifier @rowguid22
, uniqueidentifier @rowguid23
, uniqueidentifier @rowguid24
, uniqueidentifier @rowguid25
, uniqueidentifier @rowguid26
, uniqueidentifier @rowguid27
, uniqueidentifier @rowguid28
, uniqueidentifier @rowguid29
, uniqueidentifier @rowguid30
, uniqueidentifier @rowguid31
, uniqueidentifier @rowguid32
, uniqueidentifier @rowguid33
, uniqueidentifier @rowguid34
, uniqueidentifier @rowguid35
, uniqueidentifier @rowguid36
, uniqueidentifier @rowguid37
, uniqueidentifier @rowguid38
, uniqueidentifier @rowguid39
, uniqueidentifier @rowguid40
, uniqueidentifier @rowguid41
, uniqueidentifier @rowguid42
, uniqueidentifier @rowguid43
, uniqueidentifier @rowguid44
, uniqueidentifier @rowguid45
, uniqueidentifier @rowguid46
, uniqueidentifier @rowguid47
, uniqueidentifier @rowguid48
, uniqueidentifier @rowguid49
, uniqueidentifier @rowguid50
, uniqueidentifier @rowguid51
, uniqueidentifier @rowguid52
, uniqueidentifier @rowguid53
, uniqueidentifier @rowguid54
, uniqueidentifier @rowguid55
, uniqueidentifier @rowguid56
, uniqueidentifier @rowguid57
, uniqueidentifier @rowguid58
, uniqueidentifier @rowguid59
, uniqueidentifier @rowguid60
, uniqueidentifier @rowguid61
, uniqueidentifier @rowguid62
, uniqueidentifier @rowguid63
, uniqueidentifier @rowguid64
, uniqueidentifier @rowguid65
, uniqueidentifier @rowguid66
, uniqueidentifier @rowguid67
, uniqueidentifier @rowguid68
, uniqueidentifier @rowguid69
, uniqueidentifier @rowguid70
, uniqueidentifier @rowguid71
, uniqueidentifier @rowguid72
, uniqueidentifier @rowguid73
, uniqueidentifier @rowguid74
, uniqueidentifier @rowguid75
, uniqueidentifier @rowguid76
, uniqueidentifier @rowguid77
, uniqueidentifier @rowguid78
, uniqueidentifier @rowguid79
, uniqueidentifier @rowguid80
, uniqueidentifier @rowguid81
, uniqueidentifier @rowguid82
, uniqueidentifier @rowguid83
, uniqueidentifier @rowguid84
, uniqueidentifier @rowguid85
, uniqueidentifier @rowguid86
, uniqueidentifier @rowguid87
, uniqueidentifier @rowguid88
, uniqueidentifier @rowguid89
, uniqueidentifier @rowguid90
, uniqueidentifier @rowguid91
, uniqueidentifier @rowguid92
, uniqueidentifier @rowguid93
, uniqueidentifier @rowguid94
, uniqueidentifier @rowguid95
, uniqueidentifier @rowguid96
, uniqueidentifier @rowguid97
, uniqueidentifier @rowguid98
, uniqueidentifier @rowguid99
, uniqueidentifier @rowguid100)

MetaData:

 create procedure sys.sp_MSgetmetadatabatch90new  
    (@pubid uniqueidentifier,  
     @tablenick int,  
     @rowguid1 uniqueidentifier,  
     @rowguid2 uniqueidentifier = NULL,  
     @rowguid3 uniqueidentifier = NULL,  
     @rowguid4 uniqueidentifier = NULL,  
     @rowguid5 uniqueidentifier = NULL,  
     @rowguid6 uniqueidentifier = NULL,  
     @rowguid7 uniqueidentifier = NULL,  
     @rowguid8 uniqueidentifier = NULL,  
     @rowguid9 uniqueidentifier = NULL,  
     @rowguid10 uniqueidentifier = NULL,  
     @rowguid11 uniqueidentifier = NULL,  
     @rowguid12 uniqueidentifier = NULL,  
     @rowguid13 uniqueidentifier = NULL,  
     @rowguid14 uniqueidentifier = NULL,  
     @rowguid15 uniqueidentifier = NULL,  
     @rowguid16 uniqueidentifier = NULL,  
     @rowguid17 uniqueidentifier = NULL,  
     @rowguid18 uniqueidentifier = NULL,  
     @rowguid19 uniqueidentifier = NULL,  
     @rowguid20 uniqueidentifier = NULL,  
     @rowguid21 uniqueidentifier = NULL,  
     @rowguid22 uniqueidentifier = NULL,  
     @rowguid23 uniqueidentifier = NULL,  
     @rowguid24 uniqueidentifier = NULL,  
     @rowguid25 uniqueidentifier = NULL,  
     @rowguid26 uniqueidentifier = NULL,  
     @rowguid27 uniqueidentifier = NULL,  
     @rowguid28 uniqueidentifier = NULL,  
     @rowguid29 uniqueidentifier = NULL,  
     @rowguid30 uniqueidentifier = NULL,  
     @rowguid31 uniqueidentifier = NULL,  
     @rowguid32 uniqueidentifier = NULL,  
     @rowguid33 uniqueidentifier = NULL,  
     @rowguid34 uniqueidentifier = NULL,  
     @rowguid35 uniqueidentifier = NULL,  
     @rowguid36 uniqueidentifier = NULL,  
     @rowguid37 uniqueidentifier = NULL,  
     @rowguid38 uniqueidentifier = NULL,  
     @rowguid39 uniqueidentifier = NULL,  
     @rowguid40 uniqueidentifier = NULL,  
     @rowguid41 uniqueidentifier = NULL,  
     @rowguid42 uniqueidentifier = NULL,  
     @rowguid43 uniqueidentifier = NULL,  
     @rowguid44 uniqueidentifier = NULL,  
     @rowguid45 uniqueidentifier = NULL,  
     @rowguid46 uniqueidentifier = NULL,  
     @rowguid47 uniqueidentifier = NULL,  
     @rowguid48 uniqueidentifier = NULL,  
     @rowguid49 uniqueidentifier = NULL,  
     @rowguid50 uniqueidentifier = NULL,  
     @rowguid51 uniqueidentifier = NULL,  
     @rowguid52 uniqueidentifier = NULL,  
     @rowguid53 uniqueidentifier = NULL,  
     @rowguid54 uniqueidentifier = NULL,  
     @rowguid55 uniqueidentifier = NULL,  
     @rowguid56 uniqueidentifier = NULL,  
     @rowguid57 uniqueidentifier = NULL,  
     @rowguid58 uniqueidentifier = NULL,  
     @rowguid59 uniqueidentifier = NULL,  
     @rowguid60 uniqueidentifier = NULL,  
     @rowguid61 uniqueidentifier = NULL,  
     @rowguid62 uniqueidentifier = NULL,  
     @rowguid63 uniqueidentifier = NULL,  
     @rowguid64 uniqueidentifier = NULL,  
     @rowguid65 uniqueidentifier = NULL,  
     @rowguid66 uniqueidentifier = NULL,  
     @rowguid67 uniqueidentifier = NULL,  
     @rowguid68 uniqueidentifier = NULL,  
     @rowguid69 uniqueidentifier = NULL,  
     @rowguid70 uniqueidentifier = NULL,  
     @rowguid71 uniqueidentifier = NULL,  
     @rowguid72 uniqueidentifier = NULL,  
     @rowguid73 uniqueidentifier = NULL,  
     @rowguid74 uniqueidentifier = NULL,  
     @rowguid75 uniqueidentifier = NULL,  
     @rowguid76 uniqueidentifier = NULL,  
     @rowguid77 uniqueidentifier = NULL,  
     @rowguid78 uniqueidentifier = NULL,  
     @rowguid79 uniqueidentifier = NULL,  
     @rowguid80 uniqueidentifier = NULL,  
     @rowguid81 uniqueidentifier = NULL,  
     @rowguid82 uniqueidentifier = NULL,  
     @rowguid83 uniqueidentifier = NULL,  
     @rowguid84 uniqueidentifier = NULL,  
     @rowguid85 uniqueidentifier = NULL,  
     @rowguid86 uniqueidentifier = NULL,  
     @rowguid87 uniqueidentifier = NULL,  
     @rowguid88 uniqueidentifier = NULL,  
     @rowguid89 uniqueidentifier = NULL,  
     @rowguid90 uniqueidentifier = NULL,  
     @rowguid91 uniqueidentifier = NULL,  
     @rowguid92 uniqueidentifier = NULL,  
     @rowguid93 uniqueidentifier = NULL,  
     @rowguid94 uniqueidentifier = NULL,  
     @rowguid95 uniqueidentifier = NULL,  
     @rowguid96 uniqueidentifier = NULL,  
     @rowguid97 uniqueidentifier = NULL,  
     @rowguid98 uniqueidentifier = NULL,  
     @rowguid99 uniqueidentifier = NULL,  
     @rowguid100 uniqueidentifier = NULL)  
as  
    declare @procname       nvarchar(270)  
    declare @retcode int  
  
    --  security check  
    if (1 <> {fn ISPALUSER(@pubid)})  
    begin  
        RAISERROR (14126, 11, -1)  
        return (1)  
    end  
  
    select @procname= 'dbo.' + metadata_select_proc from dbo.sysmergearticles where nickname = @tablenick and pubid=@pubid  
  
    exec @retcode = @procname  
                         @rowguid1,  
                         @rowguid2,  
                         @rowguid3,  
                         @rowguid4,  
                         @rowguid5,  
                         @rowguid6,  
                         @rowguid7,  
                         @rowguid8,  
                         @rowguid9,  
                         @rowguid10,  
                         @rowguid11,  
                         @rowguid12,  
                         @rowguid13,  
                         @rowguid14,  
                         @rowguid15,  
                         @rowguid16,  
                         @rowguid17,  
                         @rowguid18,  
                         @rowguid19,  
                         @rowguid20,  
                         @rowguid21,  
                         @rowguid22,  
                         @rowguid23,  
                         @rowguid24,  
                         @rowguid25,  
                         @rowguid26,  
                         @rowguid27,  
                         @rowguid28,  
                         @rowguid29,  
                         @rowguid30,  
                         @rowguid31,  
                         @rowguid32,  
                         @rowguid33,  
                         @rowguid34,  
                         @rowguid35,  
                         @rowguid36,  
                         @rowguid37,  
                         @rowguid38,  
                         @rowguid39,  
                         @rowguid40,  
                         @rowguid41,  
                         @rowguid42,  
                         @rowguid43,  
                         @rowguid44,  
                         @rowguid45,  
                         @rowguid46,  
                         @rowguid47,  
                         @rowguid48,  
                         @rowguid49,  
                         @rowguid50,  
                         @rowguid51,  
                         @rowguid52,  
                         @rowguid53,  
                         @rowguid54,  
                         @rowguid55,  
                         @rowguid56,  
                         @rowguid57,  
                         @rowguid58,  
                         @rowguid59,  
                         @rowguid60,  
                         @rowguid61,  
                         @rowguid62,  
                         @rowguid63,  
                         @rowguid64,  
                         @rowguid65,  
                         @rowguid66,  
                         @rowguid67,  
                         @rowguid68,  
                         @rowguid69,  
                         @rowguid70,  
                         @rowguid71,  
                         @rowguid72,  
                         @rowguid73,  
                         @rowguid74,  
                         @rowguid75,  
                         @rowguid76,  
                         @rowguid77,  
                         @rowguid78,  
                         @rowguid79,  
                         @rowguid80,  
                         @rowguid81,  
                         @rowguid82,  
                         @rowguid83,  
                         @rowguid84,  
                         @rowguid85,  
                         @rowguid86,  
                         @rowguid87,  
                         @rowguid88,  
                         @rowguid89,  
                         @rowguid90,  
                         @rowguid91,  
                         @rowguid92,  
                         @rowguid93,  
                         @rowguid94,  
                         @rowguid95,  
                         @rowguid96,  
                         @rowguid97,  
                         @rowguid98,  
                         @rowguid99,  
                         @rowguid100  
      
    if @@error <>0 or @retcode <> 0   
    begin  
        raiserror(20680, 16, -1)  
        return (1)  
    end  
  
    return 0
Indsendt af kl. No comments:

sp_MSgetmetadatabatch90 (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetmetadatabatch90(uniqueidentifier @pubid
, varbinary @tablenickarray
, varbinary @rowguidarray)

MetaData:

 create procedure sys.sp_MSgetmetadatabatch90  
(  
    @pubid uniqueidentifier,  
    @tablenickarray varbinary(2000),  
    @rowguidarray varbinary(8000)  
)  
as  
    declare @tablenick          int  
    declare @tablenicklast      int  
    declare @rowguid            uniqueidentifier  
    declare @type                       tinyint  
    declare @retcode        smallint  
    declare @tnlength       int  
    declare @tnoffset       int  
    declare @guidoffset     int  
    declare @procname       nvarchar(270)  
          
    --  security check  
    if (1 <> {fn ISPALUSER(@pubid)})  
    begin  
            RAISERROR (14126, 11, -1)  
            return (1)  
    end  
        
    set @tablenicklast = 0  
    --  initialize offsets and length for walking through arrays  
    set @tnoffset = 1  
    set @guidoffset = 1  
    set @tnlength = datalength(@tablenickarray)  
  
    --  walk through arrays and populate temp table  
    while (@tnoffset < @tnlength)  
    begin  
        set @tablenick = substring(@tablenickarray, @tnoffset, 4)  
        set @rowguid = substring(@rowguidarray, @guidoffset, 16)  
  
        if @tablenick <> @tablenicklast  
        begin  
            select @procname= 'dbo.' + select_proc from dbo.sysmergearticles where nickname = @tablenick and pubid=@pubid  
            set @tablenicklast = @tablenick  
        end  
  
        --  the following type in the select proc selects out the metadata  
        set @type= 12  
  
        --  check for row in base table  
        exec @retcode= @procname @maxschemaguidforarticle = NULL, @type=@type output, @rowguid=@rowguid  
        if @@error <>0 or @retcode <> 0   
        begin  
            return (1)  
        end  
  
        --  bump up offsets for next time through loop  
        set @tnoffset = @tnoffset + 4  
        set @guidoffset = @guidoffset + 16  
    end  
  
    return (0)
Indsendt af kl. No comments:

sp_MSgetmetadatabatch (Transact-SQL MetaData) Definition

Please note: that the following source code is provided and copyrighted by Microsoft and is for educational purpose only.
The meta data is from an SQL 2012 Server.

I have posted alot more, find the whole list here.

Goto Definition or MetaData

Definition:

sys.sp_MSgetmetadatabatch(uniqueidentifier @pubid
, varbinary @tablenickarray
, varbinary @rowguidarray
, int @compatlevel
, int @lightweight)

MetaData:

 create procedure sys.sp_MSgetmetadatabatch  
    (@pubid uniqueidentifier,  
     @tablenickarray varbinary(2000),  
     @rowguidarray varbinary(8000),  
     @compatlevel int = 10,             --  backward compatibility level, default=Sphinx  
     @lightweight int = 0)              --  if <>0, return some stuff needed to cope with lightweight subscriber  
as  
    declare @tablenick          int  
    declare @tablenicklast      int  
    declare @rowguid            uniqueidentifier  
    declare @generation         bigint   
    declare @type                       tinyint  
    declare @lineage            varbinary(311)   
    declare @colv                       varbinary(2953)  
    declare @retcode        smallint  
    declare @tnlength       int  
    declare @tnoffset       int  
    declare @guidoffset     int  
    declare @procname       nvarchar(270)  
    declare @iscoltracked   int  
    declare @maxversion         int  
    declare @cCols int  
  
    --  create temp table for returning results  
    declare @meta_batch TABLE (idx int identity unique, generation bigint, type tinyint,  
        lineage varbinary(311), colv varbinary(2953), maxversion int)  
          
    --  security check  
    exec @retcode = sys.sp_MSrepl_PAL_rolecheck @pubid=@pubid  
    if @@error <> 0 or @retcode <> 0  
            return 1  
  
    set @tablenicklast = 0  
    --  initialize offsets and length for walking through arrays  
    set @tnoffset = 1  
    set @guidoffset = 1  
    set @tnlength = datalength(@tablenickarray)  
  
  
    --  walk through arrays and populate temp table  
    while (@tnoffset < @tnlength)  
    begin  
        set @tablenick = substring(@tablenickarray, @tnoffset, 4)  
        set @rowguid = substring(@rowguidarray, @guidoffset, 16)  
  
        --  instead of calling sp_MSgetrowmetadata, look it up ourselves might be faster  
          
        --   exec @rc = sp_MSgetrowmetadata @tablenick, @rowguid,  @generation output,  
        --   @type  output, @lineage output, @colv output, @pubid  
        if @tablenick <> @tablenicklast  
            begin  
            select @procname= 'dbo.' + select_proc, @iscoltracked= column_tracking from dbo.sysmergearticles where nickname = @tablenick and pubid=@pubid  
            set @tablenicklast = @tablenick  
            end  
  
        set @type= 4  
        set @generation= 0  
        set @lineage= NULL  
        set @colv= NULL  
  
        --  check for row in base table  
        exec @retcode= @procname @maxschemaguidforarticle = NULL, @type=@type output, @rowguid=@rowguid  
        if @@error <>0 or @retcode <> 0   
        begin  
            return (1)  
        end  
  
        select @cCols = 0  
        if (@type = 3)  
        begin  
            if @compatlevel < 90  
            begin  
                set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)  
            end  
  
            --  here do a union query between contents and tombstone  
            --  The normal case here would be: Either the row is in contents or if the row  
            --  was added as part of the initial snapshot it does not have a contents row   
            --  however, in the exception case, if there is some partition movement or some   
            --  deletes happening while we do this query, the row could have been deleted  
            --  and the MSmerge_contents entry for the row will not be present.  
            --  To cover the exception case we do a union query here between contents and tombstones.  
            select @type = type, @generation = generation, @lineage = lineage, @colv = colv from  
            (  
                select 2 as type,  generation as generation,   
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,   
                       case when @compatlevel >= 90 or @iscoltracked = 0 then colv1 else {fn COLV_90_TO_80(colv1, @cCols)} end as colv  
                    from dbo.MSmerge_contents  
                    where tablenick = @tablenick and rowguid = @rowguid  
                union  
                select type as type, generation as generation,   
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,  
                       NULL as colv  
                    from dbo.MSmerge_tombstone  
                    where tablenick = @tablenick and rowguid = @rowguid  
            ) as metadataunion  
              
        end  
        else  
        begin  
            set @type= 0  
              
            if @compatlevel < 90  
            begin  
                set @cCols= sys.fn_cColvEntries_80(@pubid, @tablenick)  
            end  
              
            --  here do a union query between contents and tombstone  
            --  The normal case here would be: Either the row is in tombstones or if the row  
            --  was never present it will have no entry in tombstone.   
            --  however, in the exception case, if there is some partition movement or some   
            --  re-inserts happening while we do this query, the row could have been re-inserted  
            --  and the tombstone entry will not be present.  
            --  To cover the exception case we do a union query here between contents and tombstones.  
            --  which should be done as an atomic operation.  
            select @type = type, @generation = generation, @lineage = lineage, @colv = colv from  
            (  
                select 2 as type,  generation as generation,   
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,   
                       case when @compatlevel >= 90 or @iscoltracked = 0 then colv1 else {fn COLV_90_TO_80(colv1, @cCols)} end as colv  
                    from dbo.MSmerge_contents  
                    where tablenick = @tablenick and rowguid = @rowguid  
                union  
                select type as type, generation as generation,   
                       case when @compatlevel >= 90 then lineage else {fn LINEAGE_90_TO_80(lineage)} end as lineage,  
                       NULL as colv  
                    from dbo.MSmerge_tombstone  
                    where tablenick = @tablenick and rowguid = @rowguid  
            ) as metadataunion2  
              
        end  
   
  
        --  insert values into temp table  
        if @lightweight <> 0  
        begin  
            select @maxversion= maxversion_at_cleanup from dbo.sysmergearticles   
                    where nickname = @tablenick and pubid=@pubid  
  
            insert into @meta_batch (generation, type, lineage, colv, maxversion) values  
                (@generation, @type, @lineage, @colv, @maxversion)   
        end  
        else  
        begin  
            insert into @meta_batch (generation, type, lineage, colv) values  
                (@generation, @type, @lineage, @colv)   
        end  
  
        --  bump up offsets for next time through loop  
        set @tnoffset = @tnoffset + 4  
        set @guidoffset = @guidoffset + 16  
    end  
  
    --  select out our result set  
    if @lightweight <> 0  
    begin  
        select generation, type, lineage, colv, maxversion from @meta_batch order by idx  
    end  
    else  
    begin  
        select generation, type, lineage, colv from @meta_batch order by idx  
    end  
  
    return (0)
Indsendt af kl. No comments:
Subscribe to: Posts (Atom)

Total Pageviews