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

Need Query For 'Enforce relationship fo replication'

Hi,
I am dealing with merge replication.
In enterprise manager --> design --> relationship - there is an option
'Enforce relationship for replication'.
Is it possible to identify the status (whether checked/unchecked) of this
option for a particular table through query? I need to identify this for all
the tables in the database.
Please advise
Thanks,
Soura
Soura,
I keep a list of this type of query at
http://www.replicationanswers.com/Scripts.asp. For your needs, I think this
should be it...
select table_name, constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
where constraint_type = 'foreign key'
and objectproperty(object_id(constraint_name),'CnstIsN otRepl') = 0
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thank you Paul its working fine.
Thanks,
Soura
"Paul Ibison" wrote:

> Soura,
> I keep a list of this type of query at
> http://www.replicationanswers.com/Scripts.asp. For your needs, I think this
> should be it...
> select table_name, constraint_name from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
> where constraint_type = 'foreign key'
> and objectproperty(object_id(constraint_name),'CnstIsN otRepl') = 0
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
>

Need Optimal Schedule for Snapshot and Merge Replications

I need to schedule a snapshot replication of some tables and a merge
replication of a couple other tables. I would like to know the best
way to schedule the various replication agents.
My questions are:
- Should I arrange the schedule in this order:
Step 1. Run Snapshot Agent for snapshot replication
Step 2. Run Distribution Agents: one for each branch office
Step 3. Run Snapshot Agent for merge replication
Step 4. Run Merge Agents: one for each branch office
The reason I ask is that I am under the impression that
Distribution Agent has everything to do with snapshot
replication and has nothing to do with merge replication.
Therefore, I may want to run Distribution Agent right
after Snapshot Agent for snapshot replication -- just to
group related tasks together and out of the way.
Is my understanding correct?
What is the correct order anyway?
- I have one Distribution Agent for each branch office.
Likewise, I have one Merge Agent for each branch office.
I have two branch offices; this means I have two
Distribution Agents and two Merge Agents.
Should I start the two Distribution Agents at the same time?
Should I start the two Merge Agents at the same time?
Of course, I could have separate those two Distribution
Agents in two different time slots. But I don't want to do
this in order to avoid keeping track with two different sets
of schedules.
I have a feeling that I should be able to run those two agents
at the same time because the bottleneck is the T1 line between
the central office and each branch office. Is my understanding
correct?
Thanks in advance for any info.
Jay Chan
In general you can run all agents simultaneously if you have a small number of subscribers.
You can leave the distribution and merge agents running continoulsy and they will detect the new snapshot is available for distribution and will distribute it.
Many DBAs like to
1)run their distribution and merge agents in continuous loops
2) or schedule them to restart every 10 minutes or so and have them run continoss
This makes them more resilitent to failure.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

NEED HELP! SQL Merge Replication

I have a merge replication process that is having some major performance issues. The replication process seems to run just fine for about a half hour (200k records), and then it hits a brick wall. I continually get the message:
"The process is running and is waiting for a response from one of the backend connections"
Once I get this message, the process will then go through 4 more batch downloads (2000 records per batch), and then give me that message 2 or 3 more times and then continue to download. This appears to only happen on a specific table. I have exhausted m
y thoughts on this and would really appreciate ANY help.
The subscriber is running MSDE 2000 on a pentium 3 1.7GHtz machine with 1GB of RAM.
Thank you,
Thanks!
Tony D
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
Set QueryTimeout to a larger value. You might want to post replication
related questions to microsoft.public.sqlserver.replication
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tony DiGiorgio" <tony.digiorgio@.immedient.com> wrote in message
news:Okua7om$EHA.2568@.TK2MSFTNGP11.phx.gbl...
>I have a merge replication process that is having some major performance
>issues. The replication process seems to run just fine for about a half
>hour (200k records), and then it hits a brick wall. I continually get the
>message:
> "The process is running and is waiting for a response from one of the
> backend connections"
> Once I get this message, the process will then go through 4 more batch
> downloads (2000 records per batch), and then give me that message 2 or 3
> more times and then continue to download. This appears to only happen on
> a specific table. I have exhausted my thoughts on this and would really
> appreciate ANY help.
> The subscriber is running MSDE 2000 on a pentium 3 1.7GHtz machine with
> 1GB of RAM.
> Thank you,
> Thanks!
> Tony D
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP &
> ASP.NET resources...