Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    1,245

    Unanswered: MSMerge_genhistory and MSMerge_replinfo (comparing int with concatenated VarBinary)

    This may be one of those, "why the h3ll is he trying to do THAT?" type questions. Please bear with me (or ignore and move on)...

    Background:
    I have a horizontally partitioned, dynamic merge publication that will be shared out with up to 60 subscribers. Things are going swimmingly, but I would like to get a more refined view of the merge update process; specifically, I am trying to query MSMerge_genhistory to see what updates have been made, which subscribers have received those updates, and what updates are pending.

    MSMerge_genhistory contains a field nicknames (Varbinary(1000) which is a concatenated value of subscribers. The "official" definition (from the SQL 2000 system table map) is

    Quote Originally Posted by SQL 2000 System Table Map
    A list of nicknames of other Subscribers that are known to already have this generation. Used to avoid sending a generation to a Subscriber that has already seen those changes. Nicknames in the nicknames list are maintained in sorted order to make searches more efficient. If there are more nicknames than can fit in this field, they will not benefit from this optimization.
    The MSMerge_replinfo table contains a field replnickname (int) which contains a unique integer value that, when cast as a Varbinary, matches the values that are in the nicknames field in MSMerge_genhistory.

    Quote Originally Posted by SQL 2000 System Table Map
    Compressed nickname for the replica.
    So, my question is: how do I compare the varbinary(1000) (with concatenated values) with the int (cast as a varbinary). Examples:

    MSMerge_Genhistory:
    Column nicknames:
    0x677112FA
    0x677112FAEAEB0704677112FA


    MSMerge_Replinfo:
    replnickname cast(replnickname as varbinary(1000))
    1735463674 0x677112FA
    -353695996 0xEAEB0704

    * Note that EAEB0704 is in the second Varbinary value from MSMerge_Genhistory, but not in the first.


    If there's a simpler way to do this (ie, a stored proc/sp_help*) that I have overlooked, please do enlighten me.

    Regards,

    Hugh
    Have you hugged your backup today?

  2. #2
    Join Date
    Dec 2002
    Posts
    1,245
    Okay, this may still fall under, "why the H would you want to do this", but here goes:

    Stolen from sp_MSAlreadyhavegeneration and modified into a udf...

    Code:
    
    CREATE FUNCTION dbo.fnCheckGeneration
    	(@genguid uniqueidentifier, @subscribernick int)
    RETURNS int
    
    as
    BEGIN
    	declare @nicknames varbinary(1000)
    	declare @offset int
    	declare @retcode int
    	
    	/*
    	** Check input parameter
    	*/
    	if (@genguid is null)
    		begin
    			-- RAISERROR(14043, 16, -1, '@genguid')
    			return (-1)
    		end
    
    	select @nicknames = nicknames from dbo.MSmerge_genhistory where guidsrc = @genguid
    	if @nicknames is null
    		begin
    			-- what is the appropriate error to return?
    			-- RAISERROR(21333, 16, -1)
    			return (-1)
    		end
    
    	set @offset = 1
    	while @offset < len(@nicknames)
    		begin
    			-- If the subscriber nickname is already in the list just return
    			if (convert(int, substring(@nicknames, @offset, 4)) = @subscribernick)
    				return (1)
    			set @offset = @offset + 4
    		end
    
    	return (0)
    END
    
    GO
    
    SET QUOTED_IDENTIFIER OFF 
    GO
    SET ANSI_NULLS ON 
    GO
    Then by calling this function inside of a query, I should be able to tell if a particular subscriber has been updated to a particular generation.

    Is it useful? Dunno yet...

    Regards,

    hmscott
    Have you hugged your backup today?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •