Data Migration & Integration Configuration

Configure Microsoft SQL Server 2005 Linked Servers using the OpenLink OLE DB Provider

Use the following steps to create a Microsoft SQL Server 2005 Linked Server.

1. Login to the machine that hosts the Microsoft SQL Server 2005 instance.
   
2. Expand the Start and Programs menu.
   
3. Expand the Microsoft SQL Server 2005 folder.
   
4. Expand the SQL Server Management Studio folder.
   
5. Expand the menu tree associated with your Microsoft SQL Server instance.
   
6. Expand the Server Objects menu tree.
   
7. Expand the Linked Servers menu tree.
   
8. Expand the Providers menu tree.
   
9. Right click the OpenLink Provider and set these options:
   
   

   
   [*] Dynamic parameter
   [*] Nested Queries
   [ ] Level zero only
   [*] Allow inprocess
   [ ] Non transacted updates
   [ ] index as access path
   [ ] Disallow adhoc access
   [*] Supports 'Like' operator
   

   
   
10. Save your changes.
    
11. Right click on Linked Servers.
    
12. Select New Linked Server.
    
13. Supply the following information on your General tab:
    
    • Linked Server: Any name you like.
      
    • (Check Other Data Source)
      
    • Provider Name: OpenLink OLE DB Provider for ODBC Drivers
      
    • Product Name: OpenLinkODBC
      
    • Data Source: Valid OpenLink DSN Name
      
14. This is the minimum information which you must supply. Provider and Product Name must be exactly as shown above. Feel free to engage any server options on the bottom of the tab.
    
15. Click on the Security tab and review your options.
    
16. Click OK to create your server.
    
17. Exit the Linked Server dialog box and the Microsoft SQL Server Enterprise Manager.
    
18. Open the Microsoft SQL Server Query Analyzer.
    
19. Run the following query to test your Linked Server:
    
    

    
    select * from OPENQUERY (Your_LINKEDSERVER_Name, 'your SQL query here')