Epicor 9 in Progress... Need to Query Progress

Epicor ERP 9
1

We are trying to run a script that will build a data warehouse out of Progress and put the results into SQL so we can use SSIS.

We have tried writing basic queries through the ODBC connection, and even something as simple as pulling an entire table, but we always get an errorimage

When I try to pull specific fields, I get a progress error that the field does not exist.

Has anybody out there been able to connect to Progress via ODBC to extract data?
The query we need to run is too complicated for a BAQ.

2

Yes… it can be tricky though. Some settings are different depending on whether you are 32/64 bit.

Can you make an ODBC connection on if you are logged directly to the server?

  • I usually get that working first.
    And then for client PCs, I usually start by running Netsetup… then tweak things as needed.
    \ServerName\epicor\oe101c\netsetup\setup.exe

There are some answerbooks on the old EpicWeb that will get you in the neighborhood.
And let us know if you are still having trouble.

Also… you may get errors connecting to some tables… (“too many indexes…” I think)
The WorkArounds2 reg entry fixes those.
Here is a sample for V8… last line… it would be the same for E9
Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\ODBC.INI\V8Live]
“Driver”=“c:\epicor\oe101b\bin\pgoe1022.dll”
“Description”=“OE”
“HostName”=“x.x.x.x”
“PortNumber”=“8350”
“DatabaseName”=“mfgsys”
“LogonID”=“sysprogress”
“StaticCursorLongColBuffLen”=“4096”
“UseWideCharacterTypes”=“0”
“EnableTimestampWithTimezone”=“1”
“DefaultIsolationLevel”=“READ UNCOMMITTED”
“ArraySize”=“450”
“DefaultLongDataBuffLen”=“2048”
“WorkArounds2”=“8192”

3

I used ODBC with Progress (OE 10.1b It think) back when we were on V8.

Does the ODBC connection setup on the computer pass the connection test?

There’s a chance you might need to run the ODBC32 setup on the client machine, instead of the 64 bit ODBC.

The 32 bit ODBC is at C:\Windows\SysWOW64\odbcad32.exe

There were also some “fixes” that we had to do. It involved creating a few registry entries. Here’s the contents of a REG file we used to run on clients so they could use ODBC connections to the V8 server.

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI]

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\MC_MfgSys]
"Driver"="\\\\usdataps00108\\progress\\openedge\\bin\\pgoe1022.dll"
"Description"="Live Company"
"HostName"="USDATAPS00108"
"PortNumber"="8350"
"DatabaseName"="mfgsys"
"LogonID"="sysprogress"
"StaticCursorLongColBuffLen"="4096"
"UseWideCharacterTypes"="0"
"EnableTimestampWithTimezone"="1"
"DefaultIsolationLevel"="SQL_TXN_REPEATABLE_READ"
"ArraySize"="50"
"DefaultLongDataBuffLen"="2048"

[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\ODBC Data Sources]
"MC_MfgSys"="OpenEdge 10.1B driver"


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\ODBC Drivers]
"OpenEdge 10.1B driver"="Installed"


[HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBCINST.INI\OpenEdge 10.1B driver]
"APILevel"="1"
"ConnectFunctions"="YYY"
"CPTimeout"="60"
"Driver"="\\\\usdataps00108\\progress\\openedge\\bin\\pgoe1022.dll"
"DriverODBCVer"="5.1"
"FileUsage"="0"
"Setup"="\\\\usdataps00108\\Progress\\OpenEdge\\bin\\pgoe1022.dll"
"SQLLevel"="1"
"UsageCount"="1"
"WorkArounds2"="40960"

usdataps00108 is the name of the V8 server that Progress resided on.
The “WorkArounds2”=“40960” is the fix we needed to manually enter.

Duplicate the entries in [HKEY_LOCAL_MACHINE\SOFTWARE\Wow6432Node\ODBC\ODBC.INI\MC_MfgSys] with updated name and Port setings for connections to other DB’s. For example:

[HKEY…ODBC.INI\MC_PilotComp]
“Description”=“Pilot Company”
“PortNumber”=“8380”
all other entries are the same as those in [HKEY…MC_MfgSys]

and add "MC_PilotComp"="OpenEdge 10.1B driver"
to the [HKEY...\ODBC Data Sources] section

4

Thanks. WE will try both of these. Registry fix seems like a good place to start.

We are doing this directly from the server.

5

scooped by a mere 9 minutes. :wink:

6

You guys are awesome. I’ve been banging my head for days on this.

The registry fixes did the trick. We are cooking with gas now!!

Thanks again for the help. Saved my bacon… and sorry for the idioms. :slight_smile: