Addtion of Table in merge replication

Hi,
I am dealing with merge replication (104 tables/database).
I need to add some new tables in replication. There are two options
1. Stop the replication and publish all tables including new tables then
again set the replication.
2. Create a new subscription for these new tables alone
Advise me, Which one is better?
Thanks,
Soura.
SouRa,
you can just add the new table to the existing publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||This will cause an complete snapshot to be generated and distributed. A new
publication might be the answer.
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
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uCxBq%23hsFHA.1168@.TK2MSFTNGP11.phx.gbl...
> SouRa,
> you can just add the new table to the existing publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||AFAIR, although the new snapshot will include all tables, only the new table
will get distributed.
Cheers,
Paul
|||Hi,
I think u can add additional articles by using the System SP
sp_addmergearticle .
After you've added it, you run the snapshot agent. This will generate a
complete snapshot but only the new article will be propagated by the merge
agent.
If ur having the MR option as NotSync then have this table exists in
Subscriber also.
Once u tested in test server, u can implement in production.
regards,
Herbert
"SouRa" wrote:

> Hi,
> I am dealing with merge replication (104 tables/database).
> I need to add some new tables in replication. There are two options
> 1. Stop the replication and publish all tables including new tables then
> again set the replication.
> 2. Create a new subscription for these new tables alone
> Advise me, Which one is better?
> Thanks,
> Soura.
>

Additional Components?

I have a clustered SQL installed but never added the
Replication feature along with the initial setup.
Now I ned to configure replication and thought I could
just go back to the origianl setup files, modify the
installation, add the componenets I need...and I'd be
done. No such luck!
When I go through the setup diologue the option to modify
an existing installation is -not- available and I can't
figure out why.
If someone can help me out I'd sure appreciate it....
TIA,
Jay
After you install a virtual instance of SQL Server on your computer, you cannot add the SQL Server components to your virtual instance of SQL Server by running the Setup
program. To work around this problem, you must remove the virtual instance of SQL Server from your computer and then reinstall the virtual instance of SQL Server by
using a custom installation.
This is documented in Microsoft Knowledge Base article
You cannot add SQL Server components to an existing virtual instance of SQL Server by running the Setup program
http://support.microsoft.com/?kbid=867650
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx
|||Thanks Uttam!!
How is RRE life treating you...?

>--Original Message--
>After you install a virtual instance of SQL Server on
your computer, you cannot add the SQL Server components to
your virtual instance of SQL Server by running the Setup
>program. To work around this problem, you must remove
the virtual instance of SQL Server from your computer and
then reinstall the virtual instance of SQL Server by
>using a custom installation.
>This is documented in Microsoft Knowledge Base article
>You cannot add SQL Server components to an existing
virtual instance of SQL Server by running the Setup program
>http://support.microsoft.com/?kbid=867650
>Best Regards,
>Uttam Parui
>Microsoft Corporation
>This posting is provided "AS IS" with no warranties, and
confers no rights.
>Are you secure? For information about the Strategic
Technology Protection Program and to order your FREE
Security Tool Kit, please visit
>http://www.microsoft.com/security.
>Microsoft highly recommends that users with Internet
access update their Microsoft software to better protect
against viruses and security vulnerabilities. The easiest
way
>to do this is to visit the following websites:
http://www.microsoft.com/protect
>http://www.microsoft.com/security/guidance/default.mspx
>
>.
>
|||You are most welcome.
Life is good -- learning something new everyday.
Best Regards,
Uttam Parui
Microsoft Corporation
This posting is provided "AS IS" with no warranties, and confers no rights.
Are you secure? For information about the Strategic Technology Protection Program and to order your FREE Security Tool Kit, please visit
http://www.microsoft.com/security.
Microsoft highly recommends that users with Internet access update their Microsoft software to better protect against viruses and security vulnerabilities. The easiest way
to do this is to visit the following websites: http://www.microsoft.com/protect
http://www.microsoft.com/security/guidance/default.mspx

Addition of article in Runnung MERGE Replication

