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

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

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
Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.