"Tony Fryer" <tfryer@paradise.net.nz> wrote in message
news:c2e401c408a5$4a8c47f0$a501280a@phx.gbl...
> I'm trying to figure out how I can alter the size of a
> column (e.g. increase a varchar(10) column to varchar(50))
> when the column is part of a replicated column.
> We use merge replication between SQL2000 and SQLCE on
> PocketPC devices. It is important that the subscribers be
> unaffected by the change (don't want them to have to
> reinitialise their subscription).
>
> So far all I have come up with is a clunky approach. Run
> the following:
> sp_repladdcolumn - add a dummy varchar(50) column
> copy existing column data to new column
> sp_repldropcolumn - drom the existing varchar(10) column
> sp_repladdcolumn - re-add the column with same name as
> original column but varchar(50)
> copy data from dummy column to new column
> sp_repldropcolumn - drop dummy column
>
> This works fine as long as the column is nullable and has
> no constraints/indexes.
>
> If I try this with an indexed column the publisher is
> fine. However the subscriber fails with a 'Bad or Invalid
> SQL statement' and 'The provider could not drop the object'
>
> I'm guessing that although I dropped the index (and re-
> created it) on the publisher it was unable to drop the
> original column on the subscriber because the index was
> still there.
>
>
> Can anyone tell me if it is even possible to alter a
> replicated column? or do I have to drop/recreate the
> publication?

"Kyle McAdam" <kyle.mcadam@sympatico.ca> wrote in message
news:qgx4c.50872$6y1.1921394@news20.bellglobal.com...
> When you create an identity column(index) , you can add the Not For
> Replication clause to de-activate the index.
>
>
> "Tony Fryer" <tfryer@paradise.net.nz> wrote in message
> news:c2e401c408a5$4a8c47f0$a501280a@phx.gbl...
> > I'm trying to figure out how I can alter the size of a
> > column (e.g. increase a varchar(10) column to varchar(50))
> > when the column is part of a replicated column.
> > We use merge replication between SQL2000 and SQLCE on
> > PocketPC devices. It is important that the subscribers be
> > unaffected by the change (don't want them to have to
> > reinitialise their subscription).
> >
> > So far all I have come up with is a clunky approach. Run
> > the following:
> > sp_repladdcolumn - add a dummy varchar(50) column
> > copy existing column data to new column
> > sp_repldropcolumn - drom the existing varchar(10) column
> > sp_repladdcolumn - re-add the column with same name as
> > original column but varchar(50)
> > copy data from dummy column to new column
> > sp_repldropcolumn - drop dummy column
> >
> > This works fine as long as the column is nullable and has
> > no constraints/indexes.
> >
> > If I try this with an indexed column the publisher is
> > fine. However the subscriber fails with a 'Bad or Invalid
> > SQL statement' and 'The provider could not drop the object'
> >
> > I'm guessing that although I dropped the index (and re-
> > created it) on the publisher it was unable to drop the
> > original column on the subscriber because the index was
> > still there.
> >
> >
> > Can anyone tell me if it is even possible to alter a
> > replicated column? or do I have to drop/recreate the
> > publication?
>
>

"Tony Fryer" <tfryer@paradise.net.nz> wrote in message
news:c2e401c408a5$4a8c47f0$a501280a@phx.gbl...
> I'm trying to figure out how I can alter the size of a
> column (e.g. increase a varchar(10) column to varchar(50))
> when the column is part of a replicated column.
> We use merge replication between SQL2000 and SQLCE on
> PocketPC devices. It is important that the subscribers be
> unaffected by the change (don't want them to have to
> reinitialise their subscription).
>
> So far all I have come up with is a clunky approach. Run
> the following:
> sp_repladdcolumn - add a dummy varchar(50) column
> copy existing column data to new column
> sp_repldropcolumn - drom the existing varchar(10) column
> sp_repladdcolumn - re-add the column with same name as
> original column but varchar(50)
> copy data from dummy column to new column
> sp_repldropcolumn - drop dummy column
>
> This works fine as long as the column is nullable and has
> no constraints/indexes.
>
> If I try this with an indexed column the publisher is
> fine. However the subscriber fails with a 'Bad or Invalid
> SQL statement' and 'The provider could not drop the object'
>
> I'm guessing that although I dropped the index (and re-
> created it) on the publisher it was unable to drop the
> original column on the subscriber because the index was
> still there.
>
>
> Can anyone tell me if it is even possible to alter a
> replicated column? or do I have to drop/recreate the
> publication?