ODBC on-line Help v2.0
ODBC on-line Help v3.0
ODBC Driver Downloads
UTB Downloads
UTB on-line Help

Please ensure that you've throughly researched the on-line helps and the FAQ's below prior to contacting our help desk.

Question Answer
Install Error - This installation is a server install and can only be run on a windows server operating system. Ensure you have the correct version of the driver, they are operating system specific and the single user (SU) should be used when installing on a desktop PC. The multi-user (MU) version is for windows server systems only.
Install error - This installation is a workstation only install and cannot be run on a windows server operating system. Ensure you have the correct version of the driver, they are operating system specific and the single user (SU) should be used when installing on a desktop PC. The multi-user (MU) version is for windows server systems only.
Install error - CPF0006 - Errors occurred in command - Unspecified error Install error: Microsoft OLE DB Provider for ODBC Drivers Error: - 2147467259
[IBM][iSeries Access ODBC Driver][DB2 UDB]CPF0006 - Errors occurred in command. Unspecified error.
Answer -- This error usually occurs when the user has no authority to execute DSPHDWRSC on the host iSeries.. The driver is trying to establish the Serial Number of the iSeries host which is needed to validate the license of the drivers installation.
Refer to your iSeries system administrator and ask them to set the appropriate *USE authority for the user to this command.
CPF0006 - Errors occurred in command - Failed to set library list Install AND/OR Run Time Error -- Error location: 100
Error code: 0x80004005
Error message: UES erpDirect ODBCRDA error: Resource not available - Failed to set library list Message:
Answer -- This error usually occurs when the user has no authority to do a CHGLIBL on the host iSeries.. The driver is trying to correctly set the library list based on the log on credentials entered and the environment requested., to do this the driver issues a CHGLIBL command remotely for the job and this command has generated a error. Refer to your iSeries system administrator and ask them to set the appropriate *USE authority for the user to this command.
MS Access "#Deleted" Errors with Linked ODBC Tables

Caution: The information in this document is provided on an as-is basis. #deleted is a Microsoft Access application error message that can have many underlying causes. Unity Helpdesk supports jdeDirect & erpDirect ODBC drivers but not Microsoft Access. Please contact Microsoft for additional information.
When you retrieve records in a linked ODBC table, each field in a record contains the "#Deleted" error message.
Microsoft Access may invoke the ODBC cursor library when using ODBC linked tables. The typical behaviour is to query the unique keys on the table (SQLStatistics), select the unique key fields and fetch all rows to the PC, then select records as needed by specifying the unique key on the where clause. Access seems to pop up the #deleted error message when a select is done by unique key but no record is retrieved.

Please refer to the following to establish the exact cause of the #deleted in your environment.

"Incorrect Syntax near column name" message when connecting to EnterpriseOne environment from Thin-client PC. Database is SQL Server. Please verify that the SQL Server DSNs for the environment were created with the option "Use ANSI quoted identifiers" checked. You can modify an existing SQL Server DSN by choosing to configure the DSN in the "ODBC Data Source Administrator" and the option appears on the 3rd screen in the "Microsoft SQL Server DSN Configuration" wizard.
Install error - You do not have full administrator privileges on this machine The install will write entries into the system registry and create default DSNs for you to use - this will fail unless you are logged on with local administrator rights to the PC you are installing on.
Install error - MDAC 2.6 or greater not found ! please fix this issue and try again. Unity ODBC drivers need MDAC version 2.6 of higher, this program is freely available for download from the Microsoft website, Try the Unity install again once you have downloaded and installed the correct MDAC program.
MDAC download site - "http://msdn.microsoft.com/data/mdac/default.aspx"

If the Unity installer continues to refuse to install, please review this other FAQ
MDAC 2.6 (or higher) IS installed, but the driver still refuses to install This is a common MDAC problem that Microsoft could document better. We guess they hope it will just quietly go away.

Here's what happens: You have a Windows XP machine (which comes with MDAC
2.7 built-in) or a Windows 2000 machine which had MDAC 2.7 installed on it. You could even have this problem on Windows ME upgraded to MDAC 2.7. The key is that these operating systems all have Windows File Protection (also known as System File Protection), which forcibly blocks newer system files from being replaced by older ones. Good install programs should never do that anyway, but they sometimes don't check versions correctly, so the OS enforces it now.

