Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!
It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
--
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data type in replicated table

How do I change column data type for replicated table in SQL 2000?
(transactional replication). I need to preserve data in a column, can't drop
and re-add it.
Thank you in advance for your help!It can't be done without dropping and readding. The way you do it is copy
the contents of the column to a temp table with pk info. Drop the column
using sp_repldropcolumn and add it again using sp_repladdcolumn
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in
message news:B358A642-812F-456D-971B-3DB14BF8D60E@.microsoft.com...
> How do I change column data type for replicated table in SQL 2000?
> (transactional replication). I need to preserve data in a column, can't
> drop
> and re-add it.
> Thank you in advance for your help!

Change data structure of Replicated data

Hello,
I have a database in SQL Server 2000, on which created publication and it
has one subscriber .
now if I change in data structure , following error occured.
'EMPMAST' table
- Unable to modify table.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
columns to table 'EMPMAST' because it is being published for merge
replication.
Harsha........................
Harsha,
please take 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)
|||and sp_addmergearticle for merge replication
"Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
> Hello,
> I have a database in SQL Server 2000, on which created publication and it
> has one subscriber .
> now if I change in data structure , following error occured.
> 'EMPMAST' table
> - Unable to modify table.
> ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot add
> columns to table 'EMPMAST' because it is being published for merge
> replication.
> Harsha........................
>
>
|||"uykusuz" <yb> wrote in message news:eCssQMmdFHA.720@.TK2MSFTNGP15.phx.gbl...
> and sp_addmergearticle for merge replication
that is for adding articles not columns , sorry
[vbcol=seagreen]
> "Harsha Shah" <har_sha_99@.hotmail.com> wrote in message
> news:uvdlOqldFHA.688@.TK2MSFTNGP14.phx.gbl...
it
>

Change Column lenght on a replicated database

I need to increase the a column in a replicated merge database. The
column is 100 char in lenght and I need to change it to 200. I have
read the "Schema Changes on Publication Databases". But it seems to
be a little confusing.
Thanks.
Alter table is possible in SQL Server 2005 but unfortunately this is not
possible in SQL Server 2000. You could drop and readd the subscription, or
you could solve it in a roundabout way: add a new column with the new
datatype (sp_repladdcolumn), do an update on the table to populate the
column, then drop the column (sp_repldropcolumn). Do this again to create
the column having the same original name.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message news:<#Q7NK#KrEHA.2588@.TK2MSFTNGP12.phx.gbl>...
> Alter table is possible in SQL Server 2005 but unfortunately this is not
> possible in SQL Server 2000. You could drop and readd the subscription, or
> you could solve it in a roundabout way: add a new column with the new
> datatype (sp_repladdcolumn), do an update on the table to populate the
> column, then drop the column (sp_repldropcolumn). Do this again to create
> the column having the same original name.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
Thank you that's what I thought. Unfortunally, I have SQL 2000 and it
seems there are a lot of steps for something so simple. Maybe I should
just stop replication make the changes, delete the replica and do
replication again.
Same amount of work I guess
|||This has caused us a lot of heartache also... Such a simple thing! Once this
stuff is in production it can be a nightmare & unnecessary risk to change
columns
"xkravenx" <scubamike@.gmail.com> wrote in message
news:596eda8b.0410080740.76ee0786@.posting.google.c om...
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:<#Q7NK#KrEHA.2588@.TK2MSFTNGP12.phx.gbl>...[vbcol=seagreen]
or[vbcol=seagreen]
create
>
> Thank you that's what I thought. Unfortunally, I have SQL 2000 and it
> seems there are a lot of steps for something so simple. Maybe I should
> just stop replication make the changes, delete the replica and do
> replication again.
> Same amount of work I guess