Another twist to using the OpenQuery Statement

I’m currently testing what others have done in order to (eventually) bring our old Vantage 8 Data from a progress database and bring it into SQL Server so that (if needed) once we migrate our master data, queries could be written to view older Vantage 8 data but using SQL Tables.

I have been doing a lot of testing lately and I have been able to

  • Create my linked server and see data.
  • build my SQL table
  • Create SQL statements so that I can view Progress data through SSMS.

so that I do not have to create over 2K SQL statements in order to import the progress data into my SQL Server database, I have created a SQL server statement that can be used to dynamically create my SQL statements so that I can start with an empty database and create each table within my SQL server database by directly selecting tables from Progress database.

  • SELECT ‘Select * into [Interim-DB].[DBO].’ + TBL + ’ From OpenQuery(VantageDB,’ + ‘’’’ + ‘Select * from mfgsys.pub.’ + TBL + ‘’’’ + ‘)’
    FROM OPENQUERY(VantageDB, ‘Select * from sysprogress.systables order by ID’) where TBLTYPE = ‘T’
    GO
    Now I can either direct the output to a file or into a table in the database itself that I can use T-SQL to load the information I need (my target is to use this occasionally during our E10 testing as well as on go live weekend to snapshot load our Progress data.

What it is I’m having trouble with is trying to create a SQL statement to replicate the necessary indexes that exist in the progress database into the SQL database.

Here’s what I have so far…something like this…

SELECT 'Create Index ’ + IDXNAME + ’ ON ’ + TBL + ’ ( ’ + COLNAME,
(SELECT ‘,’ + COLNAME FROM OPENQUERY(VantageDB, ‘Select * from sysprogress.sysindexes’) Col2 WHERE Col2.IDXNAME = IDXName.IDXName and IDXNAME.IDXSEQ <> 0)
FROM OPENQUERY(VantageDB, ‘Select * from sysprogress.sysindexes’) IDXName order by TBL, IDXSEGID, IDXSEQ
GO

I’m trying to get the SQL statement output to look like this…

Create Index ON [DB].[Schema]. (Col1, Col2, Col3, etc)

The part I’m having trouble with is being able to get the additional column names to appear in a single row - rather than in a column.

Here’s the error I receive:

Msg 512, Level 16, State 1, Line 2
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.
Has someone done this already? Willing to share your SQL statement?

Thanks in advance.
Jeff Henslee
M-B Companies Inc.

Maybe I’m missing something you already thought of, but it seems like Epicor’s got some pretty good tooling now for getting a Vantage 8 / Epicor 9 database upgraded to Epicor 10 - which obviously involves moving the data from Progress to SQL, and creating all of those SQL tables and indexes for you, right? Wouldn’t it be easier just to follow the natural, Epicor developed upgrade path and then just take the resulting SQL database and set it aside? I mean, you don’t even need to create an E10 app server for it or test it or anything, because you are simply interested in keeping the SQL database it spits out toward the middle of the process.

Hi Adam -
Funny you should say that because I’m using an old E9 conversion server for this test. The goal for E10 is to migrate our static data and open transactional data into 10 and start fresh in E10. The goal is to just make our V8 database available to E10 tools in side of SQL server but NOT bring any of our old data into E10 as it exists today (we really need to clean up our V8 data anyways). Think migration of our data and not converting any of our existing data through E9 on our way to E10 - the thought process here is to bypass Epicor 9 all together.

I have been successful in being able to create a dynamic SQL statement inside of SQL Server to basically select all of records from all V8 tables and bring them into a SQL Server database. What Iwas hoping to do was create a second dynamic SQL statement to replicate all of the indexes that exist and roughly do the same thing - re-create the Progress database indexes for the tables migrated to SQL Server.

Right, I knew you wanted to just “start fresh” basically on E10, but you could still migrate your V8 database to E9 SQL using their tooling, and then presto you have your legacy data already in a SQL server DB, with indexes, foreign keys, etc.

My other thought would be to use something like SQL Compare (a red-gate tool) to pull all of the indexes out of your E10 database you are using and to just try to apply them to your custom made V8 SQL database. probably a bunch of them will fail to apply, but it might give you a “good enough” set of indexes. Or, if you have any DB proficiency or understanding of indexes (which it seems you do given your current trajectory) you could just apply some indexes to the V8 SQL tables “as needed” (as your folks requests reports / views that incorporate the legacy data).

Sorry, I’ll stop distracting from your original goal from now. My understanding of querying a Progress DB is extremely limited. We have always been SQL DB, even in the E9 days.

You can do it easily in SQL. Looking at your index query. it looks like you
can easily create a temp table in SQL that will be populated with the table
name, index name and column name. The table will look something like:
create table tblIdx( tbl varchar(255) , Idx varchar(255), col varchar(255) )
Once you have that table, you have a number of techniques to pivot it into
a statement.

My favorite is a function.

The basic SQL function will have something like this:

create function dbo.createindex(tbl varchar(255), idx varchar(255))
as

begin
declare @buf

set @buf = 'create index ’ + @idx + ’ on ’ + @tbl + ‘(’
select @buf = @buf + col + ‘,’
from tblidx
where tbl = @tbl
and idx = @idx
order by iden

set @buf = substring(@buf, 1, len(@buf)-1) + ‘)’
end

once you have the function

select distinct dbo.createindex(tbl,idx) from tblidx

will result in the script with all the indexes

Jeff -

You can dump the indexes using Database Administration tools and specify your target SQL flavor and other options. Let me know if you would like specific instructions on how this is accomplished.

I’ll check into it and let you know.
Thank you

Jeffrey C Henslee
Information Technology / ERP Manager
M-B Companies Inc.
1615 Wisconsin Avenue
New Holstein, WI 53061
920-522-2684

I was able to create the index statements - but now I have another SNAFU. Apparently I have been bitten by the progress bug. a few of my tables did not import into SQL due to the following reason/error:

Msg 7354, Level 16, State 1, Line 1
The OLE DB provider “MSDASQL” for linked server “VantageDB” supplied invalid metadata for column “RepComm1”. The precision exceeded the allowable maximum.

Any thoughts or settings as a way to get around this error? Going to be checking the internet for answers too - but thought maybe someone from the list has run into a similar issue.

Thank you,
Jeff