Can any help me addition of article in runnning merge replication in MSSQL SERVER 2000 with SP3.
Any step by step help is realy great for me.
Thanks
R.MallSp_addMergeArticle is one system procedure, which may be workable, but I don't know detail step by step methods for same.

Adding table/article w/o starting snapshot!

Before running the snapshot agent, run
sp_refreshsubscriptions 'publicationname'
Cheers,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thanks for ur response, but I still get the same error.
"A snapshot was not generated b/c no subscription needed intialization".
Regards
Naveed.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:1aa701c4aa03$e43f6440$a501280a@.phx.gbl...
> Before running the snapshot agent, run
> sp_refreshsubscriptions 'publicationname'
> Cheers,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Naveed,
was the initial subscription a noinit one?
It looks like it was, in which case you'll have to
manually add the scripts
(http://support.microsoft.com/default.aspx?scid=kb;EN-
US;299903), and DTS the table. Alternatively you can
publish the table in a separate publication.
Rgds,
Paul Ibison[vbcol=seagreen]
|||Hello Paul;
I think if I reintialize the subscription then snapshot will start
publishing all articles instead of 1 article.
and I dont want that.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:3fb201c4aa11$91875d30$a601280a@.phx.gbl...
> Naveed,
> was the initial subscription a noinit one?
> It looks like it was, in which case you'll have to
> manually add the scripts
> (http://support.microsoft.com/default.aspx?scid=kb;EN-
> US;299903), and DTS the table. Alternatively you can
> publish the table in a separate publication.
> Rgds,
> Paul Ibison
>
|||Naveed,
to recap - from your other posts you mention that running the snapshot agent
doesn't do anything. After adding a new article and refreshing the
subscription, still the snapshot agent doesn't do anything. My assumption is
that the initial setup was a noinit one, in which case no snapshot of a new
article is produced. The new article is a part of the publication and (I'm
not suggesting you try this) if you update a record in this article on the
publisher there should be an error in the distribution agent which complains
about the absence of an update stored procedure onthe subscriber. Now you
have a choice -
(a) set up these stored procedures by hand using
sp_scriptpublicationcustomprocs and transfer the table by hand. Be sure to
remove the identity attribute if there is one.
(b) reinitialize.
(c) add the new article to a separate publication. This can cause integrity
problems if the article is related to other articles in the first
publication.
I have suggested (a), which won't result in a new snapshot of all articles
being produced.
HTH,
Paul Ibison (MVP)
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Hello Paul,
I'm using Transactional Replication and it's Push subscription.
Can u plz give me the complete steps to add a single table
w/o starting snapshot for all tables.
And another question,
BOL says sp_refreshsubscription for Pull and
sp_reinitsubscription is for Push Subscription.
But I saw newgroup an it says BOL is wrong sp_refreshsubscription works for
both.
What is the difference b/w sp_refreshsubscription and
sp_reinitsubscription?
Thanks in advance.
Naveed.
|||Naveed,
I don't have a complete script to hand, but this is the basic pattern I have
followed:
On the publisher:
EXEC sp_addarticle
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.source_owner = N'dbo',
@.source_object = N'Categories',
@.destination_table = N'Categories',
@.type = N'logbased',
@.creation_script = null, @.description = null,
@.pre_creation_cmd = N'drop',
@.schema_option = 0x0000000000000073,
@.status = 16,
@.vertical_partition = N'false',
@.ins_cmd = N'CALL sp_MSins_Categories',
@.del_cmd = N'CALL sp_MSdel_Categories',
@.upd_cmd = N'MCALL sp_MSupd_Categories'
GO
exec sp_addsubscription
@.publication = N'NorthwindOIncludeDRINonCLustered',
@.article = N'CategoriesArticle',
@.subscriber = N'HOME-WIN2K',
@.destination_db = N'Pubs',
@.sync_type = N'none',
@.update_mode = N'read only'
GO
sp_refreshsubscriptions
run sp_scriptpublicationcustomprocs to generate the necessary procs and
apply them to the subscriber.
(BTW, I have also found that sp_refreshsubscription works for push and
pull).
HTH,
Paul Ibison

