Using Open Database Connectivity (ODBC) to Access SPD Server Tables

Read this section if you do not have Base SAS software on the network client, but you want to access SPD Server tables on the network, using an ODBC compliant program, such as Microsoft Word, Query, Excel, or Access, and you have SPD Server tables available for use, somewhere on the network, or Scalable Performance Data Servers and SPD SNET servers running, or client machines in a Windows environment.

Why Use ODBC?

Installing OBDC Drivers on the Server

Configuring ODBC on the Client

Preparing Your Client Machine for ODBC Installation

Two Types of ODBC Connections

Primary and Secondary LIBNAME Domains

Configuring an ODBC Data Source to Connect Directly to an SPD Server

Configuring an ODBC Data Source for SPD SNET

Editing the Services File on Your Machine - ODBC Details

Creating a Query Using an ODBC-Compliant Program

Why Use ODBC?

You have SPD Server tables available on your network, and one or more of the following might be true:

You do not have Base SAS software running on the Windows client, but you need to view or change SPD Server tables.
You need to view or change the SPD Server tables using a Microsoft spreadsheet, database,or word processor.
You need to view or change SPD Server tables in ways that cannot be predetermined or programmed into a Web page.
You need to view or change SPD Server tables using Windows tools you are familiar with.

Installing OBDC Drivers on the Server

Instructions for installing the OBDC driver are included in the download package.

Configuring ODBC on the Client

Configure an ODBC data source.
Make your query using a Windows program.

Configure ODBC to Connect SPD Server Client to SPD Server Host

Configure ODBC to Connect SPD Server Client to SPD SNET Server

Preparing Your Client Machine for ODBC Installation

Before you create an OBDC data sources driver, you will need the following information from your network administrator:

a User Name and Password that is defined by an SPD Server administrator
the primary LIBNAME domain of the SPD Server (also called the DBQ)
the port number of the SPD name server (also called the SERV)
the machine name or IP address of the SPD Server Name Server (also called the HOST)
any secondary LIBNAME domains you want to assign to the ODBC connection

Two Types of ODBC Connections

With SPD Server software, you can connect directly to an SPD Server without going through the SPD SNET server. Although connecting directly is the preferred method, connections via the SPD SNET server are still supported.

Primary and Secondary LIBNAME Domains

When a connection to the SPD server is established a primary LIBNAME domain is assigned. The primary LIBNAME domain is specified by the "DBQ" connection options parameter. Immediately after the connection is made the SAS ODBC Driver assigns the secondary LIBNAME domains that are configured through the Libraries tab of the SAS ODBC Driver Configuration window.

ODBC Connections via the SPD SNET server must have an odbc.parm file configured on the SPD SNET Server machine.

Configuring an ODBC Data Source to Connect Directly to an SPD Server

Once the SAS ODBC driver is installed, you will need to configure your ODBC data source. When you open the ODBC manager, you'll get a display screen that allows you to enter information that points the OBDC driver to the data on the SPD Server.

From the Windows Start button, select Start Settings Control Panel
Locate the ODBC Data Sources icon and open the Microsoft ODBC Data Source Administrator . The exact location of this program depends on your version of Windows.
Select the Add button, then select the SAS ODBC driver.
Enter a data source name (and description if desired.)
Select the Servers panel and type in your two-part server name.
Click on the Configure box. The TCP Options window appears:
- Server Address: Enter the network address of the machine on which the SPD Server is running.
- Server User Name: Enter the user name as configured for a DBQ (SPD Server primary LIBNAME domain) on the SPD Server to which you will connect.
- Server User Password: Enter the user password as configured for a DBQ (SPD Server primary LIBNAME domain) on the SPD Server host to which you will connect.
- Connection Options: Enter the Connection Options as follows:
  
  DBQ='SPD Server primary LIBNAME domain',this is the SPD Server LIBNAME domain
  
  HOST='name server node name',this is the location of the host computer
  
  SERV='name server port number',this is the port number of the SPD Server name server running on the HOST.
  
  Any other SPD Server LIBNAME options. For more information, see the User's Guide section on LIBNAME Options.
Click OK. Then, click Add, and select the Libraries panel.
Enter the DBQ name of a secondary LIBNAME domain in both the Name and Host File text fields.
Enter "spdseng" in the Engine text field.
Use SQL Pass-Through syntax rules for libref statements when you enter a value in the Options text field.

Configuring an ODBC Data Source for SPD SNET

From the Windows Start button, select Start Settings Control Panel.
Click on the ODBC icon and select the Add button.
Select the SAS ODBC driver.
Enter a data source name (and description if desired).
Select the Servers panel and type in the two-part server name. The second part of the server name should match the entry in the services file. In the example that follows that shows you how to edit the services file, the server name is spdssnet.
Click on the Configure box. The TCP Options window appears with four input fields that you fill:
- Server Address: Enter the network address of the machine on which the SPD SNET server is running.
- Server User Name: Enter the user name as configured for a DBQ (SPD Server primary LIBNAME domain) on the SPD Server to which you will connect.
- Server User Password: Enter the user password as configured for a DBQ (SPD Server primary LIBNAME domain) on the SPD Server host to which you will connect.
- Connection Options: Enter the connection options as follows:
  
  DBQ='SPD Server primary LIBNAME domain':this is the SPD Server LIBNAME domain.
  
  HOST='name server node name':this is the location of the host computer.
  
  SERV='name server port number':this is the port number of the SPD Server name server running on the HOST.
Click OK, and then click Add.

Editing the Services File on Your Machine - ODBC Details

Editing the Services file is required only for ODBC connections via the SPD SNET Server.

Find the Services file on your Windows machine. In Windows, the Services file is usually located in c:\windows\services.
Open the Services file using a text editor.
The services file contains four columns. The rows of information can be sorted in port number order. Find the closest port number to the SPD Server port number, which you obtained from the network administrator. See Preparing Your Client Machine for ODBC Installation). This is where you insert the new information.
Add an entry to the Services file, on its own line, in proper numeric order, using the following syntax:

How to Add Service Name and Port Number to the Services File

column1 <service name>	column2 <port number & protocol>	column3 <aliases>	column4 <comment>
spdssnet	nnnn/tcp	not required	not required
spdssnet=name	nnnn=port number
assigned to server	protocol is always /tcp

Remember: The service name, spdssnet must match the server name that you used in step 6 of Configuring an ODBC Data Source for SPD SNET. The port number must match the port number on which the SPD SNET server is running.

Creating a Query Using an ODBC-Compliant Program

The following instructions create a query using Microsoft Access.

Start the SPD SNET server.
Start Microsoft Access.
From the Microsoft Access main menu, select File Get External Table .
Select Link Table.
Select Files of Type.
Select ODBC Databases.
Select the data source.