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?
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 
Feedback
 
Was this article helpful?

   

Your feedback is appreciated.

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



Characters Remaining: 255

 



Copyright © 1993-2014. Progress Software Corporation. All Rights Reserved. See the next generation of application development and data connectivity software, try now!