Need to Archive a Progress V8 DB

Vista/Vantage 8
1

We made the move to E10 back in November, and have left V8 up for people to access historical data. The E10 was a clean install, with only the minimal data exported from V8 and imported (via DMT) to E10.

Now IT wants to take the V8 server down (it is running on Winserver 2003).

But before they do, I’d like to archive some key tables, in a way that I could run queries against - should someone need something from our old V8 db. Entire tables will be exported. The pseudo SQL query would be:

SELECT * 
FROM PUB.Part Part_0

I was going to just run ODBC queries against the Progress V8 DB, from right inside Excel. Making 1 excel file for each table I exported from V8.

But many of the tables have more columns than the OpenEdge ODBC drive can handle. And some data in fields that exceed the db’s field width. Either of cases makes an ODBC connection fail.

Is there a simple (i.e. free :slight_smile: ) tool to export tables from the Progress DB, to another format like SQL tables?

Thanks in advance,
Calvin

2

You could just do a dump and load of your progress DB into SQL

1 Like
3

Any docs or white papers on how to do this?

4

You can usually create view(s) and/or adjust field widths.
Epicweb used to have answerbooks or let me know if you’re interested & I can look for my old copies.

5

Bruce - A link to that answerbook would be greatly apprectiated

All - Any idea why the Export options in the Porgress Data Admin tool would be disabled?

Ive tried various ways of making the connection (multi-user, single user) With DB and App services runing or stopped, etc…) but no luck getting the Export Data menu item to be enabled.

6

I realize this is not what you specifically asked for but if they just want to eliminate a physical server you could virtualize what you have now using P2V software which is provided free with most virtulization softwares. This way you would have it available exactly as it is now to spin up on occasion but not take up physical space.

If the goal is to shut down the server and never spin it up again and only access specific data this obviously won’t work.

Neil

7

The “server” already exists as a virtualization. IT’s issue is that it is running on on an unsupported OS (Winserver 2003). And while no one would be using it -thus meaning they don’t need to “support” it - they have policies about obsolete OS’s running. Because of security flaws that could be exploited in the older OS’s.

8

Create an ODBC Connection to a V8 SQL Empty DB… you can download the Training DB for V8 SQL and just empty the tables if you don’t have one available.
Then take the MFGSYSSH schema holders restore it and use Data Admin Tool to do a Dump and Load from Progress to the new Schema Holder / SQL Db

9

Likely a restriction caused by the licensing with OpenEdge 10.1b. Do you have an Epicor 9.05 install somewhere with a 32bit install of 10.2a? If so, I would try that as we delivered more licensed modules with OpenEdge at that version.

10

You could do a “full database” conversion to E10 and add it as a “Test/Archive” instance in your current environment.

Chances are fairly good that the info you are looking for will be available and they would use a familiar interface (E10) to access it.

We have a “SandBox” environment where we keep a “Test” as well as “YearEnd” copy available to spin up for reference

Since these are not for “Production” use and are only spun up for reference on occasion you should not run afoul of licensing.

Neil

11

Are there free tools to do this conversion? Or is this a job only Epicor can do ($$$) ?

12

These are free. Instructions are available from Epicor and lots of other threads on this list for help.

This will require some work by your IT department but it is certainly doable.

We are in the process of converting to E10 and I have done 1 conversion so far.

Neil

13

You can do it, but there is no direct path to 10, so you’lld have to go to 9 , 905 and then 10

14

Link…
I loaded 3 docs on GDrive & shared.

  • DBVisualizer connections
  • DBVisualizer adding a view
  • Answerbook - field width

Let me know if you can’t access them and/or if you want to share your email I can send copies too.

[Answer Edited by Moderator]
https://drive.google.com/file/d/0B10plDpKTB1ZWFVHNW1TNFBJaUE/view?usp=sharing_eixpa_np&ts=588fbd82
https://drive.google.com/file/d/0B10plDpKTB1ZYUFoZE1pWVcyWTQ/view?usp=sharing_eixpa_np&ts=588fbddf

And check out answerbook 994BRK on EpicWeb.
If I remember I couldn’t follow those instructions verbatim. Were pretty old, good enough to get it figured out.

1 Like
15

One could also consider using a SQL linked server connection to the Progress database via ODBC, and create series of select * into newdatabase.dbo.tablenamegoeshere from [linkedserver].mfgsys.pub.tablenamegoeshere commands

If one were to run a query like so against an V803410 SQL database

SELECT 'SELECT * INTO ' + TABLE_NAME + ' from [linkedservername].mfgsys.pub.' + TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME not like 'IM%' --IM tables aren't going to be queried after this, right?
AND TABLE_NAME not like '%_SEQ%' --exclude sequence tables 
ORDER BY TABLE_NAME

they would get something like the attached (where linkedservername could be replaced with the name of the linked server connection created)

803_sql_select_into_linked_server.txt (56.6 KB)

NOTE: the DSN bit type and the SQL server bit type need to match. So, if your SQL server instance is x64, you’d need a x64 bit OpenEdge driver along with a x64bit DSN on your SQL server to connect to the Progress database.

16

if you are getting column width issues while using ODBC, there is a process to set the ODBC column width to the Progress column width from the PROENV. Epicor Support KB 13030MPS has the details.

17

@bordway ,
Unfortunately we can’t allow publicly sharing Epicor documentation directly on the forum. Answer Books are Epicor’s property and only available to paying customers. Feel free to share links to the EpicWeb location of the answerbook

1 Like
18

I’ve fixed the column width issue using the dbtool in the ProEnv on my V8 Progress server.
So now using ODBC shouldn’t be a problem.

Nathan - that SQL “SELECT * INTO …” method looks promising.

I assume I have to have the tables already setup (keys specified, fields defined, etc …) in the destination SQL. I tried following a tutorial on Progress’ knowledgebas for doing it, but my Progress DB being on WinServer2003 threw a wrench into it.

19

You do not. Just start with an empty SQL database (created by right-clicking in SSMS and new database; no objects beyond that) and the select into will create the tables and columns for you in the destination database; there just won’t be any other SQL objects like indexes, primary keys, constraints (etc) created through it. In the mfgsys803\server\sql folder(at least in E905 we had one there), there should be an .sql file that allows you to create a base Vantage 8.03 SQL db and you could take the index creation bits from that to apply to your resultant SQL database after the data is moved.

20

My current problem is creating the linked server.

My Prog DB (OE 10.1b) is on a 32 bit WinServer2003 box. The SQL DB I’m trying to copy the tables to is a WinServer 2012R2 box.

I created a System DSN ODBC connection on the SQL box. But its a 32 bit connection.

Triedsetting up the linkedserver using

sp_addlinkedserver @server = ‘MC’, @srvproduct=N’‘, @provider=N’MSDASQL’, @datasrc=N’MC_MfgSys

Which executes okay, but when I try to query via that linked server, I get:

OLE DB provider “MSDASQL” for linked server “MC” returned message “[Microsoft][ODBC Driver Manager] The specified DSN contains an architecture mismatch between the Driver and Application”.

FWIW the System DSN on the SQL box is using the 32 bit OpenEdge 10.1B driver. Here’s the “About” info from the DSN:

Will I ever be able to make this connection? Or will I have to find a 64 bit version of the OE 10.1B ODBC driver?