Import E1 8.11 data into MSSQL

[Marty O]

Hi All,

I am currently developing an app that will need to import data from E1(8.11) Tools R(8.97) DB2 into MSSQL 2005.

The first problem, I am forced to use ODA. There are no alternatives at the moment.

What I want to do:

1. Create a linked server in MSSQL ==> done
2. Create stored procedures to extract data ==> done
3. Solve a problem as follows:

When I actually do run a query against the linked server, all works except for any tables that contain date fields. The problem (it would seem after testing with Visual Studio) is that all date fields within a table get their field name changed at random to something else like 00020 instead of ADUPMJ, thus resulting in an error in MSSQL stating that the field does not exist.

Interesting enough, in Crystal Reports 2008, no such problem.

What I have:
1. Windows 7 Pro 64 bit
2. JDE Fat Client (developper components installed)
3. ODA drivers, config, login, etc ...
4. MSSQL 2005 Latest SP
5. Visual Studio 2008 and 2010.

Has anyone encountered this before ? or has anyone have insight of what extra config I need for this ODA nightmare ?

Thanks

 

[Larry_Jones]

Marty,

I know you said you have no alternative to using ODA . . . but just want to make sure that this is a policy decision by the client - because its not a technical restriction - right?

 

[Marty O]

[ QUOTE ]
Marty,

I know you said you have no alternative to using ODA . . . but just want to make sure that this is a policy decision by the client - because its not a technical restriction - right?

[/ QUOTE ]

Hi Larry,

This is pure politics. Unfortunately, after 6 months trying, they will only allow ODA. I would much prefer direct ODBC to the AS/400 since I am more comfortable using this. But this is not my decision.

 

[Marty O]

Small update from the months...

I have discovered that if I run the exact same query in VS, the date fields and some numerical fields have their field names returned to the dataset as a sequential number instead of the field name. for example, if the first date field is the third column in the table, it will have 000003 as its column name, the fourth 000004 and so on....

What is so different from VS, MS SQL SRV and the Crystal Reports to make them react differently across all platforms...

I have tested this on MS SQL SRV 2005 SP3 & 4 on a 32bit machine and same results....

Any thoughts ?