Configuring a Microsoft SQL Server 2000 Linked Server with the Single-Tier "Lite" Edition OLE DB Provider for ODBC Data Sources

1. Open your Microsoft SQL Server Enterprise Manager.
   
2. Drill down to:
   
   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: OpenLink? OLE DB Provider for ODBC Drivers
     
   • Product Name: OpenLinkODBC?
     
   • Data Source: Valid ODBC DSN
     
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')