Need to add merge article

SQL 2000 sp4 - I have an existing merge publication, I want a new table. I
assume I would do the following, however I have some questions:
I assume I call sp_addmergearticle, for the @.article param, specify the new
table.
I also assume I would need to call sp_addmergesubscription, correct?
I do the similar for transactional repl, however when I call
sp_addsubscription I can specify the new article in the @.article param, set
sync_type to automatic, run the snapshot agent and a snapshot is generated
for only the new article. My concern is I do not see an @.article param for
sp_addmergesubscription... When the snapshot agent fires, will it run for the
new article only?
Any insight would be appreciated.
Thanks,
Chris
Scratch this... all you need is sp_addmergearticle, unfortunately a snapshot
is generated for ALL articles (locking up my prod tables), however only the
added article is sent thru the merge agents...
Thanks anyway.
"Chris" wrote:

> SQL 2000 sp4 - I have an existing merge publication, I want a new table. I
> assume I would do the following, however I have some questions:
> I assume I call sp_addmergearticle, for the @.article param, specify the new
> table.
> I also assume I would need to call sp_addmergesubscription, correct?
> I do the similar for transactional repl, however when I call
> sp_addsubscription I can specify the new article in the @.article param, set
> sync_type to automatic, run the snapshot agent and a snapshot is generated
> for only the new article. My concern is I do not see an @.article param for
> sp_addmergesubscription... When the snapshot agent fires, will it run for the
> new article only?
> Any insight would be appreciated.
> Thanks,
> Chris
|||Besides sp_addmergesubscriptions, don't you also need to run a new snaphot?
Or does this get replicated on each subscriber?
David
"Chris" <Chris@.discussions.microsoft.com> wrote in message
news:E6D1BF6E-A6C5-4D21-BF5B-1529F71F18B5@.microsoft.com...
> SQL 2000 sp4 - I have an existing merge publication, I want a new table. I
> assume I would do the following, however I have some questions:
> I assume I call sp_addmergearticle, for the @.article param, specify the
> new
> table.
> I also assume I would need to call sp_addmergesubscription, correct?
> I do the similar for transactional repl, however when I call
> sp_addsubscription I can specify the new article in the @.article param,
> set
> sync_type to automatic, run the snapshot agent and a snapshot is generated
> for only the new article. My concern is I do not see an @.article param for
> sp_addmergesubscription... When the snapshot agent fires, will it run for
> the
> new article only?
> Any insight would be appreciated.
> Thanks,
> Chris

Need to add a table in merge replication

Hi,
I would like to add a new table in merge replication (200 tables already in
replication).
I followed the steps.
Step 1: In publication properties I included the new table
Step 2: System shows the following messages
One or more tables have already been published. Do you want to update those
articles with the new default?
I gave ‘NO’
Step 3: When I click Apply it shows the following message.
SQL Server Enterprise Manager could not change the properties of article
‘Table name’
Based on object ‘Table name’.
Do you want to continue saving other changes to the publication?
Error 21416: Property ‘destination_owner’ of article ‘Table Name’ cannot be
changed.
Since I have 200 tables in merge replication it asks for 200 times and I
have ‘Yes’ and finally it shows the following error message.
SQL Server Enterprise Manager could not create article ‘NewTablename’ on
object ‘NewTablename’.
Do you want to continue saving other changes to the publication?
Error 20086: Publication ‘DatabaseName’ does not support the nosync type
because it contains a table that does not have a rowguidcol column.
Finally,
I created a new column ‘rowguid’ after that followed the same steps.
Its working fine.
Need clarification:
1.Should I create a similar table in subscriber also? (else it replication
fails)
2.Am I going in a correct way?
Please advise
Thanks,
Soura.
Sometimes the GUI is not your friend. This is one of these cases. I think
what is going on is that this new table is related via DRI to some of the
other tables and the tracking metadata has to be updated. This means a new
snapshot; but for some reason - possibly a bug you are unable to do this
through the GUI.
Your options won't work. Basically you are creating two tables which are
configured somewhat correctly (but not completely) for merge replication,
but are missing the necessary merge replication metadata in the system
tables
What I would do is create a seperate publication. This could be problematic
if you have a lot of subscribers.
Another option which probably is the way to go is to use sp_addmergearticle
like this
sp_addmergearticle 'northwind2','authors','authors',
@.force_invalidate_snapshot=1
This will generate a mini snapshot with just the authors table in it.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:825361FD-DF36-4D78-9E79-6938A34A9CE2@.microsoft.com...
> Hi,
> I would like to add a new table in merge replication (200 tables already
in
> replication).
> I followed the steps.
> Step 1: In publication properties I included the new table
> Step 2: System shows the following messages
> One or more tables have already been published. Do you want to update
those
> articles with the new default?
> I gave 'NO'
> Step 3: When I click Apply it shows the following message.
> SQL Server Enterprise Manager could not change the properties of article
> 'Table name'
> Based on object 'Table name'.
> Do you want to continue saving other changes to the publication?
> Error 21416: Property 'destination_owner' of article 'Table Name' cannot
be
> changed.
> Since I have 200 tables in merge replication it asks for 200 times and I
> have 'Yes' and finally it shows the following error message.
> SQL Server Enterprise Manager could not create article 'NewTablename' on
> object 'NewTablename'.
> Do you want to continue saving other changes to the publication?
> Error 20086: Publication 'DatabaseName' does not support the nosync type
> because it contains a table that does not have a rowguidcol column.
> Finally,
> I created a new column 'rowguid' after that followed the same steps.
> Its working fine.
> Need clarification:
> 1. Should I create a similar table in subscriber also? (else it
replication
> fails)
> 2. Am I going in a correct way?
> Please advise
> Thanks,
> Soura.
>
|||Actually it seems to generate a completely new snapshot.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"SouRa" <SouRa@.discussions.microsoft.com> wrote in message
news:825361FD-DF36-4D78-9E79-6938A34A9CE2@.microsoft.com...
> Hi,
> I would like to add a new table in merge replication (200 tables already
in
> replication).
> I followed the steps.
> Step 1: In publication properties I included the new table
> Step 2: System shows the following messages
> One or more tables have already been published. Do you want to update
those
> articles with the new default?
> I gave 'NO'
> Step 3: When I click Apply it shows the following message.
> SQL Server Enterprise Manager could not change the properties of article
> 'Table name'
> Based on object 'Table name'.
> Do you want to continue saving other changes to the publication?
> Error 21416: Property 'destination_owner' of article 'Table Name' cannot
be
> changed.
> Since I have 200 tables in merge replication it asks for 200 times and I
> have 'Yes' and finally it shows the following error message.
> SQL Server Enterprise Manager could not create article 'NewTablename' on
> object 'NewTablename'.
> Do you want to continue saving other changes to the publication?
> Error 20086: Publication 'DatabaseName' does not support the nosync type
> because it contains a table that does not have a rowguidcol column.
> Finally,
> I created a new column 'rowguid' after that followed the same steps.
> Its working fine.
> Need clarification:
> 1. Should I create a similar table in subscriber also? (else it
replication
> fails)
> 2. Am I going in a correct way?
> Please advise
> Thanks,
> Soura.
>
sql