We've got SQL Reporting Services and Progress. We do not use the Openlink ODBC driver. We use the DataDirect ODBC driver (formerly Merant) that is supplied with Progress.
My experience is that it is not a good idea to perform complex SQL queries on Progress using the ODBC driver.
I recommend simple queries with no more than three tables joined. The smaller the recordset returned, the better. I perceive improved performance using Oracle style join syntax.
On SQL Server we always use standard SQL join syntax:
select *
from tbl A
inner join tbl B
on A.key = B.key
where ...
The old Oracle style would be:
select *
from tbl A, tbl B
where A.key = B.key
When you have an outer join, append '(+)' to the optional table's columns in the where clause. For example, in the above query, if Tbl B is optional (left outer join), express it this way:
where A.key = B.key(+)
I, also, recommend first referencing the smallest, required table in the FROM clause and having some criteria corresponding to primary keys in Progress expressed in the WHERE clause to limit the records.
For example, let's say you have a parent table and a child table of detail records.
select *
from Parent, Child
where ....
My bottom line rule is to avoid asking Progress to do work that it doesn't do well. So, keep your queries as simple as possible and do the complex work on SQL Server. Create a Linked Server on SQL server and issue pass-through queries to Progress. Finish the work on the SQL Server. If you want to include WHERE clause criteria for columns that are not indexed in Progress, it may be faster to allow those records to be returned from Progress and filter them out on the SQL Server.
Here is an example of a pass-through query issued on SQL Server:
Select * from openquery(LINKEDSERVERNAME, 'select * from pub."progress-table" ' ) where somecolumn = 5
Good luck.