Adding table to merge replication - What is result of Snapshot?

I would like to add a table to an existing merge replication. The DB is
approximately 11GB in size. When I proceed through the steps to add the
table using Enterprise Manager, SQL Server 2000 responds as follows:
"After adding a new merge article, you must generate a new snapshot before
changes from any subscription can be merged."
"Although a snapshot of all articles must be generated, only the snapshot of
the new article will be used to synchronize existing subscriptions."
I remember generating an new snapshot for this DB... it took a Loooonnnnggg
time. When SQL2000 says it must generate a snapshot, does it mean for the
entire DB or just the new article?
I thought one of the features of SQL Server 2000 was the ability to add
articles "on the fly" without disturbing what exists.? It is NOT good if I
have to, first, generate a snapshot of the ENTIRE DB... AGAIN! This is not
the way I understand the documentation.
Someone please clarify.?
Brent
brent.erb@.philips.com
IIRC, just a snapshot for the new article will be generated, and distributed
along with metadata for this article.
"Berb" <berb1969@.earthlink.net> wrote in message
news:826193B1-ED96-4D11-B66D-81C4E17D80CF@.microsoft.com...
>I would like to add a table to an existing merge replication. The DB is
> approximately 11GB in size. When I proceed through the steps to add the
> table using Enterprise Manager, SQL Server 2000 responds as follows:
> "After adding a new merge article, you must generate a new snapshot before
> changes from any subscription can be merged."
> "Although a snapshot of all articles must be generated, only the snapshot
> of
> the new article will be used to synchronize existing subscriptions."
> I remember generating an new snapshot for this DB... it took a
> Loooonnnnggg
> time. When SQL2000 says it must generate a snapshot, does it mean for the
> entire DB or just the new article?
> I thought one of the features of SQL Server 2000 was the ability to add
> articles "on the fly" without disturbing what exists.? It is NOT good if
> I
> have to, first, generate a snapshot of the ENTIRE DB... AGAIN! This is
> not
> the way I understand the documentation.
> Someone please clarify.?
>
> Brent
> brent.erb@.philips.com
>

Adding one more subscriber

Hi All
I have a transactional replication with one publisher/distributor and one
subscriber. I want to add one more subscriber for another location. What is
the best method to add one more subscriber? Expect expert's suggestions.
Regards,
Aris
Make sure the new subscriber is enabled (use Tools, Replication, Configure
Distributor, Publishers and Subscribers).
If it is indistinguishable from the other subscriber simple script out your
publication, note the sp_addsubscription statement at the bottom, copy this
and paste it into your publication database on your publisher. Edit this
statement for the new subscriber.
Run the script.
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
"Aris" <Aris@.discussions.microsoft.com> wrote in message
news:86D98B89-5F43-42F1-BF4B-BE9422F8827C@.microsoft.com...
> Hi All
> I have a transactional replication with one publisher/distributor and one
> subscriber. I want to add one more subscriber for another location. What
> is
> the best method to add one more subscriber? Expect expert's suggestions.
> Regards,
> Aris
>

Adding new subscribers to a publisher

We are using Merge Replication on SQL 2000
The snapshot on our publisher has a 180 day retention period. We are able
to add new subscribers just fine within that 180 days, but it seems that come
day 181 when we add a new subscriber it is missing a bunch of schema
information that it should be getting from the snapshot.
All the MS documentation on the retention setting points to keeping current
subscribers active and says nothing about are correlation to new subscribers.
I was wondering if there is relation between the retention setting and when
you can add new subscribers. Does anyone know? Thanks!
I got some more info yesterday from one of the logs. Turns out that the
snapshot is being marked as obsolete. From what I've read the only way a
snapshot can be marked as obsolete is by running one of the following:
sp_addmergearticle
sp_changemergearticle
sp_mergecleanupmetadata
We have not run any of these stored procedures, nor have we modified the
schema in any other way. So at this point it looks like the snapshot is
being marked as obsolete as soon as the retention period has passed. Does
this sound right?
"Paul Ibison" wrote:

