Changing data types and lengths in replicated tables

Okay, this is a real bummer. I setup a one way replication from one SQL
server to another. Now, the data types and lengths in some of my tables needs
to change to fit a new data structure but I keep receiving and error that SQL
cannot drop the table because it is replicated. I can make the changes on the
Subscriber with no problem, but the publisher is a no-go!! I thought any
table changes I made would be replicated as well - I did not realize this was
going to be an issue.
Can anyone offer any help - our process is running tonight and I have to get
this working by 1AM.
Thanks,
Nicole Hagler
Nicole, this is just the way it is. The best way IMO, is to RClick the
Publication/ Generate SQL Script(Script the steps to delete)/ Preview/ grab
the
exec sp_dropsubscription @.publication = N'', @.article = N'', @.subscriber =
N'all', @.destination_db = N'all'
exec sp_droparticle @.publication = N'', @.article = N'',
@.force_invalidate_snapshot = 1
GO
that applies to your table. Then once you make your changes you will need to
resnapshot. (Unless you can do this when there will be no data
manipulations.) Do this first in test. 99.99% of the time that I've done
this, it only wanted to resnapshot the table in question. But recenlty it
wanted to resnapshot all of the table in the Publication and I still havent
figured out why. Worst case, create a new Publication and add this table to
that.
CR
"Nicole" <Nicole@.discussions.microsoft.com> wrote in message
news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> Okay, this is a real bummer. I setup a one way replication from one SQL
> server to another. Now, the data types and lengths in some of my tables
> needs
> to change to fit a new data structure but I keep receiving and error that
> SQL
> cannot drop the table because it is replicated. I can make the changes on
> the
> Subscriber with no problem, but the publisher is a no-go!! I thought any
> table changes I made would be replicated as well - I did not realize this
> was
> going to be an issue.
> Can anyone offer any help - our process is running tonight and I have to
> get
> this working by 1AM.
> Thanks,
> Nicole Hagler
|||Create a temp table which holds the pk and the columns you wish to change.
use sp_repldropcolumn to drop the column, and then sp_repladdcolumn to add
it back with the same name, but the new data type. Then update this column
to have the same value as the column in the temp table which corresponds to
your pk.
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
"Nicole" <Nicole@.discussions.microsoft.com> wrote in message
news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> Okay, this is a real bummer. I setup a one way replication from one SQL
> server to another. Now, the data types and lengths in some of my tables
needs
> to change to fit a new data structure but I keep receiving and error that
SQL
> cannot drop the table because it is replicated. I can make the changes on
the
> Subscriber with no problem, but the publisher is a no-go!! I thought any
> table changes I made would be replicated as well - I did not realize this
was
> going to be an issue.
> Can anyone offer any help - our process is running tonight and I have to
get
> this working by 1AM.
> Thanks,
> Nicole Hagler
|||Hilary this is an awesome idea and one I will definatley take advantage of
when possible. But Im curious if you've run into problems on updating large
tables in this way due to locking?
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:ODahu$3pFHA.820@.TK2MSFTNGP09.phx.gbl...
> Create a temp table which holds the pk and the columns you wish to change.
> use sp_repldropcolumn to drop the column, and then sp_repladdcolumn to add
> it back with the same name, but the new data type. Then update this column
> to have the same value as the column in the temp table which corresponds
> to
> your pk.
> --
> 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
> "Nicole" <Nicole@.discussions.microsoft.com> wrote in message
> news:0118E497-E907-4805-8266-174464DFE9D6@.microsoft.com...
> needs
> SQL
> the
> was
> get
>