Pull Merge Replication - Expand Column Size

I need to increase the size of a column in a table which is part of merge
replication.
Here's the approach I believe I need to take:
1) Use sp_repladdcolumn to create a new temporary column and use the
schema_script to populate my new column
with the values from my old column.
2) Use sp_repldropcolumn to drop the old column
3) Use sp_repladdcolumn to create a new column using the "old column" name
and use the
schema_script to populate the new column with the values.
4) Use sp_repldropcolumn to drop the temporary column created in step 1.
Questions:
1) Do I need do these steps on both the publisher and subscriber?
2) Million Dollar Question - Will this work without having to reinitialize
all my subscribers?
Thanks for all your help!
Tina
1) just publisher
2) yes
Rgds,
Paul Ibison
[vbcol=seagreen]
|||The sp_repladdcolumn system stored procedure adds the dummy column but it
doesn't run the script I set for the schema_change_script parameter.
Is there something I'm missing?
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e2SwX4IsEHA.4008@.TK2MSFTNGP14.phx.gbl...
> 1) just publisher
> 2) yes
> Rgds,
> Paul Ibison
>
>
|||I have found in testing that this approach will NOT work when using a Push
ExchangeType ( 1 ). Replication makes the schema change on the subscriber
but the values in the column are lost.
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||use sp_repladdcolumn to add a dummy column.
Then update the the value of the dummy column with the value of the column
whose data type you want to change. Do this on the publisher.
Then use sp_repldropcolumn to drop the column which you wish to modify.
Then use sp_repladdcolumn to add the column back in with the correct
datatype.
Then update the value of the new column with the value of the dummy column.
Do this on the publisher.
Then drop the dummy column using sp_repldropcolumn.
Do all the work on the publisher, you shouldn't have to do anything on the
subscriber. You do not have to reinitialize all of your subscribers.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Tina Smith" <tb.smith@.earthlink.net> wrote in message
news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
> I need to increase the size of a column in a table which is part of merge
> replication.
> Here's the approach I believe I need to take:
> 1) Use sp_repladdcolumn to create a new temporary column and use the
> schema_script to populate my new column
> with the values from my old column.
> 2) Use sp_repldropcolumn to drop the old column
> 3) Use sp_repladdcolumn to create a new column using the "old column" name
> and use the
> schema_script to populate the new column with the values.
> 4) Use sp_repldropcolumn to drop the temporary column created in step 1.
> --
> Questions:
> 1) Do I need do these steps on both the publisher and subscriber?
> 2) Million Dollar Question - Will this work without having to reinitialize
> all my subscribers?
> Thanks for all your help!
> Tina
>
|||Tina,
what is in the @.schema_change_script that you are
sending? This parameter shouldn't be nesessary for merge.
If it is a script to populate the column, try a simple
update statement instead (on the publisher).[vbcol=seagreen]
|||It's a script to populate the column with the values from the old column.
update CustomerContact
Set dummy = State
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:037e01c4b106$83c0b060$a401280a@.phx.gbl...
> Tina,
> what is in the @.schema_change_script that you are
> sending? This parameter shouldn't be nesessary for merge.
> If it is a script to populate the column, try a simple
> update statement instead (on the publisher).
>
|||I went with the following steps since I couldn't get the schema_script
parameter to work.
All went well on the Publisher, I now have a varchar(3) state column with
the data. I then ran the agent on the subscriber and ended up with a
varchar(3) state column BUT with no data. My subscriber is a PUSH (
exchangetype (1 ) ) for this publication so I don't see how it will work.
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'Dummy'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set dummy = state
GO
sp_repldropcolumn @.source_object = 'CustomerContact'
, @.column = 'state'
GO
sp_repladdcolumn @.source_object = 'CustomerContact'
, @.column = 'State'
, @.typetext = 'varchar(3)'
, @.publication_to_add = 'SAM_HomeOffice'
GO
update CustomerContact
set state = dummy
GO
sp_repldropcolumn @.source_object = 'CustomerContact' , @.column = 'dummy'
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:eko7LbMsEHA.1032@.TK2MSFTNGP10.phx.gbl...
> use sp_repladdcolumn to add a dummy column.
> Then update the the value of the dummy column with the value of the column
> whose data type you want to change. Do this on the publisher.
> Then use sp_repldropcolumn to drop the column which you wish to modify.
> Then use sp_repladdcolumn to add the column back in with the correct
> datatype.
> Then update the value of the new column with the value of the dummy
column.[vbcol=seagreen]
> Do this on the publisher.
> Then drop the dummy column using sp_repldropcolumn.
> Do all the work on the publisher, you shouldn't have to do anything on the
> subscriber. You do not have to reinitialize all of your subscribers.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Tina Smith" <tb.smith@.earthlink.net> wrote in message
> news:%23d9WM8HsEHA.1752@.TK2MSFTNGP14.phx.gbl...
merge[vbcol=seagreen]
name[vbcol=seagreen]
reinitialize
>
|||Pls try a simple update statement (on the publisher)
after the sp_repladdcolumn statement and miss out the
@.schema_change_script parameter.
TIA,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Sorry Tina,
ignore my last message as in your more recent post you
mentioned the -EXCHANGETYPE forcing uploading. Please try
putting the update statement in sp_addscriptexec as a
step after adding the column.
Rgds,
Paul Ibison