Progress > SQL Server 2005?

[ihscout]

Hello. New to anything Progress related. We just implmented a new application which used Progress as its database.

I'm trying to import data from this database into SQL Server 2005 with little success. The SQL Server 2005 is where our data warehouse resides and where we do all of our reporting from.

The application vendor gave me one option of connecting to the Progress database. It involves installing a Progress Client, SequeLink Server and SequeLink Client.

When I attempt to import the data into my data warehouse via the "SQL Server Import and Export Wizard" there is no Progress related option in the "Data Source" drop-down.

Anyone have any experience in this arena?

 

[BCM]

We do the same thing you want to do.

You can use the ODBC driver that comes with Progress. Install the ODBC driver on your SQL Server. On SQL Server, create a Linked Server that links to the Progress database via the ODBC driver. You can create DTS jobs that copy the tables and columns you need.

Unless you have the latest Progress (ver 10.x), do not use SQL to join tables on Progress because it will be very slow. It will be faster to join on the SQL Server side.

Watch out for SQL Width problems with character data and date range problems with dates.

 

[ihscout]

You do this in SQL Server 2005? How do you use DTS with 2005? It appears to me that DTS has been replaced with SSIS? Am I missing something?

Also, as far as a Progress driver, I was only given the option of installing the following programs to obtain ODBC access: Progress Client, SequeLink Server and SequeLink Client.

This leaves me with the following options in ODBC:
INTERSOLV 3.10 32-BIT SequeLink
MERANT 3.60 32-BIT Progress SQL92 v9.1D

I'm currently trying to install and configure OpenLink Lite for Progress 9.1D. I'm hoping if I get this driver to work that it will be available as a data source in SQL Server 2005.

 

[BCM]

Use the Merant 3.60 ODBC driver. Also, the Progress side will work better with a service (port) setup specifically for SQL. Use this port when you configure the ODBC dsn.

Instead of DTS use a pass-through query in a stored procedure and schedule the procedure to run daily.

Example:
Assume Linked Server is named ProgressDB.
Assume Progress database table is named Progress-App-Table.
Assume you want to import columns: Acct-Id, Trans-Date, Trans-Amt.
Assume SqlServer Table is named ProgressAppTable.

insert into ProgressAppTable
("Acct-Id", "Trans-Date", "Trans-Amt")
Select "Acct-Id", "Trans-Date", "Trans-Amt"
from openquery(ProgressDB, 'Select "Acct-Id", "Trans-Date"
, "Trans-Amt" from pub."Progress-App-Table" ')

 

[bugg_tb]

Unless you have the latest Progress (ver 10.x), do not use SQL to join tables on Progress because it will be very slow. It will be faster to join on the SQL Server side.

Thats the best piece of advice I've had in a long time! just dropped my query down from 300 seconds to 25.

Great Stuff

 

[MAXIMVS]

Hello. I'm using the MERANT 3.60 32-BIT Progress SQL92 v9.1D, I try to connect a Progress DB as a Linked Server in SQL Server 2005, I make the test conection and respond... "The test connection to the linked server succeeded"
But when I enter a simple select statement like:

SELECT *
FROMOPENQUERY(DB_TRAIN,'select tb_site from scdb.tb_mstr')
GO

I get the next error message:

OLE DB provider "MSDASQL" for linked server "DB_TRAIN" returned message "[DataDirect-Technologies][ODBC PROGRESS driver][PROGRESS]Table/View/Synonym not found (7519)".

Anyone have any experience in this area?

 

[Casper]

Did you try 'select tb_site from PUB.tb_mstr'?

The default schema holder (owner) of the table is normally PUB.

Casper.