Along comes somebody with an old Windows app. The app itself runs fine on all the recent versions of Windows, and it has no problem running on MDAC 2.7, but the app's installer includes an old version of MDAC, and you can't install the app without letting it attempt to install its old MDAC, which "should not" be a problem because at the very least, Windows File Protection will ensure that your newer MDAC files don't get replaced by older ones.

And that's exactly what happens. You launch the old app and everything works fine, so you move on with life. Here's the gotcha: there is no such thing as version checking on registry entries, nor is there such a thing as Windows Registry Protection. As long as you're installing software with administrative rights (which is what most software installations occur under), the installation can write and overwrite registry entries. Old MDAC installers update the registry to indicate what version they just finished installing. New MDAC installers do the same thing, but with one key difference: the newer MDAC installers actually go back and make sure that their files got installed before they update the registry. The old MDAC installers don't actually verify that they installed the files they "thought" they did. So if Windows Files Protection prevents old MDAC from replacing current files, old MDAC never realises it, so old MDAC merrily puts the old MDAC version number in the registry.

Six months later, you've forgotten all about the old app which tried and failed to install old MDAC, and you go to install something new like the Unity ODBC drivers for JD Edwards. The only "proper" way to determine MDAC version is the MDAC Component Checker, but there's no practical way for any installer to incorporate Component Checker into an installation, so most installers which need to check for MDAC version simply go and look at the registry. It finds an old MDAC version, which was put there six months ago.

The Unity ODBC drivers installer refuses to proceed, there's no obvious clues which would lead you to associate your problem from today with an app install six months ago (especially given that the app worked fine), so people who run into this problem wind up tearing out hair, calling support, reformatting hard drives, etc.

So how to fix? Look at the FullVer and Version entries under
HKEY_LOCAL_MACHINE\Software\Microsoft\DataAccess. Make them say 2.7 if you know you've got 2.7. If you want to use Component Checker just to be sure, download it from the Microsoft 
Component Checker page.
Install error - No environment found for user [ nnnn ] using library [ sec-lib-name ] The installer is unable to establish a list of valid World environments, this could be because you are not authorised to any environments or there are no environments configured or that you selected a incorrect security library during the previous step.
Environments are listed in the F0093 file, please check that you are authorised to at least 1 environment in this file. Note the name of the library where this file resides and ensure that this library name is selected when selecting the security library.
Refer to a system administrator if you are unable to resolve this matter
Install error - No security library found on server [ ip-address ], please enter server with a valid security library. The JDE World security library cannot be found on this AS/400, when looking for the security library, the installer is searching for libraries that contain the following 3 files - F0092, F0093 & F0094. These files cannot be found anywhere on this AS/400, therefore the security library cannot be established.
If these files do exist on this AS/400, you may not have authority to them, please ensure that the user id entered during the install process has READ access to these files and the library they reside in.
Refer to your system administrator if you are unable to resolve this issue.
Install error - IBM ODBC driver. CWBCO1004. Remote address could not be resolved. IBM client access ODBC driver communication link failure. CWBCO1004. Remote address could not be resolved. Microsoft OLE DB provider for ODBC drivers error: 2147467259.. The IP Address used is invalid - no system or device available at this IP Address, please enter a valid IP Address belonging to your AS/400. Refer to your system administrator to establish the IP Address if you do not know it.
Install error - IBM ODBC driver. PWS0007 - operation result set not found.

IBM client access ODBC driver communication link failure. PWS0007 - operation result set not found. Microsoft OLE DB provider for ODBC drivers error: 2147467259.. SQL statements to this AS/400 are failing, this is usually caused when the PC does not have the latest Client Access (iSeries Access) patches installed. Download the latest patches from the IBM website http://www-03.ibm.com/servers/eserver/iseries/access/sphist.html. You need to ensure you have the latest service pack installed for the version of Client Access or iSeries Access installed on the PC with the Unity ODBC drivers.
Please refer to your system administrator for information on the current version of Client Access installed and which service pack to download.

