I have an Access database that I am converting to an SQL database using sqlbulkcopy in vb.net. We have currency values (up to 2 decimal places) stored in Access as Double. When I convert these Access fields to SQL fields with type [decimal] (19, 2), a number like 81.6 stored in the Access field sometimes appears as 81.59 in the SQL field when converted.
If I change the type in SQL to [decimal] (19,4) then it shows as 81.5999. The number in Access is definitely calculated and stored to no more than two decimal places. Can someone explain to me why SQL is doing this? It doesn't happen all the time... only in some records.
I've reviewed just about any article I could find that explains decimal types and I understand what decimal is (I think), but I don't understand why numbers sometimes get converted like this. Thanks for your help.
EDIT: Here is the code that I mentioned below that I used to loop through all of the tables and columns to rename the aliased columns ending in "1Z" to their original names:
Dim dtTbls As DataTable = cnnSQL.GetSchema("Tables") For Each drTbls As DataRow In dtTbls.Rows Dim sTableName As String = drTbls(2) Dim query As String = String.Concat("select * from [", sTableName, "] Where 0=1") Dim myCmd As SqlDataAdapter = New SqlDataAdapter(query, cnnSQL) Dim myData As New DataSet() myCmd.Fill(myData) For Each colu As DataColumn In myData.Tables(0).Columns If colu.ColumnName.EndsWith("1Z") = True Then Dim sNewColName As String = colu.ColumnName.Remove(colu.ColumnName.Length - 2, 2) dbSQL.ExecuteNonQuery(String.Concat("EXEC sp_rename '", sTableName, ".", colu.ColumnName, "', '", sNewColName, "', 'COLUMN'")) End If Next colu Next drTbls
Your problem is that floating-point types like
Double often cannot store numbers exactly, even values which seem to be quite "simple" numbers to us humans. This can be demonstrated by typing the following into the Immediate Window of the VBA editor in Access:
?(CDbl(81.60) * CDbl(100)) - CDbl(8160)
The result returned is
Your experience suggests that a direct transfer of Access
Double to SQL Server
decimal will truncate the number instead of rounding it. Therefore, what you need to do is copy the values from Access into a separate SQL Server column of type
float, and then use an UPDATE query in SQL Server to populate the
decimal(19,2) column, e.g.,
UPDATE TableName SET DecimalColumn = ROUND(FloatColumn, 2)
In case it makes things easier, it should also be possible to do the rounding and conversion on the Access side. You could create a Select query for each table that rounds the monetary values and converts them to
Currency, like this...
SELECT ProductID, ProductName, CCur(Round([UnitPrice],2)) AS CurrUnitPrice FROM Products;
SqlBulkCopy the query instead of the table.
As you may have discovered, trying to convert a column while retaining the original column name with a query like...
SELECT ProductID, ProductName, CCur(Round([UnitPrice],2)) AS UnitPrice FROM Products
...results in the error
Circular reference cause by alias 'UnitPrice' in query definition's SELECT list
A workaround for that issue would be to use a query like this:
SELECT ProductID, ProductName, CurrUnitPrice AS UnitPrice FROM ( SELECT ProductID, ProductName, CCur(Round([UnitPrice],2)) AS CurrUnitPrice FROM Products )