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 fields

Win 2K & SQL 2K
I have several SQL servers replicating to one server and
I need to add a field to a replicated table can I....
Add the field to the subscriber, then via the Properties-
>Filter Columns->Add Column to Table add the same field
to the publisher?
Will this cause a problem with the other publishers when
they attempt to replicate and there is an addition al
field in the destination table, but has not been added on
all the publishers?
Logically, this seems like it will work. I really do not
want to drop all the publications, alter the tables, then
recreate all the publications with no sync.
HELP!!!
the preferred way to add a column to articles in an existing publication is
to use sp_repladdcolumn.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"larry" <anonymous@.discussions.microsoft.com> wrote in message
news:757d01c4765b$2188ee20$a401280a@.phx.gbl...
> Win 2K & SQL 2K
> I have several SQL servers replicating to one server and
> I need to add a field to a replicated table can I....
> Add the field to the subscriber, then via the Properties-
> to the publisher?
> Will this cause a problem with the other publishers when
> they attempt to replicate and there is an addition al
> field in the destination table, but has not been added on
> all the publishers?
> Logically, this seems like it will work. I really do not
> want to drop all the publications, alter the tables, then
> recreate all the publications with no sync.
> HELP!!!

Adding column in replicated table with nosync initialization

I'm not sure how can I replicate the table which is part of nosync
transactional replication? I have to add a column in that table and to
replicate again. I'm using no-sync initialization?
Thanks,
BaniSQL
I want to add some additional explainations regarding this issue. I'm
currently running transactional replication with nosync initialization. In
one of the articles - replicating table I want to add 2 additional fields for
future usage. Some good explainations exists in the www.replicationanwers.com
website, but I'm not so sure what I have exactly to do? Removing the table
from replication, adding 2 additional fileds ... thereafter do I have to run
all the process from the beginning or I can create the same table in the
subscriber with the new structure, make sure that all records exists in
subscriber same as they are in publisher, and drop the Push subscribtion and
run again (with no-sync initialization, knowing that the structure and data
already exists in the subscriber?)
Can somebody give me some additional informations if I'm right or not?
Thanks, BaniSQL.
"BaniSQL" wrote:

> I'm not sure how can I replicate the table which is part of nosync
> transactional replication? I have to add a column in that table and to
> replicate again. I'm using no-sync initialization?
> Thanks,
> BaniSQL
|||Interesting. I just tried this and even though my subscription was a nosync
one, using sp_repladdcolumn on the publisher propagated the change to the
subscriber, along with the new set of stored procedures. This was quite
unexpected for me as well, but it means that you don't need to jump through
loads of hoops here and can make your changes quite easily.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .
|||Hi Paul,
As I understood it worked simply by only running sp_repladdcolumn in the
publisher database, even without generating custom procedures for
INSERT/UPDATE/DELETE on the Subscriber for this article/table?
Thanks,
BaniSQL.
"Paul Ibison" wrote:

> Interesting. I just tried this and even though my subscription was a nosync
> one, using sp_repladdcolumn on the publisher propagated the change to the
> subscriber, along with the new set of stored procedures. This was quite
> unexpected for me as well, but it means that you don't need to jump through
> loads of hoops here and can make your changes quite easily.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com .
>
>
|||Exactly - I tried it in a test environment and it worked fine.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com .

Adding an existing column to replication

I have a table for which a subset of columns are being replicated. When I
want an additional column to be replicated, the GUI (publication properties
--> Filter Columns tab) alerts you that CHANGING THIS PROPERTY AUTOMATICALLY
MARKS FOR REINITIALIZATION ALL SUBSCRIPTIONS THAT SUPPORT AUTOMATIC
INITIALIZATION ...
Question:
1) How can I add the column without causing reinitalization to occur? I do
not want another snaphot to occur.
2) Will custom stored procedures be overwritten if the snapshot is not run?
Or will they have to be updated manually. Specifically, the original
stored procedure will only have N variables passed to it (for each of the
columns being replicated). Howerver after the change, the stored procedure
will require N+1 variables (for the new column that is added). Will the
new variable need to be added to the stored procedure manually?
*** Thanks in advance for your help ****
1) for transactional replication only the article will be re-snapshotted.
For merge the entire publication will be.
2) When you say custom procs, do you mean ones you have created yourself or
the replication stored procedures auto generated by SQL Server. If you mean
the later, yes they will be regenerated and will be corrected for the new
column. If you really do have custom replication stored procedures and have
disabled the autogeneration of the replication stored procedures they will
not be whacked, otherwise they will.
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
"Ziggy" <Ziggy@.discussions.microsoft.com> wrote in message
news:F96E8ECD-CA4D-4FB7-AD96-5402DB601135@.microsoft.com...
> I have a table for which a subset of columns are being replicated. When I
> want an additional column to be replicated, the GUI (publication
properties
> --> Filter Columns tab) alerts you that CHANGING THIS PROPERTY
AUTOMATICALLY
> MARKS FOR REINITIALIZATION ALL SUBSCRIPTIONS THAT SUPPORT AUTOMATIC
> INITIALIZATION ...
> Question:
> 1) How can I add the column without causing reinitalization to occur? I
do
> not want another snaphot to occur.
> 2) Will custom stored procedures be overwritten if the snapshot is not
run?
> Or will they have to be updated manually. Specifically, the original
> stored procedure will only have N variables passed to it (for each of the
> columns being replicated). Howerver after the change, the stored
procedure
> will require N+1 variables (for the new column that is added). Will the
> new variable need to be added to the stored procedure manually?
> *** Thanks in advance for your help ****
>