Article

How to link Progress Database to the MS SQL Server 2005 using SQL-92 ODBC Driver ?

« Go Back

Information

 
EnvironmentProgress 9.x
OpenEdge 10.x
OpenEdge 11.x
All Supported Operating Systems
Microsoft SQL Server 2005
Microsoft SQL Server 2008
Question/Problem Description
How to make a linked server of Progress Database in MS SQL Server 2008?
How to access Progress database from SQL server 2008?
How to connect to Progress database from SQL server 2008?
How to link Progress Database to the MS SQL Server 2008 using Data Direct SQL-92 ODBC Driver?
How to link Progress Database to the MS SQL Server 2005/2008 using Data Direct SQL-92 ODBC Driver?
How to make a linked server of Progress Database in MS SQL Server 2005?
How to access Progress database from SQL Server 2005?
How to connect to Progress database from 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 Database in the ODBC Administrator in the System DSN. Make sure the connection successfully establishes.
2) Bring up SQL server 2005 Management Studio, connect to Object explorer. Under Server Objects -> Linked Servers, Right click -> New
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 progress system tables in the SQL Server 2000 Database.

If you do not see your tables, please exit the Management Studio and then restart it.  You should now see your tables.


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 Written Documentation:

Microsoft SQL server 2005/2008 help

Progress Solutions:
P119210, "How to query Progress Database on the link server on the Microsoft SQL Server?"
P22728, "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.
Previous MonthNext Month
SunMonTueWedThuFriSat