I’m looking for some tips on making our Vantage Data available in SQL server. Since (in our E10 upgrade) we’re looking to clean up our data and migrate master files only), there is still benefit in being able to make our Vantage historical data available to our E10 environment - but not directly in the Epicor database. My thoughts were to import the Progress database into SQL server and then selectively make that data available to be referenced via external queries from within the Epicor 10 environment.
So far, I’ve been reasonably successful in getting a majority of the data into SQL Server tables using:
Select * into [table-name] from OpenQuery(Linked-Server-name, ‘Select * from Vantage-Table-Name’).
Unfortunately, there are a number of tables which error-ed out (mostly due to the wonderfully descriptive “The provider reported an unexpected catastrophic failure.” error).
I’m just wondering what other people have done to get their Vantage 8 data available in E10 and if they ran into this error, how did you get around it? I’ve run into the Progress data field massive decimals error, but am still unable to get past this error. It’s happening on too many Key tables to make the Vantage data of any real value.
All suggestions appreciated.
Thank you.
Jeff Henslee
M-B Companies Inc.
aidacra
(Nathan your friendly neighborhood Support Engineer)
2
Does the schema of the externally referenced data need to be that of your existing V803 database?
When the migration from OpenEdge E9.05.702a to E10.x occurs an interim database is created which is a SQL version of the E905 data. There aren’t any indexes with this data, but there are a number of ways to do that to improve query performance.
There was a thread earlier this year that discussed some other options which include using an OpenEdge process to dump and load into a SQL database which may be helpful.
Having the Schema field names be roughly the same has its advantages. I’ve been experimenting and pulling my hair out (what little there is left anyways).
I “thought” I stumbled onto a utility that would do exactly what I am asking about on the .Data administration tool inside of OE. The option is found on the DataServer Menu, then select MS SQL Server Utilities, then Schema Migration Tools and then finally Open Edge DB to MS SQL Server.
The OpenEdge to MS SQL Server Conversion form appear next and has options to Load SQL and also to Move Data.
I have reviewed and even tested the SQL generated by the conversion tool and it appears to run with just 1 error (Exceeded max key columns 17 vs. 16) My issue is that no matter what I try when I run the utility, I continually receive the error - [The Target Database Exists. (3216)]