2uzhan.com
Advertisement
Now Place:2uzhan.com » SQL Server column type change

SQL Server column type change

Databases @ January 17, 2010   Views:0

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

--------------Solutions-------------

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

Hey there,

I am trying to create a register code. So basicly im making a quiz and every time a user starts up my program he/she needs to register (making ...

Hello,

I have a problem to get unique values from dateperiod-loop.

There is a tons of examples how to do it, but after hours of searching, i couldn't find suitable ...

hi
this is one of my assignment that I have to do. it is a triangle calculator. everything well with it but it just the calculation. when I run it, ...

Tags:mssql
© 2018 2uzhan.com Contact