> Ben,
> are you saying that a new snapshot doesn't have the same schema as the
> existing subscribers? What things are not being snapshotted, and is the
> snapshot definitely recent?
> Cheers,
> Paul Ibison
>
>
|||I just want to make sure I'm understanding this right. So if our retention
period is set to the default of 14 days and we set up a new snapshot on
7/1/07 then we have to make sure we get all of our subscribers added to the
publisher by 7/14/07 since we cannot add any new subscribers after 7/15/07.
Is this right?
The problem is that we will continuously be adding new subscribers one week
from now, one month from now and a couple years from now. So is our only
option then to lengthen the retention period to something like 1800 days
(around 5 years) and deal with the fact that our database will be storing a
ton of metadata?
Thanks,
Ben
"Paul Ibison" wrote:

> This sounds fine to me - once the retention period is reached, the required
> changes aren't there to make the snapshot data upto date.
> Cheers,
> Paul Ibison
>
>
|||Thanks Paul,
Our experience with running the snapshot agent is that all current
subscribers have to be resyncronized which with the publisher which is a lot
of work when you have a bunch of subscribers. Maybe we are not utilizing
something that would allow this to task to be less of a headache. If you
know of any documentation that may help us in this area it would be much
appreciated. Thanks!
-Ben Fyvie
"Paul Ibison" wrote:

> Correct ... or run the snapshot agent more frequently.
> HTH,
> Paul Ibison
>
>

Adding new index to existing table in Merge Replication

I see from other posts that sp_addscriptexec is recommended for
updating/creating indexes. It isn't clear to me if I can use Enterprise
Manager to go into the table on the publisher and add additional indexes on
a table and then have the added indexes automatically populate to the
subscribers.
Additionally, BOL says that an index can contain upto 16 fields but they
recommend upto three. Does that mean I should create many indexes with only
upto three fields in each index? Help or references on this matter would
be greatly appreciated.
WB
The index can be added at the publisher as per usual. It won't get
automatically propagated to the subscribers and that is what
sp_addscriptexec is for. If I just have one or two subscribers then I tend
to create the index by hand, but if you have lots, then it is a way or being
sure they all receive the changes, especially if they are pull
subscriptions.
As for the size, this seems a general question to do with indexes. Have a
look for clustered, nonclustered and covering in BOL. Indexes are sorthed on
the first column, then for duplicates of the first column they are sorted on
the second and so on, so 2 indexes with 3 columns is not the same as one
index with 6 columns. An index with 6 columns will be 'wide' and therefore
slow, but on the other hand it might have been created as a covering index
for a particluar long-running query. This is a really big subject and I'm
sure there are loads of resources on it apart from BOL.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com/default.asp
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding new field in a table which being used for replication

Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.
If you really need the new column at a specific position, you'll have to drop the subscription, drop the publication, add the column then recreate the publication and subscription and initialize. You might want to do a nosync initialization to avoid the c
ost of the snapshot, but in this case you'll have to add the column onto the subscriber(s) table manually and if you are doing transactional replication, you'll need to create the scripts and apply them manually.
(No doubt you've seen this in other threads, but referring to columns by position rather than by name is generally seen as being a restrictive practice.)
HTH,
Paul Ibison
|||Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.
|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison

Adding new field in a table which being used for replication

Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.If you really need the new column at a specific position, you'll have to dro
p the subscription, drop the publication, add the column then recreate the p
ublication and subscription and initialize. You might want to do a nosync in
itialization to avoid the c
ost of the snapshot, but in this case you'll have to add the column onto the
subscriber(s) table manually and if you are doing transactional replication
, you'll need to create the scripts and apply them manually.
(No doubt you've seen this in other threads, but referring to columns by pos
ition rather than by name is generally seen as being a restrictive practice.
)
HTH,
Paul Ibison|||Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx
.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison

Adding new field in a table which being used for replication

Hi dear friends,
I'm trying to add a new field in a table and in my desired
position (OrdinalPosition), but I can not and I get the
following error message. As this table is involved in
replication I get this error message:
('tblTransPayments' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
Cannot drop the table 'dbo.tblTransPayments' because it is
being used for replication.)
It should be noticed that I have lots of data in this
table and I'm not going to drop the table.
I can easily add my new field at the end of the column's
list but not as 21th column which I want to.
Thanks in advance.Hi -
Please check SQL BOL for "sp_repladdcolumn" and "sp_repldropcolumn".
Thanks
-Surajit
"Mattew" <anonymous@.discussions.microsoft.com> wrote in message
news:19d4101c44d7b$6507c260$a101280a@.phx.gbl...
> Hi dear friends,
> I'm trying to add a new field in a table and in my desired
> position (OrdinalPosition), but I can not and I get the
> following error message. As this table is involved in
> replication I get this error message:
> ('tblTransPayments' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]
> Cannot drop the table 'dbo.tblTransPayments' because it is
> being used for replication.)
> It should be noticed that I have lots of data in this
> table and I'm not going to drop the table.
> I can easily add my new field at the end of the column's
> list but not as 21th column which I want to.
> Thanks in advance.|||Surajit,
this won't give the ability to specify the position, and Matthew doesn't
want the column created at the end.
Regards,
Paul Ibison

