Using 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, Microsoft Query, Microsoft Excel, or Microsoft Access. In addition, you must have SPD Server tables available for use somewhere on the network and SPD Server data servers and SPD Server SNET server running on 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 the SPD Server SNET Server

Editing the Services File on Your Machine

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 is 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 SPD Server tables using a Microsoft spreadsheet, database, or word processor.
You need to view or change SPD Server tables in a way that cannot be predetermined or programmed into a Web page.
You need to view or change SPD Server tables using Windows tools that you are familiar with.

Installing OBDC Drivers on the Server

Instructions for installing OBDC drivers are included in the SPD Server installation package.

Configuring ODBC on the Client

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

Configure ODBC to Connect an SPD Server Client to the SPD Server Host

Configure ODBC to Connect an SPD Server Client to the SPD Server SNET Server

Preparing Your Client Machine for ODBC Installation

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

a user name and password that are defined by an SPD Server administrator
the primary LIBNAME domain of the SPD Server (also called the DBQ)
the port number of the SPD Server 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 that you want to assign to the ODBC connection

Two Types of ODBC Connections

SPD Server software enables you to connect directly to an SPD Server host without going through the SPD Server SNET server. Although connecting directly is the preferred method, connections through the SPD Server SNET server are 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, which are configured through the Libraries tab of the SAS ODBC Driver Configuration window.

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

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

After the SAS ODBC Driver is installed, you need to configure your ODBC data source. When you open the ODBC Manager, a display screen enables you to enter information that points the SAS OBDC Driver to the data on the SPD Server.

From the Windows Start menu, select SettingsControl Panel.
Locate the ODBC Data Sources icon, and open the Microsoft ODBC Data Source Administrator. The location of this program depends on your version of Windows.
Click Add, and then select the SAS ODBC Driver.
Enter a data source name and description.
Select the Servers panel, and enter your two-part server name. The second part of the server name should match the entry in the services file.
Click Configure. 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 the DBQ on the SPD Server to which you will connect.
- Server User Password Enter the user password as configured for the DBQ on the SPD Server host to which you will connect.
- Connection Options Enter the connection options as follows:
  
  DBQ='SPD Server primary LIBNAME domain' SPD Server LIBNAME domain
  
  HOST='Name Server node name' location of the host
  
  SERV='Name Server port number' port number of the SPD Server Name Server running on the host
  
  Any other SPD Server LIBNAME options. For more information, see LIBNAME Options.
Click OK, click Add, and select Libraries.
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.
Follow the syntax rules for the SQL pass-through LIBREF statement for entering a value in the Options text field.

Configuring an ODBC Data Source for the SPD Server SNET Server

From the Windows Start menu, select SettingsControl Panel.
Locate the ODBC Data Sources icon, and open the Microsoft ODBC Data Source Administrator. The location of this program depends on your version of Windows.
Click Add, and then select the SAS ODBC Driver.
Enter a data source name and description.
Select the Servers panel, and enter your two-part server name. The second part of the server name should match the entry in the services file. In the example that shows how to edit the services file, the server name is spdssnet.
Click Configure. The TCP Options window appears.
- Server Address Enter the network address of the machine on which the SPD Server SNET server is running.
- Server User Name Enter the user name as configured for the DBQ on the SPD Server to which you will connect.
- Server User Password Enter the user password as configured for the DBQ on the SPD Server host to which you will connect.
- Connection Options Enter the connection options as follows:
- DBQ='SPD Server primary LIBNAME domain' SPD Server LIBNAME domain.
- HOST='Name Server node name' location of the host
- SERV='Name Server port number' 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

Editing the services file is required only for ODBC connections through the SPD Server SNET server.

Find the services file on your Windows machine. 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. For more information, see Preparing Your Client Machine for ODBC Installation. This is where you insert the new information.
Add an entry to the services file. The entry should be on its own line, in proper numeric order, and using the following syntax:

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

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

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

Creating a Query Using an ODBC-Compliant Program

The following instructions create a query using Microsoft Access.

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