Services Partners Company
Knowledge Base


Article

How to link to a Progress / OpenEdge database from Microsoft SQL Server using a Progress / OpenEdge ODBC Driver ?

« Go Back

Information

 
EnvironmentProduct: Progress
Version: 9.1x
Product: OpenEdge
Version: All supported versions
OS: Windows
Other: Microsoft SQL Server 2005, 2008, 2012
Question/Problem Description
How to link to a Progress database from Microsoft SQL Server using a Progress / OpenEdge ODBC Driver ?
How to make a Linked Server for a OpenEdge database in Microsoft SQL Server 2008?
How to make a Linked Server for an OpenEdge database in Microsoft SQL Server 2012?
How to access an OpenEdge database from Microsoft SQL server 2008?
How to access an OpenEdge database from Microsoft SQL server 2012?
How to connect to OpenEdge database from Microsoft SQL server 2008?
How to link to an OpenEdge database from Microsoft SQL Server 2008 using a DataDirect SQL-92 ODBC Driver?
How to link to a Progress / OpenEdge database from Microsoft SQL Server 2005/2008 using a DataDirect SQL-92 ODBC Driver?
How to make a Linked Server for a Progress database in Microsoft SQL Server 2005?
How to access a Progress database from Microsoft SQL Server 2005?
How to connect to a Progress database from Microsoft SQL Server 2005?
Steps to Reproduce
Clarifying Information
Error Message
Defect/Enhancement Number
Cause
Resolution

Disclaimer: The information below relates to a third-party product and is derived from Microsoft Knowledge Center articles. It is therefore provided 'as-is'. Customers requiring more information are advised to check the Microsoft Knowledge Center or contact Microsoft Technical Support for further assistance.

Option #1 - Using Object Explorer

1) Setup an ODBC connection to the Progress eatabase in the ODBC Administrator in the System DSN. Make sure the connection successfully establishes.
2) Bring up SQL Server Management Studio, connect to Object Explorer. Under Server Objects -> Linked Servers, Right click -> New Linked Server.
3) Provide the information to following options:

Linked server - Provide the name you will use to refer to this linked server.
Server Type - Select "Other data source" as server type. Clicking this option activates the options below it.
Provider -  Microsoft OLE DB provider for ODBC driver.
Product name - it could be anything but better to use progress since it is a progress database.
Data source - ODBC System DSN name. The rest of the options can be left blank

4) Go to the Security tab, choose the radio button associated with "Be made using this security context" and enter the "Remote login" and "With password" of the progress SQL92 user as supplied in the ODBC DSN.
5) Click OK. 

This will bring all the tables (including system tables) of the Progress / OpenEdge database in the Linked Server.
Restart the SQL Server if the tables do not appear and retry.


Option #2 - Using sp_addlinkedserver system stored procedures 
Typical statements to add linked server. "JIM" is the linked server.

EXEC master.dbo.sp_addlinkedserver @server = N'JIM',
@srvproduct=N'Progress', @provider=N'MSDASQL', @datasrc=N'jim',
@provstr=N'MSDASQL'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'collation
compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'data access',
@optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'dist',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'pub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'rpc',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'rpc out',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'sub',
@optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'connect
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'collation
name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'lazy schema
validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'query
timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'use remote
collation', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'JIM', @optname=N'query
timeout', @optvalue=N'900'
Go

Workaround
Notes
Objects linked servers can be referenced in Transact-SQL statements using four-part names. For example, if a lserver name of DeptSQLSrvr is defined against another instance of SQL Server 2005, the following statement references a table on that server:

SELECT Title, HireDate FROM DeptSQLSrvr.AdventureWorks.HumanResources.Employee


Reference to other documentation:

Microsoft SQL server 2005/2008/2012 help

Progress article(s):
000011294, "How to query a Progress database via a Microsoft SQL Server Linked Server?"
000019910, "How to link Progress Database to the MS SQL Server 2000 using SQL-92 ODBC Driver"
Attachment 
Disclaimer

The origins of the information on this site may be internal or external to Progress Software Corporation (“Progress”). Progress Software Corporation makes all reasonable efforts to verify this information. However, the information provided is for your information only. Progress Software Corporation makes no explicit or implied claims to the validity of this information.

Any sample code provided on this site is not supported under any Progress support program or service. The sample code is provided on an "AS IS" basis. Progress makes no warranties, express or implied, and disclaims all implied warranties including, without limitation, the implied warranties of merchantability or of fitness for a particular purpose. The entire risk arising out of the use or performance of the sample code is borne by the user. In no event shall Progress, its employees, or anyone else involved in the creation, production, or delivery of the code be liable for any damages whatsoever (including, without limitation, damages for loss of business profits, business interruption, loss of business information, or other pecuniary loss) arising out of the use of or inability to use the sample code, even if Progress has been advised of the possibility of such damages.



Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

Please tell us how we can make this article more useful.



Characters Remaining: 255