In SQL Server 2000 the following works but in 2005 it gave me an error.

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Thanks

This is twice now you posted a question but did not include the example. Can
you please post exactly what the commands are you are trying to run when you
get the error?

--
Andrew J. Kelly SQL MVP

"Rogers" <naissani (AT) hotmail (DOT) com> wrote

I did but I don't know why commands are not sending...


"Andrew J. Kelly" <sqlmvpnooospam (AT) shadhawk (DOT) com> wrote

Nor do I. Try pasting your commands into Notepad first. Then, in Notepad,
select those lines, then cut and paste them into your email message.

RLF
"Rogers" <naissani (AT) hotmail (DOT) com> wrote

The Query I post on my database gave the same error:

SELECT TOP 10000 A.[unid]
FROM [dbo].[hardware] A INNER JOIN (SELECT TOP 1000 B.[recordid] AS [recordid],B.[ranking] AS [ranking]
FROM [dbo].[hardware] C INNER JOIN (SELECT D.[recordid],(SUM( D.[weight]))/(SUM( E.[count])) AS [ranking]
FROM (SELECT F.[recordid],F.[tableid],F.[woordid],((COUNT( F.[woordid])+0.0))/(7) AS [weight],0 AS [ignore],CASE WHEN F.[woordid]=N'5b0001' THEN 1 ELSE 0 END AS [notOr],0 AS [OrGroup0]
FROM [dbo].[woordindex] F WHERE (F.[tableid]=5) AND (F.[woordid] IN(N'5b0001')) GROUP BY F.[recordid],F.[tableid],F.[woordid]) D INNER JOIN [dbo].[recordwoordcount] E ON ((E.[recordid]=D.[recordid]) AND (E.[tableid]=D.[tableid])) AND (ABS(E.[recordstatus])=1) GROUP BY D.[recordid] HAVING SUM( D.[notOr])>=1) B ON C.[unid]=B.[recordid] WHERE (C.[status]=1) OR (C.[status]=-1) ORDER BY ranking DESC) G ON G.[recordid]=A.[unid] WHERE A.[status]=1 ORDER BY A.[naam] ASC

Msg 8115, Level 16, State 1, Line 1
Arithmetic overflow error converting numeric to data type numeric.

Any idea on how to determine which field no longer is correct?


From http://www.google.nl/search?q=Msg+81...&start=10&sa=N

Posted via DevelopmentNow.com Groups
http://www.developmentnow.com/g/

Manon
It is not easy as I also blocked about such issues
http://dimantdatabasesolutions.blogs...-would-be.html
(Erland had filled Connect about it)


Now , I see the problem might occur here COUNT( F.[woordid])+0.0))/(7) AS
[weight],0 or her SUM( D.[weight]))/(SUM( E.[count])) , so check out what
datatype is so it looks like you want to CAST literal (N'5b0001') to
numeric
Try THEN '1' ELSE '0' END AS [notOr],0 AS [OrGroup0]




"Manon" <manonr (AT) topdesk (DOT) nl> wrote

Manon (manonr (AT) topdesk (DOT) nl) writes:
F.[woordid]=N'5b0001' THEN 1 ELSE 0 END AS [notOr],0 AS [OrGroup0]
[dbo].[recordwoordcount] E ON ((E.[recordid]=D.[recordid]) AND
(E.[tableid]=D.[tableid])) AND (ABS(E.[recordstatus])=1) GROUP BY
D.[recordid] HAVING SUM( D.[notOr])>=1) B ON C.[unid]=B.[recordid] WHERE
(C.[status]=1) OR (C.[status]=-1) ORDER BY ranking DESC) G ON
G.[recordid]=A.[unid] WHERE A.[status]=1 ORDER BY A.[naam] ASC
Since you have a number of nested derived tabless, you can run the
inner queries, and see on which level the error start to appear.

My bet goes to one of the SUM functions.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinf...ons/books.mspx