Adding New Column & updating bulk data in merge replication

Hi,
We have about 50 databases (SQL Server 2000, SP3) which are merge
replicated. We merge replicate about 100 odd tables in each of these
database. We need to add couple of columns in one of our major transaction
table where most insert/updates are being done. This table presently on
average has 5 lacs records.
During testing, we noticed that it takes about 60-80 minutes to add a
column in this table. Considering the # of database we have where the change
need to implemented, we will not be able to plan the upgrade without
production downtime. For upgrade 50 database it will take about 50 hours.
What are the options available in Replication so this can be done quickly
w/o any production downtime.
Adding to this, in one of the column we have added to the transaction table
, we need to update a new value. On testing we found that for 5 lacs records
it takes anyway between 2-3 hours. This takes roughly another 75 hours for
us to do this update after adding the new column in the table. How can this
be speeded up ?
thanks,
Soura
What's a lacs?
Basically there is probably no good solution for this. I would look at doing
a sync. Then creating my publication and then backing it up and restoring it
to all the subscribers and doing a no sync subscription, or I would look at
regenerating a snapshot and distributing it after you have made the column
change.
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:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
> Hi,
> We have about 50 databases (SQL Server 2000, SP3) which are merge
> replicated. We merge replicate about 100 odd tables in each of these
> database. We need to add couple of columns in one of our major transaction
> table where most insert/updates are being done. This table presently on
> average has 5 lacs records.
> During testing, we noticed that it takes about 60-80 minutes to add a
> column in this table. Considering the # of database we have where the
> change
> need to implemented, we will not be able to plan the upgrade without
> production downtime. For upgrade 50 database it will take about 50 hours.
> What are the options available in Replication so this can be done quickly
> w/o any production downtime.
> Adding to this, in one of the column we have added to the transaction
> table
> , we need to update a new value. On testing we found that for 5 lacs
> records
> it takes anyway between 2-3 hours. This takes roughly another 75 hours for
> us to do this update after adding the new column in the table. How can
> this
> be speeded up ?
> thanks,
> Soura
|||5 lacs is 500 K or 500 thousand i.e 500,000
lacs is primarily an indian unit of measurment. 1 lac is 0.1 million
"Hilary Cotter" wrote:

> What's a lacs?
> Basically there is probably no good solution for this. I would look at doing
> a sync. Then creating my publication and then backing it up and restoring it
> to all the subscribers and doing a no sync subscription, or I would look at
> regenerating a snapshot and distributing it after you have made the column
> change.
> --
> 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:A19172BF-E8AC-4BB7-9FED-87EEC5353D89@.microsoft.com...
>
>

Adding more keys in a table

