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
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.
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,
HughOkay, 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...
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
