May 11, 2007 at 8:28 am
Does anyone know how to link a Progress database to SQL as a Linked Server?
May 11, 2007 at 10:31 am
Only through an ODBC driver. There is one from some company. I can't remember the name.... I haven't done anything with Progress in a few years. Just search for Progress ODBC driver on the net, you'll find it.
May 11, 2007 at 10:40 am
January 29, 2009 at 12:41 pm
I'm trying to do the same.
I create an ODBC using MERANT 3.6 and I have a problem. 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?
January 29, 2009 at 12:49 pm
I do not, but if you've set up your Progress DB as a linked server, you should not need to use OPENQUERY. Instead, use the linked server reference to the table.
select tb_site from linked_server_name.catalog.schema.scdb.tb_mstr
January 29, 2009 at 2:14 pm
In my experience, at least with a linked ORACLE server, the OPENQUERY is much more efficient fetching data than using the linked server reference. If you think about it, the OpenQuery does the selecting job on the other RDBMS and only brings back the results, whereas the linked server has to go trough the whole data to extract the rows, generating the additional network/processing overhead. Now in my case, the data on ORACLE was a HUGE amount of data, so this may or may not be true for smaller datasets.....
And Hugo, the error you are getting because you might have to include the catalog part, as John pointed out:
catalog.schema.scdb.tb_mstr
April 15, 2010 at 11:47 am
Well here's the deal about Linked Server with Progress DB 10.xx version...
1.- Don't use Merant :sick:, use Progress OpenEdge 10.xx Driver.
In the Progress Server ...
2.- Create the sysprogress user.
3.- When starts the Progress DB must be with the command PROSERVE, the necesary functions doesn´t start using _mprosrv
4.- Enable the port and the services that will be used (how to do this? ask to your Progress DBA, because I don't like Progress :sick: ).
In the SQL Server Server
5. Map the IP addresses of the Progress Server in your hosts file.
example: XXX.XXX.XXX.XXX ProgressServer
6. Add the port numbers for well-known services in the services file
example: ProgressSQLName52822/tcp #SQL Progress Service
7. Configure the ODBC
Example:
Database Name: DBPROGRESS
Description: my progress database
Host Name: ProgressServer or XXX.XXX.XXX.XXX
Port Number: 5282
Database Name: MYPROGDB
User ID: sysprogress
Make a Test Connect 😛
In SQL managment Studio:
EXEC sp_addlinkedserver
@server = N'DBPROGRESS',
@srvproduct = N'PROGRESS',
@provider = N'MSDASQL',
@datasrc = N'DBPROGRESS'
GO
EXEC sp_addlinkedsrvlogin
@rmtsrvname = N'DBPROGRESS',
@useself = N'False',
@locallogin = NULL,
@rmtuser = N'sysprogress',
@rmtpassword = 'sysprogresspass'
GO
And voila is ready!!
A select test...
select * from [DBPROGRESS].[MYPROGDB].[PUB].[my_progress_table]
I hope this will be helpful, this cost me hours and hours and I had to fight with the Progress DBA to make this possible.
April 15, 2010 at 1:12 pm
I forgot!
Something very important in the ODBC.
You must configure...
Default Isolation Level: READ UNCOMMITTED
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply