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 ) tool to export tables from the Progress DB, to another format like SQL tables?
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.
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.
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.
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.
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
aidacra
(Nathan your friendly neighborhood Support Engineer)
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.
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
aidacra
(Nathan your friendly neighborhood Support Engineer)
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)
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.
aidacra
(Nathan your friendly neighborhood Support Engineer)
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.
@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
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.
aidacra
(Nathan your friendly neighborhood Support Engineer)
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.
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?