Import data into SQL 2000 using dts

[ConvertFromOldNGs]

by Nath >> Tue, 5 Aug 2003 5:17:54 GMT

One of our clients uses GreenTree accounting system, which uses JADE. We have developed an application (Access 2000) for them that uses SQL server 2000 for it's data. A section of the application requires a list of Debtors that they can select from.
We've connected the required table via the JADE odbc driver 5.02.08.258 to the access app. But it's toooo slow, some of the users connect to the db's via VPN so I assume it would be even slower, therefore unusable.
So, I thought maybe we setup a DTS (Data Transformation Service) package in SQL 2000 and simply import the data (Debtors) once a day (the same method that we import exchange contacts into SQL 2000 for another purpose). Unfortunately there is an 'unexpected error message .....' and the data fails to import / export.

Does anyone know of this error?

Has anyone tried to do what we're doing?

Any suggestions / comments / solutions would be greatly appreciated.

TIA

Nathan

[ConvertFromOldNGs]

by Allistar Melville >> Tue, 5 Aug 2003 23:21:00 GMT

Hi Nathan,
Since yuo want a fairly thin connection I would suggest writing a DCOM component that uses the JOMAPI to retrieve data from Jade to use in an external system. Note that this is how the Excel -> Jade ("FREE") interface works.

Regards,
Allistar.

[ConvertFromOldNGs]

by Rob Owen >> Tue, 13 Jan 2004 21:34:23 GMT

Hi Nath,

I have tried what you are trying. Instead of selecting the table from the dropdown list in SQL SErver import you need to explicitly enter the SQL SELECT statement. Make sure all fields are capitalised exactly as they are in Jade.

You will probably encounter problems with money or decimal fields. I am waiting patiently for Jade to attend to this.

Regards.