• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2387
  • Last Modified:

Error 7354 on SQL server

I am using this SQL Query

SELECT c.ID, d.*
FROM Company c RIGHT OUTER JOIN
OPENQUERY(Dynamics,
'SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth,
SUM(s.QTYTOINV) AS CubeQty, SUM(s.XTNDPRCE) AS CubeDollar
FROM SOP30200 h, SOP30300 s, IV00101 i
WHERE h.SOPNUMBE = s.SOPNUMBE
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)') d
ON c.CustNmbr = d.CUSTNMBR

and I ve got this message :

Server: Msg 7354, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' supplied invalid metadata for column 'CubeQty'. The precision exceeded the allowable maximum.
OLE DB error trace [Non-interface error:  Column 'CubeQty' (ordinal 4) of object 'SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth,
SUM(s.QTYTOINV) AS CubeQty, SUM(s.XTNDPRCE) AS CubeDollar
FROM SOP30200 h, SOP30300 s, IV00101 i
WHERE h.SOPNUMBE = s.SOPNUMBE
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)' reported an unsupported value for PRECISION of 74].


If try to get rid of "SUM(s.QTYTOINV) AS CubeQty", I have the same error but on CubeDollar
If I try to use MAX instead of SUM, I have no error

Dynamics is a Persavise SQL server (btrieve Data)


Can someone help me, Please
0
mbelouara
Asked:
mbelouara
  • 3
1 Solution
 
ispalenyCommented:
A) MS SQL Server support precision up to 38, so 74 is not supported.
Agregate Sum() increased precision. It is probably a default behavior of Pervasive SQL server.

SELECT c.ID, d.*
FROM Company c
RIGHT OUTER JOIN
OPENQUERY(Dynamics,
'SELECT h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE) AS CubeMonth
,CAST(SUM(s.QTYTOINV) AS DECIMAL(38,16)) AS CubeQty
,CAST(SUM(s.XTNDPRCE) AS DECIMAL(38,16)) AS CubeDollar
FROM SOP30200 h, SOP30300 s, IV00101 i
WHERE h.SOPNUMBE = s.SOPNUMBE
GROUP BY h.CUSTNMBR, s.ITEMNMBR, MONTH(h.DOCDATE)') d
ON c.CustNmbr = d.CUSTNMBR

B) Or it is a bug.
Something like http://dbforums.com/arch/26/2002/3/321667
0
 
ispalenyCommented:
How it works ?
0
 
mbelouaraAuthor Commented:
Thanks Ispaleny.

"CAST" didn't work but you gave me a good clue
I just changed cast for convert
I have now CONVERT(SUM(s.QTYTOINV),SQL_DOUBLE) AS CubeQty, CONVERT(SUM(s.XTNDPRCE),SQL_DOUBLE) AS CubeDollar

Thanks a lot

You save my life

0
 
ispalenyCommented:
I downloaded Pervasive SQL server help file and I found nothing about converting, only that CAST is a reserved word. Then I sought thrue www.pervasive.com and I found 1 use of SELECT CAST(... AS ...).
So the correct syntax is CONVERT(X,SQL_DOUBLE).

MSSQLSERVER also increases precision for SUM() function to prevent an overflow.

This is a very rare case, when an error reported by ODBC meams exactly the same thing it says.

Best regards, Ivo Spaleny.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now