May 14, 2004 at 6:44 am
Greetings,
I'm hoping someone might have a suggestion for a work around. I finished a series of scripts used to do merge replication between a server and tablets. Code is solid and works like a charm, but under limited circumstances.
There are 15 tables that are part of the snapshot which are pulled down to the tablet. Since the whole process is dynamic I did something like this:
Select @Subset_string = 'intMasterAcctID in (' + @strParams + ')'
Select @Table_Name = 'SpecialFeatureStorage'
Select @ASTR = 'exec sp_addmergearticle @publication = N''' + @DB_Name + '_DYNAMIC'', @article = N''' + @Table_Name +''', @source_owner = N''dbo'',
@source_object = N''SpecialFeatureStorage'', @type = N''table'', @description = null, @column_tracking = N''true'',
@pre_creation_cmd = N''drop'', @creation_script = null, @schema_option = 0x000000000000CFF1, @article_resolver = null,
@subset_filterclause = ''' + @Subset_String +''',
@vertical_partition = N''false'', @destination_owner = N''dbo'',
@auto_identity_range = N''false'', @verify_resolver_signature = 0, @allow_interactive_resolver = N''false'',
@fast_multicol_updateproc = N''true'', @check_permissions = 0'
exec (@ASTR)
I pass in, as parameters, @DB_Name,@Table_Name, and the provlem is @Subset_String. I apply a filter to the table so the entire thing is not pulled over, and the other 14 tables all have the same common key. When I tested the filter the first time I just choose 6 items, so the @Subset_String looked like 'intMasterAcctID in (1,2,3,4,5,6)'
Where I run into problems is when they choose too many and the @Subset_String grows to over 1000 chars in length. in sp_addmergearticle, the @subset_filterclause field only takes 1000. I could potentially have a subset filter which is ALOT larger.
Any thoughts or ideas on how I can get around this problem? There is no other common key between the tables that I can really pull off of.
Thanks,
Chris
May 14, 2004 at 7:40 am
Are the in secuence? Can you use between instead of IN?
May 14, 2004 at 9:56 am
Hi there,
Nope, between wouldn't work since the numbers could jump all over. Found the following solution:
-- Adding the article subset filter
Select @ASTR = 'exec sp_addmergefilter @publication = N''' + @DB_Name + '_DYNAMIC'', @article = N''BuildingDescriptions'',
@filtername = N''Propertys_BuildingDescriptions'',
@join_articlename = N''Propertys'',
@join_filterclause = N''BuildingDescriptions.intMasterAcctID=Propertys.intMasterAcctID'',
@join_unique_key = 0'
exec (@ASTR)
when I create the merge article for the primary table, Propertys, I just use a different subset filter which has fewer selections. The other articles have @subset_filterclause = NULL and then I do addmergefilter for all of the other tables against the Fk in each of them from Propertys. Works like a charm. Learned more about replication in the last few days than I really wanted to but it seems pretty solid now.
Chris
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply