Using OpenRowSet Instead of DTS/SSIS or Data Stage

[smedvid]

I have a slight issue with my attempt to not use DTS/SSIS or 3rd party ETL (Data Stage). Basically, IT will no longer support DTS/SSIS and want to force developers to offshore ETL work using a product called Data Stage... So I am attempting to find alternatives for Import/Export...

For Import; it appears I can use OpenRowSet; with one issue. The DTS previously imported a field (rtecur) from MS Access as a true number (i.e. 9.9899975). When I use OpenRowSet; it appears to round up and truncate the values (i.e. 9.99).

How can I capture the true number using OpenRowSet?

My guess is some type of format statement perhaps?

INSERT INTO dbo.tblData
(ID,age,rtecur)
SELECT ID,age,rtecur 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      '\\TestServer\data\00_Test_Data.mdb';
      'admin';'',tblData);

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[bborissov]

What is the precision of this:

SELECT ID,age,rtecur
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
      '\\TestServer\data\00_Test_Data.mdb';
      'admin';'',tblData) Test

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[smedvid]

MS Access field is Number Double 4 decimals...

Steve Medvid
IT Consultant & Web Master

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...