Sage 100

We have a client whom we are trying to put into place our logic and supporting reporting that allows stock quantity by day selected and other things. What we are running into is using the ODBC to pull data out we are getting a message back saying ""Cannot get the current row value of column ""[MSDASQL].UnitCost"" from OLE DB provider ""MSDASQL"" for linked server ""MAS_OJI"". Conversion failed because the data value overflowed the data type used by the provider."" So this lead me to look thru the item transaction history table, and I found where there are some entries around a time period that have an extended cost of over a trillion dollars, some with unit costs in the billions with accuracy to the right of 4 decimals. This appears to be where the issue is, I have attached a DFDM of the record, if I adjust the values I can query past that record without issue. As this is an important report they need for an audit are are trying to figure out a way around this. Changing the values would throw off the costs calculated up to that day so we are now thinking about adjusting the data dictionary to change the mask or expand that field so those values can be pulled thru the ODBC (why Sage even let them in I'm not sure). Anyone else have experience with something like this? Any pointers before we start messing with the data dictionary?

I have seen something similar happen when incorrect units of measure are used in BOM production data entry.

@AlnoorCassim ??

If you are using a connection string, add this property EnforceDouble = 1 If you are pointing to a DSN then on Options tab check the box for Enforce Double It changes the numeric precision from ""decimal"" to ""double"". When you test close out of your report and restart it to make sure you consume the new ODBC connection Hope that helps.

All I can say is thank you @AlnoorCassim !!!!!!!!!! That did the job, I may just check that from now on on all ODBC's that I setup

@AlnoorCassim Is ""double"" two decimal precision?

No, it is still returning the precision expected, -2476180809.0867

Doug - No. Double means a specific kind of precision at least it does in the SQL world. Here I think it refers to the total number of digits including sign bit (for negative) and commas in the mask. In the dictionary for UnitCost the mask is 16.6. However I see a ""Print Format"" setting that shrinks the total number of digits to 18 (##,###,###.000000-). In Chris' record the total number is 19 if we count commas, sign bit, and period. That is my theory anyway and I believe Enforce Double prevents the _formatting_ restriction of 18. I'm just glad it worked out for you @ChrisMengerink

Thanks Alnoor.

@ChrisMengerink What was the source journal that created the entry? Do you know the cause? I've seen this before and I don't believe we ever got to the root cause. The times I have seen it occurred in Bill of Material production entry.

@DougHiggs the entry in the screen shot was from an invoice, but there are other records in there from other events with same issue, I remember seeing something from inventory count as well. When I get back in though I'll see if anything stands out.

Here's a link to a post I made in 2015: https://90minds-com.socialcast.com/messages/25900590?ref=stream