Loss of Decimals Upon Link to Access

Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is base
d
on a table which has some data types as float. I created a view on the table
.
The view shows me units of a product divided by units of all products. The
results are expressed in the view as decimals. So, for example, .499857. Thi
s
is what I want. However, when I link the view to Access, all of my decimals
become zero. For example, .499857 becomes 0. I'm completely confounded. Any
suggestions would be fantastic! Thanks!If the SQL view is correct and you can use Query Analyzer to
view the results and they are as expected then this is more
of any MS Access issue. Make sure you have the latest Jet
service pack installed on the client.
But this is more of an Access issue so you would want to try
posting in one of the Access newsgroups. When posting your
question, be sure to include versions (version of SQL
Server, version of Access), what service packs you are
using.
-Sue
On Wed, 19 Apr 2006 08:11:02 -0700, Mike C
<MikeC@.discussions.microsoft.com> wrote:

>Hi. I have an Access DB that's linked to a SQL DB view. The SQL view is bas
ed
>on a table which has some data types as float. I created a view on the tabl
e.
>The view shows me units of a product divided by units of all products. The
>results are expressed in the view as decimals. So, for example, .499857. Th
is
>is what I want. However, when I link the view to Access, all of my decimals
>become zero. For example, .499857 becomes 0. I'm completely confounded. Any
>suggestions would be fantastic! Thanks!

Losing decimal places on Sql Svr to Sql Svr data flow

Hi,

I have a staging table that has a float [DT_R8] column that shows 4 decimal places. When I use an OLE DB Source referencing that table to go to an OLE DB Destination referencing a table with an identical column the data gets rounded to a single decimal place. Needless to say this is really messing with the values.

I can see the Scale property in the Advanced Editor for OLE DB Destination but I cannot change it. Same for the OLE DB Source.

Oh, and if I do an insert using SQL in Management Studio I have no problem getting the 4 decimal places in. For example:

Insert into table2
(Col1, Col2)
select Col1, Col2 from table1

Moves all the data and keeps the 4 decimal places.

How do I do this without losing those decimals?

Thanks
John
Do you have the SSIS datatypes set to DT_R8? Double click on the green line coming out of the OLE DB source and in the metadata tab, what's the data type set to?

(Just asking the perhaps obvious question for completeness.)|||The datatype for that column is DT_R8 in the OLE DB Source (both External and Output columns), OLE DB Destination (both External and Input columns) and in the green line metadata.

John
|||Works fine for me. Maybe you've got some old metadata stuck somewhere. Try putting a Data Viewer on the pipeline at various points (if there are various points) to see what it looks like in flight. Maybe you need to delete and recreate some components or the whole data flow to get it straightened out. What method/query are you using to pull the data from the source? What type of connections are you using?
|||While recreating the data flow, I checked the view and it appears that the problem is happening just before the package executes.

Thanks for all the help.

loosing values when load from text file

When loading a table in a data flow from a text file that contains non-null float values, I am seeing erratic and inconsistent results. I am presently using SQL Server Destination in a data flow.

- With low volumnes of data, less that 50,000 rows, no problems

- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package. If I run is directly (right-click and click on Execute), I get the expected result.

But if I use SQL Server Agent to run the package, half of the values are lost and nulls are loaded instead. I have inspected the into text file and there are few rows with null for the column.

Any help would be appreciated!

Greg

>>>- With low volumnes of data, less that 50,000 rows, no problems

Did you have success using both BIDS and SQL Agent for the 50K load?

>>>- But with higher volumnes, 2,000,000+ rows, I get different results depending on how I run the package.

While using SQL Agent did you chose the Command subsystem and use dtexec or the SSIS subsystem?