Install error - IBM ODBC driver. CWBSY0002. Password for user [ nnnn ] on system [ ip-address ] is not correct. IBM client access ODBC driver communication link failure. CWBSY0002. Password for user [ nnnn ] on system [ ip-address ] is not correct Microsoft OLE DB provider for ODBC drivers error: 2147467259.. The password used is invalid for the user id, please retry using the correct password for this user id. Refer to your system administrator to obtain the valid password for this user id if you do not know it.
Install error - IBM ODBC driver. CWBSY0001. User id [ nnnn ] on system [ ip-address ] does not exist. IBM client access ODBC driver communication link failure. CWBSY0001. User id [ nnnn ] on system [ ip-address ] does not exist Microsoft OLE DB provider for ODBC drivers error: 2147467259.. The user id given does not exist on this AS/400. Please retry using a valid user id for this AS/400. Refer to your system administrator to obtain a valid user id for this system if you do not have one.
Install error - IBM driver. CWBCO1003. winsock error, function returned 10061, [ip-address] 
IBM client access ODBC driver communication link failure. CWBCO1003. winsock error, function returned 10061, [ip-address] Microsoft OLE DB provider for ODBC drivers error: 2147467259.. The IP Address given does not belong to a AS/400 on this network, whilst it is a valid IP Address of a system - it is not a AS/400. Please submit the correct IP Address.
Install error - Error, you must uninstall your existing world driver before proceeding. erpDirect ODBC drivers for World is a rebranded version of the previous product jdeDirect and are one of the same other than for the name change. Both products are mutually exclusive and one cannot be install whilst the other is installed due to component conflicts. You need to completely uninstall one - including removing the directory and registry entries before you can install another. The default install directories can be found under program files, Unity Enterprise Solutions or UES Limited. The registry entries are under HKEY_LOCAL_MACHINE, SOFTWARE, UES Limited, ODBC Driver or HKEY_LOCAL_MACHINE, SOFTWARE, Unity Enterprise Solutions, jdeDirect
Extreme caution should be taken when editing registry entries, and should only be carried out by a system administrator.
Install error - Incorrect client access version or not installed at all. For windows 98 ensure v4 installed, for all other systems ensure v4 or higher installed. You need to install the client access ODBC components, these are APIs that are required to enable communication to your AS/400 system and are license free from IBM. Take note that with windows 98 you will need to install client access version 4 and with windows NT, 2000, 2003 client access version 4 OR higher. Client Access version 3 is NOT supported and may break your system.
Gateway service stops when I log of the PC. Do not install or configure the Gateway Service via Terminal Services (RDP), instead use the Gateway PC's console to install and configure the service. Using RDP may cause the Gateway service to stop operating once you log off the Gateway PC.
How to license (get an activation code) for your Unity ODBC driver

jdeDirect -- Please refer to our on-line help here:

erpDirect - Please refer to our on-line help here:

How to get metadata (schema) information from the driver when using ODBC .Net Data Provider in ADO.Net 1.1

The ODBCConnection class in System.Data.ODBC Namespace does not provide members to retrieve the schema information for the current connection session to the ODBC driver. To get the schema information, please follow one of the options below:

1. Upgrade to ADO.NET 2.0. ADO.NET 2.0 has added support for retrieving Schema information using the GetSchema() member of ODBCConnection class. For details, visit
2. Use the System tables available with your connection to the driver. This is very much like querying the System Catalogue directly when connecting to the database using the native driver.

The available system tables are:

Below are some sample queries that you can use:
To get the table schema:

To get the column schema for table F0101:

To get the statistics information (i.e. Indexes, keys etc):

How to connect to the Unity driver using a DSN-less connection ? Use the following connection string for jdeDirect Oneworld ODBC driver:
Driver=[jdeDirect OneWorld ODBC Driver];Database=oajde;UID=;PWD=; LTN=0;ALTN=0;LCN=0;ALCN=0; DD=1;SCDO=1;UDC=0;UDCD=1; UDCD2=1;UUTC=0; ENV=;SYSDB=;SYSDBO=;GATEWAYSVR=;GATEWAYPORT=;

