SQL Server column type change

[tqmd1]

Dear SIR,
I am using sqlserver2005.
Table1 has a column named bill_no and its type is text
Now I want to change its type "Numeric"
When I do this the it displays this error message

---------------------------
Microsoft SQL Server Management Studio
---------------------------
Conversion from 'text' to 'numeric' is not supported on the connected database server.

How to change type text to numeric
I do not want it through vb.net codes.
I want to apply it directly in sqlserver 2005.

Please help

[ChrisPadgham]

You will probably need to export the table data, drop the table and rebuild and reload.

[sknake]

the text datatype is for BLOBs. You can substring the data out if you inadvertently used a blob for number storage? You can probably do this in fewer operations, but to clearly demonstrate the point:

IF OBJECT_ID('Test123', 'U') IS NOT NULL DROP TABLE Test123
Create Table Test123
(
  ID int,
  Col text
)

Insert Into Test123 (ID, Col) Values (1, '5.5')

GO

Alter Table Test123 Add ColNumber Numeric

GO

Update Test123 Set ColNumber = SUBSTRING(Col, 1, 50) Where IsNumeric(SUBSTRING(Col, 1, 50)) = 1

GO

Alter Table Test123 Drop Column Col

GO

Alter Table Test123 Add Col Numeric

GO

Update Test123 Set Col = ColNumber

GO

Alter Table Test123 Drop Column ColNumber