We are using SQL Server Reporting Services to run reports on our Vantage 8.03 database. My task is to remove all non-Vantage objects from SQL Server and move them to a data warehouse server.

I need the means to generate a list of all Vantage objects so I know which database objects to exclude from this move.

I am new to Vantage and am not up to speed on querying the Progress database; I hope to get some help / info soon, but in the meantime, does anyone have any suggestions or resources I could go to in order to create such a list of Vantage database objects?

5 Replies

Everything should be pretty contained if your database is sitting on SQL Server and Progress is only used for your AppServer connection to SQL. If you DO have this type of environment, Progress houses the Schema Holders for you, but the data is actually contained within a SQL Server database. If memory serves me correct, 8.03.x database default names on a SQL Server are going to start with MfgSys, IE, MfgSys803. If your database is 100% Progress, your not going to have anything listed in SSMS as SQL Server isn't a requirement for a 100% Progress Database environment.

Are you running any other Epicor apps such as Service Connect? They should have an independant DB as well. I don't have 8.x spun up, but on 9.05 the Service Connect database is ESCDB I believe.

Cryhal: Before I start, let me again say THANK YOU for being so responsive and helpful. This is new territory for me; just knowing you're out there is a great comfort to me.

Now on to the problem at hand...

All our data tables live in SQL Server. The way it's been explained to me is 'the schema lives in the Progress database.' I'm a Microsoft / SQL guy so that's where I've done all my work at this point. From what I can see there is very little 'metadata' stored in the SQL side of things; table relationships are not defined in SQL Server, for example -- although the table naming conventions have made that an easy thing to work around.

Our database is in fact named mfgsys803. Just to dip my toe into the water, I did a quick query in the database itself...

select * from sysobjects where name like 'mfgsys803%'

...and got nothing.

We have a copy of Progress OpenEdge which I'm installing (sure is taking a long time...) so I'm hoping that will help me explore the Progress database -- given the correct login credentials, of course.

Since my task is to 'remove all non-Vantage objects from the mfgsys803 database' my first question is which objects ARE for Vantage? Ideally I'd like to get this from a database, but I'll take a list. The last resort is to plod through the Data Dictionary and type the item names manually. Ugh.

Is OpenEdge Data Dictionary not already installed on your server? Its part of the default of Progress OpenEdge.

See, this one is a little tricky for me not having a glance at your setup. Essentially, you could also pop into the Progress Explorer Tool and take a glance at the databases as well if you needed reassurance on where everything was stored. I really hate to steer you in the wrong direction, so I would start there.

In regards to the Schema Holders, your correct, they are in housed by Progress.