Use the following connection string for jdeDirect World ODBC driver:
Driver=[jdeDirect World ODBC Driver];Database=oajde;UID=;PWD=; LTN=0;ALTN=0;LCN=0;ALCN=0;DD=0;SCDO=0;UDC=0;UDCD=1;UDCD2=1; UUTC=0;A81DD=1; ENV==;SVRLIB=;HOSTNAME=;

Use the following connection string for erpDirect World ODBC driver:
Driver=[erpDirect World ODBC Driver];Database=oajde;UID=;PWD=; LTN=0;ALTN=0;LCN=0;ALCN=0;DD=0;SCDO=0;UDC=0;UDCD=1;UDCD2=1; UUTC=0;A81DD=1; ENV==;SVRLIB=;HOSTNAME=;

LTN - Long Table Names, set it to 1 or 0 to imply On or Off.
ALTN - Append Long (or Short) Table name. Set it to 1 or 0, complementary to LTN.
LCN - Long Column Names, set it to 1 or 0 to imply On or Off.
ALCN - Append Long (or Short) column name. Set it to 1 or 0, complementary to LCN.
DD - Enable DD column Screen names, set it to 1 or 0.
SCDO - Enable system code description overrides, set it to 1 or 0.
UDC - Internal switch , always set to 0.
UDCD1 - Enable UDC Description Column 1, set it to 1 or 0.
UDCD2 - Enable UDC Description Column 2, set it to 1 or 0.
A81DD - A81 based Data Dictionary or Not, set to 1 if yes or 0 if no.

These settings match the settings that you would see on the DSN Configuration when creating a datasource to the driver using the ODBC Data Source Administrator application.
Why doesn't a new or changed View, File or Logical display in the driver ? The Unity ODBC driver caches the schema locally, to reflect any change made to the system that affect the schema - File, View or Logical definition, you need to reload the local cache. Do this by clearing the cache - the empty cache will cause the driver to reload from the system when you next use the driver.

Clear the local cache by going to:
Start, Programs, UES Limited, your driver version, Control Panel.
From the driver control panel, select the icon "Driver Controls"
From the driver controls, select the tab "Caching"
In the caching tab, click the button marked "Clear Schema Cache"
Save your settings and exit the application.

Restart the driver and your new or updated system schema will be loaded to the local cache.
MS Access - Scaling of decimal value resulted in data truncation. Error 3759

This is a MS Access error caused when 2 or more decimal columns are being totaled and where the result could cause an overflow to the value to the right of the decimal point.
Most other applications will simply round or truncate the result without a hard halt, unfortunately MS Access does a hard halt and the query ends.
A workaround to this problem is to use the CDBL Function

SELECT SUM[GBAN07] from F0902
Also, ensure that you have the latest version of the driver installed and at least higher than

How are ZERO dates processed against JD Edwards World The problem comes about because JDE allows zero dates to get into the database, All PC applications and SQL Syntax do not understand zero dates, they understand a proper date or a NULL date.. And, the AS/400 does not do NULL's - there has to be a value.. In the case of a real AS/400 date field, the default value for a zero date is 0001/01/01, but as this is not a real date field the default being written is a zero - perfectly valid in a normal AS/400 numeric field but totally invalid when we attempt to convert that to display as a date field.

A JDE date of 000001 is converted to 1900/01/01 by the Unity driver, so logic points to a zero being 1899/12/31 - so in the next release of the driver all zero dates will display as 1899/12/31 leaving you able to perform date selections and arithmetic on zero dates.. in the mean time you need to use a workaround, something similar to this ::

if IsNull([F060116_Employee_Master.YADT_Date_Terminated]) then "00/00/00"
Else ToText([F060116_Employee_Master.YADT_Date_Terminated])

However, this was designed for Crystal, and each application will have its own syntax for this kind of conversion.  This problem affects all versions of the jdeDirect & erpDirect ODBC driver up to and including v2.0.4.4
Why do I need to have QZDASOINIT running to make the Unity driver work ?

For AS/400 & iSeries database's, the Unity driver requires the QZDASOINIT jobs to be running. The QZDASOINIT handles the server side of ODBC requests, so client side ODBC will fail to execute without the server side running.

