Data Migration & Integration Configuration

Configure Microsoft SQL Server 2005 Linked Servers using the Microsoft 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 Microsoft 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. Right click on Linked Servers.
   
9. Select New Linked Server.
   
10. 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
      
11. This is the minimum information which you must supply. Feel free to check any server options on the bottom of the tab.
    
12. Click on the Security tab and review your options.
    
13. Click OK to create your server.
    
14. Exit the Linked Server dialog box and the Microsoft SQL Server Enterprise Manager.
    
15. Open the Microsoft SQL Server Query Analyzer.
    
16. Run the following query to test your Linked Server:
    
    

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