Data Migration & Integration Configuration

Configure Microsoft SQL Server 2000 Linked Server using the OpenLink OLE DB Provider

The following instructions will enable you to create your own Microsoft SQL Server Linked Server using an OpenLink DSN:

1. Open your Microsoft SQL Server Enterprise Manager.
   
2. Expand the following tree structure:
   
   Console Root => Microsoft SQL Servers => SQL Server Group => Your SQL Server
   
   
3. Expand the Security folder.
   
4. Right click on Linked Servers.
   
5. Select New Linked Server.
   
6. Supply the following information on your General tab:
   
   • Linked Server: Any name you like
     
   • (Check Other Data Source)
     
   • Provider Name: Microsoft OLE DB Provider for ODBC Drivers
     
   • Product Name: MSDASQL
     
   • Data Source: Valid OpenLink DSN Name
     
7. This is the minimum information which you must supply. Feel free to check any server options on the bottom of the tab.
   
8. Click the big Provider Options button.
   
9. Check 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. Click on the Security tab and review your options.
    
12. Click OK to create your server.
    
13. Exit the Linked Server dialog box and the Microsoft SQL Server Enterprise Manager.
    
14. Open the Microsoft SQL Server Query Analyzer.
    
15. Run the following query to test your Linked Server:
    
    

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