SUPPORT / SAMPLES & SAS NOTES
 

Support

Usage Note 43070: Creating a DBMS library in SAS® Management Console to Access it from a third-party ODBC application using the SAS ODBC driver

DetailsAboutRate It

You can access DBMS library that is defined in SAS Management from third party ODBC application using SAS ODBC driver and SAS/Share.®

You access the library as follows:

Step 1: Define a DBMS Library

First you need to define a DBMS library in SAS® Management Console. For details about how to define such a library, see "Assigning Libraries" in the SAS® 9.2 Intelligence Platform: Data Administration Guide.

You use SAS Management Console or the METALIB procedure to register the database tables that you want to access from a third-party application. To register tables with SAS Management Console, see Registering and Verifying Tables" in the SAS(R) 9.2 Intelligence Platform: Data Administration Guide. To register tables with PROC METALIB, see "Overview: METALIB Procedure" in the SAS(R) 9.2 Languages Interface to Metadata.

The following display shows five tables that are registered for the Teradata library in SAS Management Console.

image label

As you follow the steps to define your library, make sure that the library is assigned to a SAS/SHARE server, as shown below.

image label

Step 2: Configure the SAS ODBC Driver

Configure the SAS ODBC driver so that it creates an ODBC data source. For details, see "Defining Your Data Sources" in the SAS® 9.2 Drivers for ODBC: User's Guide, Second Edition.

To configure the driver, follow these steps:

  1. In the Windows operating environment, locate the ODBC Data Sources or the ODBC Administrator icon. This icon might be located in the Control Panel group, an ODBC group, or in Administrative Tools. Most commonly, you can find it by selecting Start ► Settings ► ► Control Panel ► Administrative Tools ► Data Sources(ODBC). The result should be the ODBC Data Source Administrator dialog box. In this dialog box, click the Add button to open the SAS ODBC Driver Configuration dialog box.

    This dialog box contains three tabs: General, Servers, and Libraries. On each tab, provide information about the data source that you want to access.

  2. On the General tab, provide a name in the Data Source Name text box, as shown in the following display:

    image label

  3. On the Servers tab, enter a value for the Name list box. In Windows operating environments, the SAS ODBC driver requires special syntax for the server name. You must specify the two-part name, using the syntax machine-name__server-ID. Each part of the name must be eight characters or fewer, as shown in the example in the following display:

    image label

    The SAS ODBC Driver interprets machine-name (in this case, t12345 as the host name of the SAS/SHARE server. It interprets server-ID (in this case, __8551) as the service name.

  4. Click the Configure button to display the SAS/SHARE Options dialog box.
  5. image label

    In this dialog box:

    • The Server Address text box is automatically filled with the alias for the TCP/IP network machine name that you specify in the Name text box on the Servers tab. Replace that value with the fully qualified domain name for your SAS/SHARE server (for example, machine.domain.com).

    • The value for the User Name text box is your user ID for the system where the server is running. A value is required here if the server is running in secure mode. Otherwise, the system ignores the value in this box.
    • The value for the User Password box is your password for the system on which the server is running. If you provide a user name without a user password, then the system prompts you for a password at connection time.
    • The Connect Options text box uses a value for the SAS/SHARE server connection. Leave this box empty.
    • The Userid/Password Override check box requests that the UID keyword and PWD keyword be used in the ODBC client application. The driver passes the value of the PWD keyword as the user logon password, and the value of the UID keyword as the user ID.
  6. When you finish entering all of the data, click OK to return to the Servers tab.

    Important: Click the Add button to save the server definition.

  7. Click the Libraries tab, as shown in the following display:

    image label

  8. Define a library for each data library that you want to access with this data source name, as follows:
    1. In the Name text box, enter a name for the library. This text box corresponds to the libref in a SAS LIBNAME statement.
    2. Enter a name for the host in the Host File text box.
    3. The Description text box is optional.
    4. In the Engine text box, enter META.
    5. In the Options text box, enter the following option:
      metauser="metadata-user-ID" metapass="metadata-password" library="exact-library-name-as-defined-in-SAS-Management-Console"
  9. Click Add to save your library information. The saved library name is added then to the list of libraries in the Libraries list box on the left.

After you complete all of the steps above, you can query your database tables from any ODBC application (for example, Microsoft Access).



Operating System and Release Information

Product FamilyProductSystemProduct ReleaseSAS Release
ReportedFixed*ReportedFixed*
SAS SystemSAS Drivers for ODBCMicrosoft® Windows® for x649.239.2 TS2M3
Microsoft Windows Server 2003 Datacenter Edition9.239.2 TS2M3
Microsoft Windows Server 2003 Enterprise Edition9.239.2 TS2M3
Microsoft Windows Server 2003 Standard Edition9.239.2 TS2M3
Microsoft Windows Server 2003 for x649.239.2 TS2M3
Microsoft Windows Server 20089.239.2 TS2M3
Microsoft Windows Server 2008 for x649.239.2 TS2M3
Microsoft Windows XP Professional9.239.2 TS2M3
Windows 7 Enterprise 32 bit9.239.2 TS2M3
Windows 7 Enterprise x649.239.2 TS2M3
Windows 7 Home Premium 32 bit9.239.2 TS2M3
Windows 7 Home Premium x649.239.2 TS2M3
Windows 7 Professional 32 bit9.239.2 TS2M3
Windows 7 Professional x649.239.2 TS2M3
Windows 7 Ultimate 32 bit9.239.2 TS2M3
Windows 7 Ultimate x649.239.2 TS2M3
Windows Vista9.239.2 TS2M3
Windows Vista for x649.239.2 TS2M3
* For software releases that are not yet generally available, the Fixed Release is the software release in which the problem is planned to be fixed.