Full Optimize Problem

[#1]

Hi Guys,

I am getting the following error when doing a full optimize with database compression:"Error message:Arithmetic overflow error converting numeric to data type numeric.

The statement has been terminated."

I am guessing this has a lot to do with the performance issues i am having with system at the moment. Everything is slow at the moment and I am getting SendGovener issues as well.

Any help will be appreciated.

Regards,

AvdB

[sorin_radulescu]

Hi,

You need to know what it will be the with aproximation the biggest number insert into database.

In this way you will know what kind of format you should have into database.

By the way deleting the record from tbldefaults is not the right approach.

You have to use webadmin page - Application Parameters and you can set there the format use for your application.

Knowing what kind of numbers you have to handle you will be able to set the correct format for your signeddata.

Kind Regards

Sorin Radulescu

[Former Member]

Hi Guys,

I organized dome down time for the system tomorrow morning and I would just like to confirm the steps with you before potentially breaking the system.

1.Backup the tblFactFinance table

2.Create a new table with a signeddata column size of decimal(25,6) (they need 6 numbers after the comma)

3.copy all the datafrom the tblFactFinance into the new table

4.Delete the old table and rename the new one to tblFactFinance

5.Perform a Full Optimize and hope that is actually works

Is this the best way to do this fix?

Regards,

AvdB

[Former Member]

Hi guys,

I implemented my proposed fix above and it is still giving me the same error when I try to optimize the application. Any other ideas? I have also logged a call now with SAP, this is becoming a big issue for me.

Regards,

AvdB

[Former Member]

Hi All,

I finally got it working. I had to follow the following steps:

1. Used the following query to determine what the decimal precision had to be (just change the app specific stuff)

SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO], CONVERT(DECIMAL(25,10),SUM([SIGNEDDATA])) AS [SIGNEDDATA],[SOURCE],[ACCOUNTL] FROM ( SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SIGNEDDATA],[SOURCE],[ACCOUNTL]
FROM [tblFAC2FinanceStatutory]
UNION ALL
SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SIGNEDDATA],[SOURCE],[ACCOUNTL]
FROM [tblFACTFinanceStatutory]
UNION ALL
SELECT [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SIGNEDDATA],[SOURCE],[ACCOUNTL]
FROM [tblFACTWBFinanceStatutory]
) T
GROUP BY [ACCTDETAIL],[CATEGORY],[GROUPS],[LEGALENTITY],[TIMEID],[DATASRCL],[INTCO],[SOURCE],[ACCOUNTL]
HAVING ABS(SUM(SIGNEDDATA)) > 0.0000001

2. Changed the signed_data_format in the APPLICATION parameters (not appset parameters)

3.modified the application

4.optimized the application

Thanks for all the help!

Regards,

AvdB