"Shirley Kelly [MS]" <skelly@online.microsoft.com> wrote in message
news:7VxCWp49DHA.2808@cpmsftngxa07.phx.gbl...
> Hi Tina,
>
> Unfortunately, there isn't a simple way to change the datatype of a
> published column. Here's a method that will work, but it's tedious.
>
> 1. Use sp_repladdcolumn to add a new 'dummy' column with the varchar(140)
> value.
> 2. Transfer all the data from the existing column to the new dummy
column.
> Depending on how large the existing column is, this could impact
> replication performance and take a while to complete.
> 3. Delete the existing column from the publication, using either
> Enterprise Manager or sp_repldropcolumn.
> 4. Re-create the column with varchar(140).
> 5. Transfer the data from the dummy column back the newly re-created
> varchar(140) column. Once again, this can impact performance if there are
> lots of rows in the table.
> 6. Remove the dummy column using sp_repldropcolumn or Enterprise Manager.
>
>
> Regards,
> Shirley
> SQL Server Support
>
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> You assume all risk for your use. © 2001 Microsoft Corporation. All rights
> reserved.
>

"Veerendra" <Veerendrak@hotmail.com> wrote in message
news:12a0d01c3f746$874b5b60$a101280a@phx.gbl...
> I would drop and add article again. Please the sample
> code.
>
> sp_droparticle @publication = 'publicationName'
> , @article = 'tableName'
> , @force_invalidate_snapshot = 1
>
> go
> alter table tableName
> alter column columnName varchar(140)
> go
>
> exec sp_addarticle
> @publication = 'publicationName'
> , @article = N'tableName'
> , @source_owner = N'dbo'
> , @source_object = N'S_LST_OF_VAL'
> , @destination_table = N'S_LST_OF_VAL'
> , @type = N'logbased'
> , @creation_script = null
> , @description = null
> , @pre_creation_cmd = N'drop'
> , @schema_option = 0x00000000000000F3
> , @status = 16
> , @vertical_partition = N'false'
> , @ins_cmd = N'CALL sp_MSins_tableName'
> , @del_cmd = N'CALL sp_MSdel_tableName'
> , @upd_cmd = N'MCALL sp_MSupd_tableName'
> , @filter = null
> , @sync_object = null
> , @auto_identity_range = N'false'
> , @force_invalidate_snapshot = 1
>
> Exec sp_refreshsubscriptions 'publicationName'
>
> Note : Start the snapshot agent at the end.
>
> Regards
> Veerendra
> MCDBA
>
> >-----Original Message-----
> >I need to increase the size of a column from varchar
> (100) to varchar(140) in
> >a table that is part of merge replication. I tried
> using the following, it
> >just adds the dummy column and doesn't alter my
> invoice_description column
> >with the new size.
> >
> >sp_repladdcolumn @source_object = 'invoice', @column
> = 'dummy', @typetext =
> >'int',
> >@publication_to_add = 'pubs', @schema_change_script =
> >'c:\temp\upgrade_invoice.sql'
> >
> >Upgrade script looks like this:
> >ALTER TABLE invoice ALTER COLUMN invoice_description
> varchar(140)
> >
> >Any help would be appreciated.
> >
> >
> >.
> >