|
|
Posted by Erland Sommarskog on 11/24/06 22:56
(othellomy@yahoo.com) writes:
> Hi Madhivanan,
> Thanks for the input. The column type is varchar where the value
> 1.2334e+006 is stored. So I need to convert it to float as suggested by
> some before converting it to decimal. Anyway, that brings another
> issue. When running the query the server just errors out without giving
> the value that causes the error. It just says 'conversion error'. I
> had to take the SQl out and put it in a cursor and use a loop to find
> out which row is actually causing the error and find the value
> 1.2334e+006. Is there any easier way to find out which row in the table
> causes the SQL server to error out. For example can I set the error
> level so that I find more information so that I can locate the row in
> the table.
Unfortunately, there are not really any good options. The best is probably
to run a SELECT query, and takes some hint from where it terminates. This
example illustrates:
CREATE TABLE #tmp1 (a varchar(23) NOT NULL, b int IDENTITY)
go
INSERT #tmp1(a) VALUES ('1234')
INSERT #tmp1(a) VALUES ('1232')
INSERT #tmp1(a) VALUES ('2344')
INSERT #tmp1(a) VALUES ('34.34')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('-1234')
INSERT #tmp1(a) VALUES ('1234e+006')
INSERT #tmp1(a) VALUES ('777')
go
SELECT convert(decimal(20, 2), a) FROM #tmp1 ORDER BY b
go
DROP TABLE #tmp1
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|