sql server dts: Hello,

I have tried to export a table that contains fields of datatype decimal,
however, when I export the data to MS Access, the values to the right of
the decomal point are never present. The number is rounded to the
nearest whole number.

I tried creating the target table in MS Access, and even changed the
field type in this table to decimal, but that didn't do the trick. I
haven't tried select convert(varchar(10), column1) yet.

Also can anyone please tell me how to export data from a SQL Server
table to a text file? I tried doing this but each time I tried to create
the target table, using the DTS Designer, I received an MMC execption,
which shut down the DTS designer.

Any help would be greatly appreciated.

Thanks,

Sam





*** Sent via Developersdex http://www.developersdex.com ***

Hi Sammie,

I try to simulate your problem in my PC, and it works fine.
I create a table in my SQL :
CREATE TABLE [dbo].[Table3] (
[A] [char] (10) NULL ,
[B] [decimal](18, 5) NULL
) ON [PRIMARY]
Don't forget to define the 'scale' for your decimal, or it
won't store the values to the right of the decimal point.
And then I populate the table properly.
Then create a blank Access database.
Then I use the import/export wizard to transfer my table
from SQL Server to Access. I let the DTS create the new
table in Access. And the result is just fine.

You can create the table manually in your Access, but
don't forget to define the scale too for your decimal
column.

I even try to create a table like this
CREATE TABLE [dbo].[Table3] (
[A] [char] (10) NULL ,
[B] [decimal](38, 30) NULL
) ON [PRIMARY]
Access only support maximum to 28 in the scale of decimal
data type. The DTS wizard needs some modifications in
creating the Access to define just the 28 in the scale, or
it will generate an error. But after you do this, the data
transformation will work just fine with the 30 digit scale
converted to 28 digit in Access.

If this don't work, I assume there's some problem in your
DTS, because I also have no point in your problem for the
text file, it's just work fine with mine. I'll check for
other alternatives later.

Hope it helps :)
Regards,
Jono Indrawijaya - MCDBA
PT. eBiz Cipta Solusi - Microsoft Certified Partner
Indonesia

[quoted text, click to view]