I have a database created in Microsoft SQL 2000 Enterprise Manager. I need to change the datatype for a column name EngrMultiplier in the table ProjectReCapParameter. The current datatype is INT and I would like to replace it to FLOAT. What should I do? Write a query or change the datatype in the table design view? How? Also which version of Visual Studio will be useful to modify the client user web form currently built with .NET Framework 1.1.4322? Please Advise me.
You can do that with a simple query:
ALTER TABLE ProjectReCapParameter ALTER COLUMN EngrMultiplier float
Here is a full example using temporary tables:
IF OBJECT_ID('tempdb..#ProjectReCapParameter', 'U') IS NOT NULL DROP TABLE #ProjectReCapParameter Create Table #ProjectReCapParameter ( EngrMultiplier int ) Insert Into #ProjectReCapParameter (EngrMultiplier) Values (1) Insert Into #ProjectReCapParameter (EngrMultiplier) Values (2) Insert Into #ProjectReCapParameter (EngrMultiplier) Values (3) GO Select * From #ProjectReCapParameter GO ALTER TABLE #ProjectReCapParameter ALTER COLUMN EngrMultiplier float GO Select * From #ProjectReCapParameter
Thanks for quick reply.
I have tried the first option.
ALTER TABEL ProjectReCapParameter
ALTER COLUMN EngrMultiplier float
The result gives me the following errors:
Server: Msg 5074, Level 16, State 1, Line 1
The object 'DF_ ProjectReCapParameter_EngrMultiplier' is dependent on column 'EngrMultiplier'.
Server: Msg 4922, Level 16, State 1, Line 1
ALTER TABLE ALTER COLUMN EngrMultiplier failed because one or more object access this column.
I do not understand the second option you mentioned as
"Here is a full example using temporary tables:"
Also I tried to change other column datatype from FLOAT to INT (within the same table) and it works.
I don't know it exactly, but if the column "EngrMultiplier" is used as foreign-key (constraints) in other tables, this error can occure.
Be sure, that the column you want to modify is not used as foreign-key!
if column is a foreign-key, remove all constraints using this column, change datatype in ALL tables and finally you can add all contraints again.