Kbase P120484: How to link Progress Database to the MS SQL Server 2005 using SQL-92 ODBC Driver ?
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
12/6/2010 |
|
Status: Verified
GOAL:
How to make a linked server of Progress Database in MS SQL Server 2008?
GOAL:
How to access Progress database from SQL server 2008?
GOAL:
How to connect to Progress database from SQL server 2008?
GOAL:
How to link Progress Database to the MS SQL Server 2008 using Data Direct sql-92 ODBC Driver?
GOAL:
How to link Progress Database to the MS SQL Server 2005/2008 using Data Direct SQL-92 ODBC Driver?
GOAL:
How to make a linked server of Progress Database in MS SQL Server 2005?
GOAL:
How to access Progress database from SQL Server 2005?
GOAL:
How to connect to Progress database from SQL Server 2005?
FACT(s) (Environment):
MS SQL 2008
Progress 9.x
OpenEdge 10.x
Windows
FIX:
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
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