Configuring Microsoft SQL Server DTS Import using the Single-Tier "Lite" Edition OLE DB Provider for ODBC Data Sources

Use the following instructions to configure a DTS Import.

1. Open your Microsoft SQL Server Enterprise Manager.
   
   
   
2. Drill down to Console Root >> Microsoft SQL Servers >> SQL Server Group >> Your Actual 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 descriptive name (no spaces)
     
   • (Check the "Other Data Source" radio button)
     
   • Provider Name: OpenLink ODBC Provider
     
   • Product Name: OpenLinkOLEDB
     
   • Data Source: Your Multi-Tier DSN
     
     
     
7. Do not check any server options on the bottom of the tab.
   
   
   
8. Provide your Data Source username and password on the Security tab.
   
   
   
9. Click OK to create your server.
   
   
   
10. Exit the Linked Server dialog box.
    
    
    
11. Locate your newly created Linked Server.
    
    
    
12. Expand the new Linked Server's tree menu.
    
    
    
13. Click the Tables icon.
    
    
    
14. Locate the qabasics table in the tables list.
    
    
    
15. Carefully record the Schema and Catalog associated with the table. For example —
    
    

    
    NAME       SCHEMA   CATALOG   TYPE
    qabasics   dbo      master    User
    

    
    
16. Expand the Microsoft SQL Server Enterprise Manager's Tools menu.
    
    
    
17. Select the Microsoft SQL Server Query Analyzer.
    
    
    
18. Build your query with four-part naming, along these lines:
    
    

    
    SELECT * FROM [your linked server name].[catalog].[schema].qabasics 
    

    
    For example:
    
    

    
    SELECT * FROM SQL2KLA.master.dbo.qabasics 
    

    
    Note: Leave blanks as place holders when your target database lacks CATALOG or SCHEMA information. For example:
    
    

    
    SELECT * FROM ORA9iLA..SCOTT.qabasics 
    

    
    
19. Click the green arrow icon to run your query.