math error

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0
>
select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
>
can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

Try

select convert(decimal(20,2),1.2334e+006)

Madhivanan|||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.
Thanks.

Madhivanan wrote:

Quote:

Originally Posted by

othellomy@.yahoo.com wrote:

Quote:

Originally Posted by

select convert(float,'1.2334e+006')
1233400.0

select convert(decimal(20,2),'1.2334e+006')
Server: Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.

can I set some options arithabort etc to have a workaround to this
problem?
Thanks.

>
Try
>
select convert(decimal(20,2),1.2334e+006)
>
Madhivanan

|||(othellomy@.yahoo.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Materialized View Error 8908

Microsoft SQL Server 2005 - 9.00.1187.07

dbcc checkdb is failing with an interesting message:

Msg 8908, Level 16, State 1, Line 1

Indexed view 'BritishEnglishMV' (object ID 226099846) does not contain all rows that the view definition produces. Refer to Books Online for more information on this error. This does not necessarily represent an integrity issue with the data in this database.

The data materialized in the indexed view is exactly the same as the data in the underlying tables...
Books online has no info on this error.
Rebuilding the index fixes the problem.

This warning is produced if the indexed view does not 100% match the "newly generated" indexed view. This may happen in cases when there are updates performed on the underlying tables.
I will use an example to explain. If a view contains for examle an aggregation SUM, then inserting of new value to underlying table will add a new value to this sum. If the SUM was produced originally from a sequence of numbers, say a1, a2, ..., an, and the new inserted value is bb, then updating the indexed view means
(a1+a2+a3+...+an) + bb while recalculating the indexed view may prform the sum in different order.
We are still working on providing more information about warnings and errors we generate. This should improve substantially by the time we ship the final release of SQL Server 2005.

Lubor Kollar