Change the Column datatype

Dear Friends
I need to change the column datatype from Real to Float
as now i am facing the problem when my user put value
140189.14 it is accepting only 104189.1
Please note that i have the same table in merge
replication and i do not want to stop the replication.
Please guide me to solve the problem.
Best regards
Sharad
the best way to do this is to use sp_repladdcolumn and sp_repldropcolumn
here are the steps
1) use sp_repladdcolumn to add a dummy column with the float data type. Call
this column dummy
2) update the table on the publisher so the dummy column has the values of
the column whose data type you are trying to change.
3) use sp_repldropcolumn to drop the column containing the real datatype
4) use sp_repladdcolumn to add the column back with the new float data type
5) update the table on the publisher so the new column has the values of the
dummy column
6) use sp_repldropcolumn to drop the dummy column
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Sharad" <niitmalad@.yahoo.co.in> wrote in message
news:172a01c4f3f6$6ccdaf60$a501280a@.phx.gbl...
> Dear Friends
> I need to change the column datatype from Real to Float
> as now i am facing the problem when my user put value
> 140189.14 it is accepting only 104189.1
> Please note that i have the same table in merge
> replication and i do not want to stop the replication.
> Please guide me to solve the problem.
> Best regards
> Sharad
|||I have an idea, which may simplify this routine. I did not tried it myself
yet, but it should work just fine I guess
Let's say you have tableX with columnA which type should be changed from
real to float
1) Create temporary tableY with PK column (or columns) from tableX, and also
with columnA
2) Copy data from tableX to tableY
3) use sp_repldropcolumn to drop columnA (containing the real datatype) from
tableX
4) use sp_repladdcolumn to add the columnA back with the new float data type
5) Copy data from tableY column A to tableY columnA
6) Drop temporary tableY
This way replication subsystem has to do following steps:
1) Drop column
2) Add column
3) Process insert of data into columnA
Compared to the steps which should be performed when using earlier mentioned
approach:
1) Add dummy column
2) Process insert of data into dummy column from columnA
3) Drop column
4) Add column
5) Process insert of data into columnA from dummy column
6) Drop dummy column
Regards,
Kestutis Adomavicius
Consultant
UAB "Baltic Software Solutions"
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u5trHx$8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> the best way to do this is to use sp_repladdcolumn and sp_repldropcolumn
> here are the steps
> 1) use sp_repladdcolumn to add a dummy column with the float data type.
Call
> this column dummy
> 2) update the table on the publisher so the dummy column has the values of
> the column whose data type you are trying to change.
> 3) use sp_repldropcolumn to drop the column containing the real datatype
> 4) use sp_repladdcolumn to add the column back with the new float data
type
> 5) update the table on the publisher so the new column has the values of
the
> dummy column
> 6) use sp_repldropcolumn to drop the dummy column
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "Sharad" <niitmalad@.yahoo.co.in> wrote in message
> news:172a01c4f3f6$6ccdaf60$a501280a@.phx.gbl...
>
|||Yes, this is a much simpler and better way of doing it. Thanks Kestutis!
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Kestutis Adomavicius" <kicker.lt@.nospaamm_tut.by> wrote in message
news:uVd2uDA9EHA.3756@.TK2MSFTNGP14.phx.gbl...
> I have an idea, which may simplify this routine. I did not tried it myself
> yet, but it should work just fine I guess
> Let's say you have tableX with columnA which type should be changed from
> real to float
> 1) Create temporary tableY with PK column (or columns) from tableX, and
also
> with columnA
> 2) Copy data from tableX to tableY
> 3) use sp_repldropcolumn to drop columnA (containing the real datatype)
from
> tableX
> 4) use sp_repladdcolumn to add the columnA back with the new float data
type
> 5) Copy data from tableY column A to tableY columnA
> 6) Drop temporary tableY
> This way replication subsystem has to do following steps:
> 1) Drop column
> 2) Add column
> 3) Process insert of data into columnA
> Compared to the steps which should be performed when using earlier
mentioned[vbcol=seagreen]
> approach:
> 1) Add dummy column
> 2) Process insert of data into dummy column from columnA
> 3) Drop column
> 4) Add column
> 5) Process insert of data into columnA from dummy column
> 6) Drop dummy column
> --
> Regards,
> Kestutis Adomavicius
> Consultant
> UAB "Baltic Software Solutions"
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:u5trHx$8EHA.1408@.TK2MSFTNGP10.phx.gbl...
> Call
of
> type
> the
>