0

We have a legacy financial application running which is no longer supported but important to us.

We have overwritten approximateley 250 rows manually in 2 columns "price" and "selling_price" and now the application crashes at some point where it calculates some reports.

I believe our mistake was to not round to 2 decimals before writing the valuies.

My plan is to use MS Access and update the values in the row.

Old values were like:

 24.48
  6.98
100.10

But we also wrote values like 

 20.19802
 99.42882
108.1302

and I believe this lets it crash when it sums over them

Could it be a good idea just to make an MS Access query and overwrite with the rounded values? Or is it on the MS SQL Tabel level more accurate to use a SQL Query that modifies using T-SQL functions? My idea would be to overwrite with ROUND(108.1302, 2)

Someone with a lot of MS SQL experience?

This row is of type float, lenght 53, scale 0, allow Null = no.

I am not quite sure what happens internally and what the application is expecting, because float is stored in binary, so what we see cannot be the same that is internally stored.

In MS Access, we connect to the tables using ODBC, it would almost be the same function ROUND(108.1302, 2), but does this also lead to the same result?

How should this be overwritten, what seems safest from your experience?

Alojz Janez
  • 530
  • 1
  • 3
  • 13
  • How many incorrectly formatted values are we dealing with? Also, involving Access when you are already using SQL Server is usually not a good decision. – Eric Hauenstein Apr 14 '14 at 12:51
  • I'd do the converting in SQL Server to avoid messing with the source data (I assume the incorrect data is ported from Access to SQL Server). Also it might be worthwhile to use the DECIMAL datatype in SQL Server. – Allan S. Hansen Apr 14 '14 at 13:11
  • Those are approximateley 250 rows affected and 2 columns. Access is writing to the database directly, there is no import / export involved. I feel not at all that I am allowed to change the data type, because the main application owns the database and it might lead to further problems. We are trying to do as few intrusions to the DB as possible, and the application works for many years fine now with the datatypes it has. – Alojz Janez Apr 14 '14 at 16:47

1 Answers1

1

If you're using Access as a front End for a SQL Server back End then the two approaches should effectively be the same.

You could check this before updating by running the conversion as a select statement in Access and then running it as a SQL pass through

SELECT
ColumnA, ROUND(ColumnA, 2) AS RoundedA
FROM TableName
WHERE ColumnA <> ROUND(ColumnA, 2) 
GROUP BY  ColumnA
ORDER BY  ColumnA

And checking they match

Tom Page
  • 1,211
  • 1
  • 7
  • 8
  • Hi Tom, thank you, I did do it and I used MS Access and it worked. Those are linked tables and I am now quite sure that MS Access writes correct SQL and sends it to SQL Server when I start the Modify Query in the Access GUI. MS Access is actually a nice GUI for SQL Server for many tasks. – Alojz Janez Apr 14 '14 at 21:54