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.