Hi All,
I'm running merge replication.
I want to add some keys for existing fields in a replicated table (composite
keys). Is it possible? How to do that?
Thanks
use sp_repladdcolumn for this. When I hear the term composite keys I think
a primary key consisting of more than one column. Replication normally
complains when you change a PK.
"echo" <echo@.discussions.microsoft.com> wrote in message
news:462ECC14-6DF7-4D12-9B46-3744B388B563@.microsoft.com...
> Hi All,
> I'm running merge replication.
> I want to add some keys for existing fields in a replicated table
(composite
> keys). Is it possible? How to do that?
> Thanks
|||Hi Hilary,
That's why. The column is already exist. I just want to change/modify it as
(another) primary key.
Should I do create dummy column --> copy the contents --> drop the column
--> create the new one --> copy from dummy?
The replication runs in continues mode. Will it stops immediately when I do
these changes? What should I do next?
TIA
Echo
btw do you have merge replication book? how can I get it? thanks )
"Hilary Cotter" wrote:

> use sp_repladdcolumn for this. When I hear the term composite keys I think
> a primary key consisting of more than one column. Replication normally
> complains when you change a PK.
> "echo" <echo@.discussions.microsoft.com> wrote in message
> news:462ECC14-6DF7-4D12-9B46-3744B388B563@.microsoft.com...
> (composite
>
>

adding independent agents on publication

Hi,
I have set up replication but i want to use independent agent for some other
tables
according to one of the post , i shld right-click properties of the
publication , go to Sunscription Options
however, it's disabled
i am using SQL Server 2000 sp3a , Std Ed
kindly advise how i can get it enabled
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1
If you are using independent agents ensure that you group the articles into
different publications which are related by DRI. Also make sure you group
them according to application dependency as well. So if you have a stored
procedure which is referencing a group of tables all of these tables should
be in the same publication.
To fix your particular problem, open up QA and do the following.
You need to do the following
1) script out the subscriptions
2) drop the subscriptions
3) in QA in the publication database issue the following
sp_changepublication 'MyPublicationName','independent_agent','true'
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f1ef11076f5@.uwe...
> Hi,
> I have set up replication but i want to use independent agent for some
> other
> tables
> according to one of the post , i shld right-click properties of the
> publication , go to Sunscription Options
> however, it's disabled
> i am using SQL Server 2000 sp3a , Std Ed
> kindly advise how i can get it enabled
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forums...erver/200601/1
|||tks i'll try out what you have advised
Hilary Cotter wrote:[vbcol=seagreen]
>If you are using independent agents ensure that you group the articles into
>different publications which are related by DRI. Also make sure you group
>them according to application dependency as well. So if you have a stored
>procedure which is referencing a group of tables all of these tables should
>be in the same publication.
>To fix your particular problem, open up QA and do the following.
>You need to do the following
>1) script out the subscriptions
>2) drop the subscriptions
>3) in QA in the publication database issue the following
>sp_changepublication 'MyPublicationName','independent_agent','true'
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forums...erver/200601/1

adding independent agents on publication

Hi,
I have set up replication but i want to use independent agent for some other
tables
according to one of the post , i shld right-click properties of the
publication , go to Sunscription Options
however, it's disabled
i am using SQL Server 2000 sp3a , Std Ed
kindly advise how i can get it enabled
tks & rdgs
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1If you are using independent agents ensure that you group the articles into
different publications which are related by DRI. Also make sure you group
them according to application dependency as well. So if you have a stored
procedure which is referencing a group of tables all of these tables should
be in the same publication.
To fix your particular problem, open up QA and do the following.
You need to do the following
1) script out the subscriptions
2) drop the subscriptions
3) in QA in the publication database issue the following
sp_changepublication 'MyPublicationName','independent_agent',
'true'
--
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
"maxzsim via droptable.com" <u14644@.uwe> wrote in message
news:59f1ef11076f5@.uwe...
> Hi,
> I have set up replication but i want to use independent agent for some
> other
> tables
> according to one of the post , i shld right-click properties of the
> publication , go to Sunscription Options
> however, it's disabled
> i am using SQL Server 2000 sp3a , Std Ed
> kindly advise how i can get it enabled
> tks & rdgs
> --
> Message posted via droptable.com
> http://www.droptable.com/Uwe/Forum...server/200601/1|||tks i'll try out what you have advised
Hilary Cotter wrote:[vbcol=seagreen]
>If you are using independent agents ensure that you group the articles into
>different publications which are related by DRI. Also make sure you group
>them according to application dependency as well. So if you have a stored
>procedure which is referencing a group of tables all of these tables should
>be in the same publication.
>To fix your particular problem, open up QA and do the following.
>You need to do the following
>1) script out the subscriptions
>2) drop the subscriptions
>3) in QA in the publication database issue the following
>sp_changepublication 'MyPublicationName','independent_agent',
'true'
>[quoted text clipped - 12 lines]
Message posted via droptable.com
http://www.droptable.com/Uwe/Forum...server/200601/1