There is scant information on the IBM site about QZDASOINIT. If you search for "QZDASOINIT tuning" on
http://www.as400.ibm.com you get only 5 hits! There is some information in the IBM JDE OneWorld guide. In about 3 pages it describes what the QZDASOINIT job is and the fact that you need to setup the pre-start pool to accommodate your expected concurrent user base.

Our experience tells us the one area where you can tune this job is memory. If you put the QSERVER subsystem into its own memory pool it helps reduce fragmentation and gives generally better performance. The other major gain can be found by using SETOBJACC. This allows you to load certain tables in RAM, this can give massive gains to the performance of some queries.

Overview of ODBC and QZDASOINIT.
ODBC talks to the QSERVER subsystem, (QUSRWRK on v5)
An OBDC connection causes a pre-start job, QZDASOINIT, to be used.
The QZDASOINIT job will stay alive until the connection is closed at which time the QZDASOINIT job is returned to a pool, and then reused by a subsequent connection.

Adjusting Pre-start Jobs.
You can change how jobs are started and reused by using the Change Pre-start Job Entry (CHGPJE) command.
On systems serving a lot of ODBC connections increase the number of pre-start jobs available and manipulate the pool thresholds.
Create a separate memory pool and change the subsystem to use this pool. Size the pool to eliminate/reduce page faults.

Turn on Expert Cache.
The iSeries has the ability to cache data.
Use WRKSHRPOOL to examine the memory pools on the AS/400, make sure the base pool is set to *CALC, this turns on object Expert Cache.
Create a separate memory pool and load frequently used Files into the pool using the SETOBJACC command, This allows the File to be maintained in RAM.
(Note -- specific to the Unity ODBC drivers. SETOBJACC is a way to cache whole Physical Files in memory. Depending on available RAM you can cache just about any table. The F0911 would NOT be a good candidate given its size. But UDC tables F0004/F0005 and perhaps even the address book tables would be good candidates if frequently used in reports)

Tune the Subsystem.
Set up a separate pool for QSERVER subsystem.
By default, the QSERVER subsystem runs in the base memory pool.
Create a separate pool and adjust the subsystem description to run in this new pool.
Adjust the pre-start job entries so that they run in the new pool.
This segregates the memory used by ODBC, JDBC and OLEDB into a separate space and reduces contention and fragmentation.
Make sure you set the pool to *CALC with the WRKSRHPOOLS command so that it uses expert cache.

How to connect jdeDirect ODBC to a networked E1 8.10 standalone install

To enable testing of the "thin client mode" of the jdeDirect ODBC driver without actually having a full JDE OW/E1 install anywhere on the network - working only with a standalone install.

Standalone PC Steps --
1, Install the E1 8.10 standalone (including data) using the media from Oracle, ensure that it is fully operational and works in such that you can log in and navigate around the application.

