Add column to Merge publication causes re-init without subcriberchanges

First off, we have added columns in the past and I understand that
causes a reinit, but the option is in place to upload subscriber
changes. This has worked before, we are now on CU4 and I am
suspecting something might have changed?
It is a vague error message "The merge process could not enumerate
changes at the 'Subscriber'. "
I have tried it both from the GUI and from sp_mergearticlecolumn,
neither worked.
Anyone have any ideas?
fyi, these are CE subscribers...
That's a hard one. Try setting the querytimeout to a large value and
enable logging to see where it is getting stuck. You can specify a
profile name in the sqlcereplication class.
On Nov 15, 12:25 pm, Greg J <greg.judk...@.gmail.com> wrote:
> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
|||Sounds like you are doing something wrong.
First, adding a column does not cause a new snapshot to be created. A new
table yes, a new column - no.
Use:
sp_repladdcolumn @.source_object = 'tableName'
, @.column = 'newColumn'
, @.typetext = 'dataType'
, @.publication_to_add = 'mergePubName'
, @.force_invalidate_snapshot = 0
Second - increase the QueryTimeout value in your merge agent profile.
Finally, make sure you are performing merge systable maintenance... at a
minimum you should be rebuilding indexes on these tables:
DBCC DBREINDEX (MSmerge_contents, '', 80)
DBCC DBREINDEX (MSmerge_genhistory, '', 80)
DBCC DBREINDEX (MSmerge_tombstone, '', 80)
DBCC DBREINDEX (MSmerge_current_partition_mappings, '', 80)
DBCC DBREINDEX (MSmerge_past_partition_mappings, '', 80)
ChrisB MCDBA
MSSQLConsulting.com
"Greg J" wrote:

> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
>
|||fyi... sp_repladdcolumn is SQL2000 syntax
SQL2005 - use regular DDL: ALTER TABLE myTable ADD newColumn int
ChrisB
"Greg J" wrote:

> First off, we have added columns in the past and I understand that
> causes a reinit, but the option is in place to upload subscriber
> changes. This has worked before, we are now on CU4 and I am
> suspecting something might have changed?
> It is a vague error message "The merge process could not enumerate
> changes at the 'Subscriber'. "
> I have tried it both from the GUI and from sp_mergearticlecolumn,
> neither worked.
> Anyone have any ideas?
> fyi, these are CE subscribers...
>
|||On Nov 15, 12:56 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
> fyi... sp_repladdcolumn is SQL2000 syntax
> SQL2005 - use regular DDL: ALTER TABLE myTable ADD newColumn int
> ChrisB
>
> "Greg J" wrote:
>
>
> - Show quoted text -
Yes this is 2005.
I am using the alter table followed by the sp below. We have
@.replicate_ddl turned off, we don't want all columns published.
ALTER TABLE myTable ADD
myColumn smallint NULL
GO
exec sp_mergearticlecolumn @.publication = N'myPub',
@.article = N'myTable',
@.column = N'myColumn,
@.operation = N'add',
@.force_invalidate_snapshot = 1, -- I have tried running without this
option, it does not allow it because there is already a valid
snapshot.
@.force_reinit_subscription = 1 -- I have tried this without the
option, it won't allow it b/c subscribers are already tied to it.
I'm working on getting verbose logging going now...
|||On Nov 15, 1:41 pm, Greg J <greg.judk...@.gmail.com> wrote:
> On Nov 15, 12:56 pm, Chris <Ch...@.discussions.microsoft.com> wrote:
>
>
>
>
>
>
> Yes this is 2005.
> I am using the alter table followed by the sp below. We have
> @.replicate_ddl turned off, we don't want all columns published.
> ALTER TABLE myTable ADD
> myColumn smallint NULL
> GO
> exec sp_mergearticlecolumn @.publication = N'myPub',
> @.article = N'myTable',
> @.column = N'myColumn,
> @.operation = N'add',
> @.force_invalidate_snapshot = 1, -- I have tried running without this
> option, it does not allow it because there is already a valid
> snapshot.
> @.force_reinit_subscription = 1 -- I have tried this without the
> option, it won't allow it b/c subscribers are already tied to it.
> I'm working on getting verbose logging going now...- Hide quoted text -
> - Show quoted text -
Ok, so the lesson to be learned is just do it the way Microsoft
expects you to. I turned on the @.replicate_ddl to true for the
publication and added the column. It put the column in the
publication, brought the schema to all the subscribers, uploaded all
changes, not even a need for a snapshot or re-inits from the devices.
That leaves the question why doesn't the above stored proc behave like
the @.replicate_ddl option would, but I'm ok not knowing.