adding independent agents on publication

Hi,
I have set up replication but i want to use independent agent for some other
tables
according to one of the post , i shld right-click properties of the
publication , go to Sunscription Options
however, it's disabled
i am using SQL Server 2000 sp3a , Std Ed
kindly advise how i can get it enabled
tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1If you are using independent agents ensure that you group the articles into
different publications which are related by DRI. Also make sure you group
them according to application dependency as well. So if you have a stored
procedure which is referencing a group of tables all of these tables should
be in the same publication.
To fix your particular problem, open up QA and do the following.
You need to do the following
1) script out the subscriptions
2) drop the subscriptions
3) in QA in the publication database issue the following
sp_changepublication 'MyPublicationName','independent_agent','true'
--
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
"maxzsim via SQLMonster.com" <u14644@.uwe> wrote in message
news:59f1ef11076f5@.uwe...
> Hi,
> I have set up replication but i want to use independent agent for some
> other
> tables
> according to one of the post , i shld right-click properties of the
> publication , go to Sunscription Options
> however, it's disabled
> i am using SQL Server 2000 sp3a , Std Ed
> kindly advise how i can get it enabled
> tks & rdgs
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1|||tks i'll try out what you have advised
Hilary Cotter wrote:
>If you are using independent agents ensure that you group the articles into
>different publications which are related by DRI. Also make sure you group
>them according to application dependency as well. So if you have a stored
>procedure which is referencing a group of tables all of these tables should
>be in the same publication.
>To fix your particular problem, open up QA and do the following.
>You need to do the following
>1) script out the subscriptions
>2) drop the subscriptions
>3) in QA in the publication database issue the following
>sp_changepublication 'MyPublicationName','independent_agent','true'
>> Hi,
>[quoted text clipped - 12 lines]
>> tks & rdgs
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server/200601/1

Adding data from disconnected subscriber

Abdul,
what type of replication are you using?
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
Thank you Paul, I'm using merge replication
"Paul Ibison" wrote:

> Abdul,
> what type of replication are you using?
> Rgds,
> Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Abdul,
in your first post you mention "Then I recreate the subscription but I find
that the record does not replicate on the other subscriber and publisher. ".
Merge replication is not normally continuous, rather the merge agent is run
on a schedule. You could have a schedule of once a minute and enter records
on the subscriber between synchronizations. To simulate a broken connection,
you could disconnect the network card, or set one of the databases to
read-only, then synchronize. Dropping the subscription as you have done is
something else entirely and is a simulation of a subscriber exceeding the
maximum retention period. In this case you could run sp_removedbreplication,
resubscribe as a noinit subscription and do a dummy update. However, I think
that this isn't what you really require.
Rgds,
Paul Ibison, SQL Server MVP, WWW.Replicationanswers.Com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Adding columns during replication

Is there any way to add columns to the database and not have to stop
replication.
I have one publisher with many subscribers and I don't want to delete all
the subscriptions to add columns to my publisher.
If this is not the correct way to add columns could you please explain how I
should do it correctly.
Thank you
Abdul,
have a look at sp_repladdcolumn.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Paul, as always thank you for your help
"Paul Ibison" wrote:

