CLI and ODBC errors occur when you use the SAS® 9.3 PC Files Server to import and export to Excel


When you are importing or exporting using the SAS PC Files Server, you might receive the following error:

ERROR: CLI error trying to establish connection: [Microsoft][ODBC Driver Manager] 
       Data source name not found and no default driver specified
ERROR: Error in the LIBNAME statement.  
       Connection Failed.  See log for details.

Any of the following can cause this problem:

To circumvent the problem, first check the bit architecture of the SAS PC Files Server and Microsoft Office. Use the following instructions to check this. You can check it by doing the following.

Section 1 - Checking Your Version of Microsoft Office

If you have Office 2003 or Office 2007, you have a 32-bit version of Office and should go to Section 2 of this KB article. If you have Office 2010 and you are running the 64-bit Windows version of SAS, you can check whether you have a 32-bit or 64-bit version of Office by doing the following:

This example uses Excel.

  1. In Excel 2010, click the File tab.
  2. Click Help, on the left.
  3. The version and bit architecture of Excel are shown on the right, under About Excel.
  4. If you have 64-bit Excel and you are running SAS for Windows, stop here, because you do not need the SAS PC Files Server. You can use DBMS=EXCEL instead of the SAS PC Files Server.
  5. If you have 32-bit Excel or you are running SAS on a UNIX host or remote host, continue with the installation process for the SAS PC Files Server.

 

Section 2 - Checking the Version and Bit Architecture of the SAS PC Files Server

To check the bit architecture of the SAS PC Files Server, do the following:

  1. Select Start ► Settings ► Control Panel.
  2. Click Administrative Tools.
  3. Double-click Services.
  4. Locate the SAS PC Files Server. Right-click on the service and select Stop.
  5. Minimize the Services.
  6. Select Start ► All Programs ► SAS PC Files Server.
    This invokes the SAS PC Files Server as a task.
  7. Select Help ► About PC Files Server.
  8. If you have the 64-bit version of the SAS PC Files Server installed, you see this:

    SAS 9.3 for Windows 64-bit

    If you have the 32-bit version of the SAS PC Files Server installed, you see this:

    SAS 9.3 for Windows 32-bit

  9. The SAS PC Files Server must match the bit architecture of the Microsoft Office that you are using. (32-bit Office to 32-bit SAS PC Files Server or 64-bit Office to 64-bit SAS PC Files Server).
  10. If they do not match, continue to Section 3, Uninstalling the SAS PC Files Server.

Section 3 - Uninstalling the SAS PC Files Server

If the SAS PC Files Server and Microsoft Office do not match, you can uninstall the SAS PC Files Server by carefully doing the following:

  1. The Windows user ID needs to have Administrative rights on the machine. If it does not, contact your SAS Administrator at your site.
  2. First make sure that the SAS PC Files Server is stopped. Look in your services panel.
  3. Shut down the SAS PC Files Server if it is still running from doing the steps in Section 2 of this KB article. Click the Shutdown Server button.
  4. Make sure that all BI Servers that are running on the machine are stopped.
  5. Select Start ► Settings ► Control Panel.
  6. Under View by: in the upper right, select Large icons or Small icons.
  7. Click Programs and Features.
  8. Double-click SAS 9.3.
  9. In the Choose Language dialog box, select English or another language and click OK.
  10. Click the Clear All button.
  11. Make sure that all products are unchecked.
  12. Select the check box next to the SAS PC Files Server only.
  13. Click Next.
  14. The Tooling Initialization Complete panel appears. The top bar should have 1/1. If it does not stop, contact SAS Technical Support and click the Cancel button.
  15. Then select the Next button.
  16. A stage 1 uninstall button appears.
  17. Click the Start button. The SAS PC Files Server will be uninstalled.
  18. Click This Program Uninstalled Correctly.
  19. Reboot your machine.
  20. After the machine has rebooted, go to Section 4, Installing the Matching SAS PC Files Server.

Section 4 - Installing the Matching SAS PC Files Server

The SAS 9.3 PC Files Server can be downloaded from the following link:

SAS PC Files Server 9.3

Alternatively, you can do the following:

  1. Click support.sas.com.
  2. Click Downloads & Hotfixes, on the left.
  3. Click SAS Software, under Downloads.
  4. Click SAS PC Files Server.
      • The top one is (Release 9.3 - AUG 2012) for release SAS 9.3 ts1m2.
      • The next one (93.140 11w50 - DEC 2011) is the Week 50 version, which is for SAS 9.3 ts1m1.
      • The Week 29 version (93.130 11w29 - JUL 2011) is for 9.3 ts1m0.
      • Choose the corresponding version to Base SAS that you have.
      • After you choose your version, a table appears. In the table, you see the two different SAS PC Files Servers that are available. Neither works with SAS 9.2 TS2M3 or earlier versions of SAS. Click one of the versions, in accordance with the following:
        • The Microsoft Windows for X64 option is for the 64-bit version of Office. Even if you are running an X64 platform such as Windows 7, do not choose this version if you are running the 32-bit Office.
        • The Windows option is for 32-bit Office. If you are running 32-bit Office, choose this option. The 32-bit version of Office requires the Windows version of the SAS PC Files Server.

    There is also a README file that is located with each SAS PC Files Server. This contains the installation instructions for the SAS PC Files Server. Read the README file carefully before proceeding. The installation of the correct SAS PC Files Server should restore the ODBC components as well.