Double Precision (Real, Double, Access & MS SQL)

[Caradog]

Guys, I got a bit of problem with data types.

I have an Access Database which contains various amounts of data stored on a daily basis. The format of this data in Access is in Real.

I have written a web front end to allow users to query this data in vb.net, however, the data is copied via a DTS package from Access to an MS SQL server with the same data is stored in a Double. I've noticed after the copy has finished that the data has a different precision in MS SQL, just a few points off, but its causing me problems...

I need to keep the data in its original format without any alterations. Any idea's how to do that?

 

[dbomrrsm]

the data field in sql server needs to be set at the correct scale and precision for example if in access it is always 2 decimal places then the field needs to have a scale of 2.

Numeric data types with fixed precision and scale.

decimal[(p[, s])] and numeric[(p[, s])]

Fixed precision and scale numbers. When maximum precision is used, valid values are from - 10^38 +1 through 10^38 - 1. The SQL-92 synonyms for decimal are dec and dec(p, s).

p (precision)

Specifies the maximum total number of decimal digits that can be stored, both to the left and to the right of the decimal point. The precision must be a value from 1 through the maximum precision. The maximum precision is 38. The default precision is 18.

s (scale)

Specifies the maximum number of decimal digits that can be stored to the right of the decimal point. Scale must be a value from 0 through p. Scale can be specified only if precision is specified. The default scale is 0; therefore, 0 <= s <= p. Maximum storage sizes vary, based on the precision.

[blue]DBomrrsm[/blue]

[blue]Software code, like laws and sausages, should never be examined in production[/blue][black] - [/black][purple]Edward Tenner[/purple]

 

[JamesLean]

Caradog, the equivalent floating point data types between Access and SQL Server are:

Access -> SQL Server
Single -> Real
Double -> Float

DBomrrsm, floating point data types do not have a precision and scale - these are only used in exact numeric data types.

--James

 

[Caradog]

Cheers guys. Fixed my problem