[lnkForumImage]
TotalShareware - Download Free Software
Confronta i prezzi di migliaia di prodotti.
Asp Forum
 Home | Login | Register | Search 


 

Forums >

microsoft.public.sqlserver.programming

Data Type conversion from MS Access to SQL Server, problems with significant figures

bbcrock

3/30/2007 6:00:00 PM

I'm new to working with numeric data that must be very accurate.

We're converting data from MS Access though the original source data
is still in Dbase IV and I can use that. A typical number looks like:

0.7854350

In Access I linked to the SQL Server 2005 database and wrote INSERT
INTO blah blah SELECT FROM queries to import data from 1 or usually 2
tables. Because the new database in SQL Server will contain data from
multiple original tables, I used insert queries for this.

No matter what I do, such as running Access's convert functions like
CDec or CDbl or using the Float or decimal data types, one system or
another rounds these numbers to the closest integer. I am not a math
whiz, even remotely, so I'm lost when it comes to this. What is the
proper technique to move data with 7 decimal places from Access to SQL
Server?

thanks!

Don
3 Answers

Dave Patrick

3/30/2007 10:28:00 PM

0

What datatype are you using in SQL server? You probably want a fixed
precision DECIMAL datatype.

Numeric data types that have 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). numeric is functionally equivalent to
decimal.

p (precision)
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 of 38. The default precision is 18.

s (scale)
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.


--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.c...

<bbcrock@gmail.com> wrote:
> I'm new to working with numeric data that must be very accurate.
>
> We're converting data from MS Access though the original source data
> is still in Dbase IV and I can use that. A typical number looks like:
>
> 0.7854350
>
> In Access I linked to the SQL Server 2005 database and wrote INSERT
> INTO blah blah SELECT FROM queries to import data from 1 or usually 2
> tables. Because the new database in SQL Server will contain data from
> multiple original tables, I used insert queries for this.
>
> No matter what I do, such as running Access's convert functions like
> CDec or CDbl or using the Float or decimal data types, one system or
> another rounds these numbers to the closest integer. I am not a math
> whiz, even remotely, so I'm lost when it comes to this. What is the
> proper technique to move data with 7 decimal places from Access to SQL
> Server?
>
> thanks!
>
> Don
>

bbcrock

3/31/2007 3:07:00 AM

0

yeah, I glossed over the data types, this error occurs with decimal,
float, obviously real, int and others that can't handle large
numbers. I tested it with every numeric data type that sql server
express used and received the same rounding error every single time.

in the end I exported the data to raw "insert into" sql queries that
listed out the values instead of using an "insert into... select" type
query and that worked.

however every single numeric data type in SQL Server had this problem.

thanks,

Don

On Mar 30, 6:28 pm, "Dave Patrick" <DSPatr...@nospam.gmail.com> wrote:
> What datatype are you using in SQL server? You probably want a fixed
> precision DECIMAL datatype.
>
> Numeric data types that have 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). numeric is functionally equivalent to
> decimal.
>
> p (precision)
> 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 of 38. The default precision is 18.
>
> s (scale)
> 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.
>
> --
>
> Regards,
>
> Dave Patrick ....Please no email replies - reply in newsgroup.
> Microsoft Certified Professional
> Microsoft MVP [Windows]http://www.microsoft.c...
>
> <bbcr...@gmail.com> wrote:
> > I'm new to working with numeric data that must be very accurate.
>
> > We're converting data from MS Access though the original source data
> > is still in Dbase IV and I can use that. A typical number looks like:
>
> > 0.7854350
>
> > In Access I linked to the SQL Server 2005 database and wrote INSERT
> > INTO blah blah SELECT FROM queries to import data from 1 or usually 2
> > tables. Because the new database in SQL Server will contain data from
> > multiple original tables, I used insert queries for this.
>
> > No matter what I do, such as running Access's convert functions like
> > CDec or CDbl or using the Float or decimal data types, one system or
> > another rounds these numbers to the closest integer. I am not a math
> > whiz, even remotely, so I'm lost when it comes to this. What is the
> > proper technique to move data with 7 decimal places from Access to SQL
> > Server?
>
> > thanks!
>
> > Don

Dave Patrick

3/31/2007 3:17:00 AM

0

What precision and scale did you specify for the destination column?

--

Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.c...

<bbcrock@gmail.com> wrote:
> yeah, I glossed over the data types, this error occurs with decimal,
> float, obviously real, int and others that can't handle large
> numbers. I tested it with every numeric data type that sql server
> express used and received the same rounding error every single time.
>
> in the end I exported the data to raw "insert into" sql queries that
> listed out the values instead of using an "insert into... select" type
> query and that worked.
>
> however every single numeric data type in SQL Server had this problem.
>
> thanks,
>
> Don