> Abdul,
> have a look at sp_repladdcolumn.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>

Adding column to table

Just wanted to check. Running merge replication on SQL 2000. If I add a
column to a table that is a published article, I assume I just run
sp_repladdcolumn is that correct and is that all I need to do in order for
the laptops to get the change with their next synch? Also, what do I set
the value for @.force_reinit_subscription to? Thanks.
David
Yes. However I believe you need to set @.force_reinit_subscription and
@.force_invalidate_snapshot to 1.
Basically these switches will stop your script from working if they are set
to their defaults of 0. If you set them to 1 your scripts will run but you
may have to reinitialize your subscriptions.
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
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23IOG4PPOHHA.3872@.TK2MSFTNGP06.phx.gbl...
> Just wanted to check. Running merge replication on SQL 2000. If I add a
> column to a table that is a published article, I assume I just run
> sp_repladdcolumn is that correct and is that all I need to do in order for
> the laptops to get the change with their next synch? Also, what do I set
> the value for @.force_reinit_subscription to? Thanks.
> David
>

Adding column to article on *susbcriber* side

Hi,
I've got a unidirectional transactional replication (on sql 2000) and
I want to add a column to a table on the subscriber's side, the column
allows nulls and is going to be updated on the subscriber.
I thought of creating the table, than adding the publication and when
I add this specific article, tell it to truncate the table if it
exists rather than to drop it.
Is there any flaw in this?
Thanks in advance,
R. Green
It should work. This is the normal way of carrying out what you are trying
to accomplish.
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
"Ronald Green" <zzzbla@.gmail.com> wrote in message
news:1172407317.380240.255030@.t69g2000cwt.googlegr oups.com...
> Hi,
> I've got a unidirectional transactional replication (on sql 2000) and
> I want to add a column to a table on the subscriber's side, the column
> allows nulls and is going to be updated on the subscriber.
> I thought of creating the table, than adding the publication and when
> I add this specific article, tell it to truncate the table if it
> exists rather than to drop it.
> Is there any flaw in this?
> Thanks in advance,
> R. Green
>
|||Thanks a lot!
On Feb 25, 3:01 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> It should work. This is the normal way of carrying out what you are trying
> to accomplish.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Ronald Green" <zzz...@.gmail.com> wrote in message
> news:1172407317.380240.255030@.t69g2000cwt.googlegr oups.com...
>
>
>
>
> - Show quoted text -
|||Hi,
I ran a little test, and it failed on delivering the snapshot because
of that extra column on the subscriber side. So the moral of the story
is to add the column in a post snapshot script which is not always the
best idea (if you already have your schema deployed and something is
dependant on this column), OR you can change the SYNC view on the
publisher and add a blank column to it prior to running the snapshot
agent.
R. Green
On Feb 25, 3:01 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
> It should work. This is the normal way of carrying out what you are trying
> to accomplish.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTShttp://www.indexserverfaq.com
> "Ronald Green" <zzz...@.gmail.com> wrote in message
> news:1172407317.380240.255030@.t69g2000cwt.googlegr oups.com...
>
>
>
>
> - Show quoted text -
|||Arghhh!!!!!!!!!!!! Somehow I was assuming you would be doing a no-sync.
Yes, this could be accomplished via a post snapshot script.
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
"Ronald Green" <zzzbla@.gmail.com> wrote in message
news:1172412186.310429.11430@.k78g2000cwa.googlegro ups.com...
> Hi,
> I ran a little test, and it failed on delivering the snapshot because
> of that extra column on the subscriber side. So the moral of the story
> is to add the column in a post snapshot script which is not always the
> best idea (if you already have your schema deployed and something is
> dependant on this column), OR you can change the SYNC view on the
> publisher and add a blank column to it prior to running the snapshot
> agent.
> R. Green
>
> On Feb 25, 3:01 pm, "Hilary Cotter" <hilary.cot...@.gmail.com> wrote:
>