2, UPGRADE the installed MSDE database to a full SQL Server Data Base. (Refer to
http://support.microsoft.com/default.aspx?scid=kb;en-us;815013 )
3. Open the SQL Server Enterprise Manager, check to see if you have "SERVERNAME\JDELOCAL" added to your SQL Server Group. If not, add it by registering a new server under the SQL Server Group.
4. Open the F98611 table under PSSY810 database. Update the OMSRVR field to change the value "Local" to the actual name of the computer on ALL "Local" records.
5. Create 2 database user accounts, DEMO and PSFT. Make sure that these user accounts has read access to all the databases under the SERVERNAME\JDELOCAL instance.
6. Download and extract the jdeDirect Standalone driver setup files to a temporary folder. Navigate to \program files\UES Limited\ODBC Driver\jdeDirect\util and run the StandAloneGenerator.exe (follow instructions on Screen).
7. Log on to E1 and create a new role, say UNITYSYS, and assign this role to users DEMO & PSFT.
9. Go to C:\winnt or C:\windows and make a copy of the jde.ini and call it jde_jdeDirect.ini.
10. Open the jde_jdeDirect.ini, and under the [DB SYSTEM SETTINGS] section, change key "Server" to "SERVERNAME\JDELOCAL". Also ensure that the "Database" key is set to "PSSY810" and "Base Datasource" key is set to "System Local"
11. Place jde_jdeDirect.ini to a network share for other PC's to be able to access it. Log out of E1 if not already done so.

Networked PC Steps -
1, download jdeDirect Single-User or Multi-User driver depending on whether you are installing this on a Workstation or a Server
2, Run the ODBC driver setup program. Choose the defaults during the installation up till when you see the "Configure E1 Connection" screen. On this screen, check the "pt" check-box (on the bottom-right corner). A file/browse dialog will show up, navigate to and open the_jde_jdeDirect.ini file created in step 9. Click Next.
3. Enter one of the user names/ passwords (database) created in Step 5, and enter DEMO810 for the Environment. Click next.
4. Click finish on the next screen and proceed with the installation as directed on screen.

MS SQL Server error -- Cannot resolve collation conflict

Introduction --
"Collation" defines the way in which characters are stored in a SQL Server database. It define the rules that govern the sort order and the code-page for non-unicode databases. In SQL Server, collation can be specified a various levels, i.e. right from the collation for a database to the collation for a particular column in a table. For more details on collation, visit

Symptom --
During the initialisation of a connection to the JDE system, the jdeDirect ODBC driver performs several join operations on various System and Control tables. If these tables are stored in SQL Server databases that have been built using different collation settings, then the SQL Server rejects these join operations with a "Cannot resolve collation conflict" error message.

Resolution --
This problem can be resolved by changing the collations for the SQL Server Databases, such that all the databases that store the System and Control tables are set to the same collation value, thus avoiding a collation conflict.

Workaround --
A Workaround for "Cannot resolve collation conflict" error when using jdeDirect ODBC driver with SQL Server databases:
After installing the jdeDirect ODBC driver, open the OPENRDA.INI file that is located at \UES Limited\ODBC Driver\jdeDirect\bin\iwinnt and under the [Client] section, create a new key and set its value as follows:


Save and close the OPENRDA.INI file and restart all applications that may be using the jdeDirect ODBC driver.
This setting will force the jdeDirect ODBC driver to treat your database as distributed (as if held on different servers) and perform the join operations on the system and control table internally and therefore not causing the collation conflict error.

IM001:[ uesODBC ][OpenRDA ODBC]RIGHT OUTER JOIN is not supported. Error opening rowset.. IM001:[ uesODBC ][OpenRDA ODBC]RIGHT OUTER JOIN is not supported.  The Unity ODBC drivers do NOT support Right Outer Joins - we have no current plans to implement support for this. However, this post will be updated should our plans change.
JD Edwards OneWorld XE StandAlone Table Generator Fails If you wish to run the jdeDirect ODBC driver against your stand alone version of the JD Edwards OneWorld ERP system you will need to run the SATableGenerator module shipped with our standalone driver install.  This is because the standalone version of OneWorld does not ship with a full set of Central Objects tables. The jdeDirect ODBC driver operates by retrieving information from these tables and presenting the OneWorld data model dynamically. When running a full version of the jdeDirect driver against a production OneWorld database these tables will already exist.

In order for the driver to function with OneWorld standalone the SATableGenerator must be used to generate the F98710, F98711, F98713 tables into your local standalone database.  However, with the XE version of OneWorld StandAlone there is a problem because all of your data is stored within a MS Access database. This database is actually shipped using a very old version of the access database (Which is now no longer supported by Microsoft themselves).  This will cause the SATableGenerator to fail and the F98710, F98711, F98713 tables will not be copied.

In order to get around this problem you first should make a backup of your original OneWorld StandAlone database. This can usually be found at C:\B7\DEMO\data\jdeb7.mdb

Once you have made a backup copy of this database then attempt to open your OneWorld StandAlone database using MS Access. You will be prompted to upgrade your database to a newer version. You want to do this. Follow all on-screen prompts to upgrade your database to the new version and then ensure that you can still open OneWorld and log in successfully.  If you have completed this and everything is ok so far, you must now run the SATableGenerator and your new upgraded database will be altered to include the required Central Objects tables for the jdeDirect ODBC driver to function.
SQL Server locks when using MS Access linked tables

Users may notice that their SQL Server database is reporting locks when using ODBC linked tables with MS Access, resulting in some JDE transactions failing.

Please refer to this Microsoft KB article that explains the situation and advises on how to reduce the number of locks.