'; window.popUpWin.document.write(zhtm); window.popUpWin.document.close(); // Johnny Jackson 4/28/98 } //--> Windows 98 Professional Reference -- Ch 16 -- Windows 98 ODBC Connectivity


Windows 98 Professional Reference

Previous chapterNext chapterContents


- 16 -
Windows 98 ODBC Connectivity



This chapter addresses the use of ODBC (Open Database Connectivity) and the issues of successfully connecting a client program running on Windows 98 to a database server running on another machine. If you have never done this before, you may want to read the next few pages, which provide an overview of client/server database concepts, ODBC terminology, and the parameters used in an ODBC Connect String.

This chapter includes two examples of creating DSNs (Data Source Names). One example shows how to create a DSN that connects to a SQL Server; the second depicts a connection to an Access database. Both examples address how to create user, system, and file DSNs.

A program on the CD ROM allows you to experiment with various ODBC parameters to see what effect they have on the connection process.

Finally, this chapter includes sections that address how to install or upgrade ODBC, troubleshooting ODBC connection problems, and advanced ODBC tuning options.

Client/Server Database Concepts

You are probably reading this chapter because you need to connect an application program running on a Windows 98 client computer to a database located on some server, such as an NT Server, a UNIX box, or a mainframe system. This chapter concentrates on two-tier client/server architecture, but it is also applicable to N-tier (3 or more-layer) architecture. See Chapter 15, "OLE, COM, DCOM, and ActiveX," for more information on N-tier architecture.

Databases used by PC users fall into one of two categories: file type or client/server type databases.

File type databases such as Access and Paradox were designed primarily as single-user, relatively small databases in which the data, the user interface and, if required, the database engine all resided on a single end-user machine. Today, many file type databases are network capable and can support multiple users accessing data simultaneously either with or without ODBC. However, file type databases were not designed for this purpose and therefore suffer under heavy workloads. While there are ODBC drivers written to allow access to file type databases, this was not the primary intent of ODBC (see Figure 16.1).

Figure 16.1

This diagram shows the major componentsin a file type database.

In contrast, client/server type databases (see Figure 16.2) such as SQL Server, ORACLE, and Informix were specifically designed to handle many users accessing large centralized databases over communication lines. The server runs an engine (a program) that accepts requests in the form of a SQL statement from the client machine, locates those records matching the SQL statement, and finally returns the data matching the request to the client via the communication lines. The engine is often written as a multithreaded program that can process requests from multiple users simultaneously. Database server machines often contain large amounts of RAM, multiple processors and high-speed disk subsystems to handle the heavy workloads imposed by many users.

Figure 16.2

This diagram shows the major components found in a client/server database.

A single database engine running on a server platform usually services multiple databases. For example, a database engine might service requests from the Accounting department against the AP and AR databases, whereas the Sales department might need access to the ForecastSales and ClosedSales databases. ODBC's primary focus is to allow client programs to access these server type databases.

Now you have been introduced to the big picture and some terminology from the database server side. You might be wondering how Windows 98 fits in or, more importantly, what you as a system administrator need to do in order for Windows 98 to successfully communicate with a database on a database server.

When an application running on Windows 98 attempts to access a database engine located on a remote server, many layers of software and hardware must work together to establish the connection. In addition to the standard networking components that you must install and configure, you must also install and configure software that fits between your application program and the networking software.

On Windows 98, you must install and configure an ODBC Manager and at least one ODBC Driver. The application program talks to the ODBC Manager when it needs to access a database. Based on configuration settings, the ODBC Manager selects an ODBC driver that can talk to the database engine. After the database driver is selected and loaded in memory, data flows between the application and the networking software through the database driver.

In the Control Panel is a 32bit ODBC applet. This applet is used to configure parameters such as which ODBC Driver to use, what database server to connect to, and what database on the database engine to access. The same applet allows you to set configuration parameters for your application program to access local files on your Windows 98 computer, as well as set configuration for client/server databases. These configuration settings can be unique to a specific user, shared among multiple users of a Windows 98 computer, or copied as a file to many machines. (The procedures to install ODBC software and to configure it properly are covered later in this chapter.)

An Overview of ODBC

ODBC (Open Database Connectivity) is an Application Programming Interface (API) developed by Microsoft that has been widely adopted by many industry database manufacturers. ODBC allows programmers the ability to write application programs that universally access Relational Databases Management Systems (RDBMS) such as SQL Server, ORACLE, Informix, and others.

ODBC frees customers and application programmers from the requirements of a specific database implementation or manufacturer. Customers have the ability to port their data to another RDBMS for reasons such as increased performance, cost savings, or platform change while maintaining the ability of the application program to access the data.

However, the freedom of data portability comes with a cost. In order to provide portability, ODBC uses ANSI Standard SQL statements, which provide a generic method for programmers to access data. This generic approach allows for portability but precludes the ability of the application programmer to take advantage of special features and SQL extensions associated with a specific database engine. In other words, if an application programmer chooses to use the native database interface instead of ODBC, the speed of access may be faster and the functionality may be greater. But this comes at the cost of rewriting the application if it is necessary to port the data to another database engine.

ODBC Drivers

ODBC implementation relies on drivers that abstract differences in database implementation from the application program in a manner similar to how printer or NIC drivers abstract the implementation of specific printer devices or NIC cards. For example, to communicate with SQL Server you need an ODBC driver written specifically to talk to SQL Server.

Microsoft has written ODBC drivers for its own products and several competing products. Microsoft has also written drivers for SQL Server and a partial ORACLE driver for client/server database engines, as well as drivers for Fox Pro, Access, Paradox, and dBase file type databases. Microsoft also has drivers for non-database data sources such as Excel spreadsheets and comma-delimited text files. There are, however, many other database manufacturers and third-party companies that have written ODBC drivers for databases found on mainframe, UNIX, and other platforms.

Not all ODBC drivers are created equal. In Windows 98, 32-bit applications must use 32-bit ODBC drivers whereas 16-bit applications must use a 16-bit ODBC Manager and 16-bit ODBC drivers. Additionally, the ODBC specification allows drivers to conform to various levels of the specification. The level of conformance the driver is written to meet determines the level of functionality provided. Lastly, differences in how the drivers are written may result in speed differences between otherwise similar drivers.

The ODBC Manager

In addition to the ODBC driver(s), you also use an ODBC Manager program (refer to Figure 16.2) that for the most part is transparent to the user. The ODBC Manager (ODBC32.DLL) and driver require several parameters in order to successfully connect to the desired database. The ODBC Manager handles loading the specified ODBC driver, querying the driver for its level of conformance, and passing connection parameters, read and write data, error information, and so on, between the application program and the ODBC driver.

Connecting to the Database

Before data can be read from or written to the database, a connection must be established. Often the source of the data is on another machine that must be accessed via a LAN or WAN connection. To successfully connect, ODBC uses the following parameters:


NOTE: The first five parameters are required to establish a connection.


The ODBC parameters required to establish a connection can be supplied from one source, or a combination of several sources. One source is from within the application program itself, in which case these parameters are supplied by the programmer. A second source is through a DSN; a DSN is usually created by the system administrator. A third is Windows 98, which can supply the UserID and password entered by the user during logon. Another source is the user himself, who, when prompted with a dialog box, can select a DSN and also enter a UserID and password.

If the program includes the required parameters, it should connect directly to the database when run. A problem with this is that it requires changes to the program if the database is ported to another server or RDBMS.

Usually, the program includes the DSN and possibly the DATABASE and UID parameters. This affords local administrators or management the flexibility to determine which server to place the database on, or which RDBMS to use. The ODBC Manager can also prompt the user with a dialog box (see Figure 16.3), in which the user can provide the missing PWD or both the UID and PWD parameters in order to promote security. If the UID and PWD parameters are missing, the Windows 98 logon UserID and password are used for these parameters.

Figure 16.3

This dialog box allows users to enter a UserID and/or password, or choose a DSN.


NOTE: By using the accompanying CD-ROM you can install a program that allows you to experiment with ODBC connectivity. This program was written with Visual Basic 5.0 and has been tested against the Pubs database included with SQL Server version 6.5 and the Northwind database included with Access 97. This program allows you to quickly change, include, or exclude various ODBC parameters, then attempt to establish a connection. After you're connected, you can query tables and views from the database.


The ODBC Connect String

As mentioned earlier, there are several sources that can supply ODBC parameters. Inside a program these parameters reside in what is known as an ODBC Connect String; outside the program these parameters are supplied by a DSN. The ODBC Connect String is compiled as part of the program and therefore does not change. The application program looks to itself first for the ODBC parameters. If some ODBC parameters are missing, the program then can use a DSN or can prompt the user for the missing parameters.

One way to supply the ODBC parameters is for the application program to include an ODBC Connect String. Programs often include an ODBC Connect String, which includes some or all of the ODBC parameters. ODBC connect strings should not include any extra spaces (although you must exactly match the DRIVER name, which often does include spaces), but must terminate each parameter with a semicolon.

The following examples show several connect strings and explain what happens when they are used. Assume that the client program is attempting to access the Inventory database on a SQL Server named Sun; the user account is WarehouseMgr and the password is 98765.

"UID=WarehouseMgr;PWD=98765;DRIVER=SQL Server;SERVER=Sun;DATABASE=Inventory;"

In the preceding ODBC Connect String, the required parameters are included and the client program can connect without any user intervention. The programmer can control whether a dialog box (refer to Figure 16.3) is displayed to the user during the connection sequence. In this case it is not necessary.

 "DRIVER=SQL Server;SERVER=Sun;DATABASE=Inventory;"

In this ODBC Connect String, neither the UID nor the PWD parameter is included. In this case, the ODBC Manager uses the UserID and password from the Windows 98 logon process. If the programmer displays the dialog box, it is prefilled with the logon UserID and Password. If the dialog box is not displayed, a connection is attempted using the logon UserID and Password.

"UID=WarehouseMgr;DRIVER=SQL Server;SERVER=Sun;DATABASE=Inventory;"

In this ODBC Connect String, the PWD parameter is not included. The programmer can choose to display the dialog box with the UID prefilled. The user must then enter the proper password to connect successfully.

The three preceding examples all require client program recompilation if the database is not called Inventory on a SQL Server named Sun.

"UID=WarehouseMgr;DSN=InventoryDSN;"

Assume the DSN named InventoryDSN supplies the proper server, driver, and database parameters. This ODBC Connect String works the same as the previous example except that now you have freedom to move the database without recompiling the program by changing these parameters in the DSN.

"DSN=InventoryDSN;"

Assume the DSN named InventoryDSN supplies the proper server, driver, and database parameters. In this example, the ODBC Manager uses the UserID and password from the Windows 98 logon process. If the programmer displays the dialog box to the user, it is prefilled with the logon UserID and Password. If the dialog box is not displayed to the user, a connection is attempted using the current Windows 98 logon UserID and Password.

Creating a DSN

The last two ODBC Connect String examples in the previous section include a DSN parameter. The DSN parameter supplies a name, which is compared with the Data Source Names (DSNs) on the system. If the DSN name matches the name from the DSN parameter, contents of the DSN such as the server, driver, and database are used to establish the connection. Using DSNs provides greater flexibility over ODBC Connect Strings. In some cases this flexibility is desired. However, in other cases, the ease of use offered by the ODBC Connect String is preferred.

You create a DSN by using the Control Panel 32bit ODBC applet. A DSN cannot supply either the UID or PWD parameters but can supply other parameters, including several not found in the ODBC Connect String.


NOTE: If both a DSN and the ODBC Connect String provide the driver, database, or server parameters, the parameter(s) from the ODBC Connect String take precedence and are used.


By using the 32bit ODBC applet you can create three types of DSNs: User DSNs, System DSNs, and File DSNs.

A User DSN is stored in the User Profile of the currently logged-on user and is usable by that user account. A System DSN is available to any user on this machine and to system applications. Both of these DSN types are stored in the Registry and are typically created by an administrator on a machine-by-machine basis.

A File DSN can be built on one machine and then copied to other machines that have a need for similar DSNs. Remember that DSNs do not hold UID or PWD parameters, so copying DSNs should not impose a security risk.

Steps to Create a DSN

To create or update a DSN you must first open the ODBC Data Source Administrator. To do this, choose Start, Settings, Control Panel, 32bit ODBC. You should see the ODBC Data Source Administrator applet that appears in Figure 16.4.


NOTE: The Windows 98 installation process does not automatically install or update ODBC because the files are located outside the Windows 98 directory on the CD. Your system may have an old version of the 32bit ODBC applet left over from Windows 95 or the installation of an application such as Visual Basic or Access. Or, you may not have a 32bit ODBC applet at all in Control Panel. Therefore, you may need to manually force the ODBC installation process if any of the following three scenarios are true:
n The 32bit ODBC icon is not present within Control Panel.
n The ODBC Data Source Administrator window appears substantially different from the one shown in Figure 16.4.
n The screens or wizards you see do not substantially match the figures shown throughout the rest of this chapter.
If you determine that you need to install or upgrade ODBC, see the section "Install or Upgrade ODBC" later in this chapter.


Figure 16.4

The ODBC Data Source Administrator applet is used to create and maintain DSNs.


NOTE: The dialog-box names and choices you see as you create a DSN can change depending on which ODBC Driver you choose. The basic steps involved, however, are fairly similar. As you create either a User, System, or File DSN you can follow either of two samples. One sample creates a DSN for SQL Server as an example of a server type RDBMS. The second sample creates a DSN for a Microsoft Access database as an example of a file type database.


The next few sections describe how to create DSNs. As a system administrator you should decide which type of DSN best fits the current needs of your system.

If many machines require identical DSNs to access a central database, then a file type DSN is probably the best choice. You can create one File DSN for the database and then copy the file to all machines that need access to that database.

If several people use Windows 98 and it is configured to maintain separate user profiles, then User DSNs are a good choice. User DSNs can reduce user confusion and promote security by limiting available choices to only those required by a given user.

A System DSN is available to all users and also to services running on the computer. If multiple users of a system need access to the same database, creating one system DSN is less work than creating multiple identical User DSNs.

To learn how to create a DSN, proceed to any of the following sections, which describe the creation of all three types of DSNs.

Creating a User DSN

To create a new User Data Source Name choose the User DSN tab in the 32bit ODBC Control Panel, and, depending on the driver you're installing, proceed either to the "Common SQL Server Driver Steps" section, or to the "Common Microsoft Access (.mdb) Driver Steps" section, later in this chapter.

Creating a System DSN

To create a new System Data Source Name, choose the System DSN tab in the 32bit ODBC Control Panel. Then, proceed to either the "Common SQL Server Driver Steps" section, or the "Common Microsoft Access (.mdb) Driver Steps" section, later in this chapter.

Creating a File DSN

To create a new File DSN, choose the File DSN tab in the 32bit ODBC Control Panel. Then follow these steps:

1. Use the Look In folder selection window to browse for a specific directory holding File DSNs. The default folder location is \Program Files\Common Files\Odbc\Data Sources.
2. Click the Set Directory button to set the directory identified in the Look In window as the default directory for DSNs.
3. To continue creating a new File DSN, click the Add button. The Create New Data Source screen appears (see Figure 16.5).
4. Type an identifiable name (for example, Inventory File DSN), then choose the Next> button. A Create New Data Source confirmation screen appears (see Figure 16.6).
5. If you agree with the information displayed on the confirmation screen, click Finish.
6. At this point you must complete the creation of the File DSN. Depending on which ODBC driver you want to use, proceed to either the "Common SQL Server Driver Steps" section, or the "Common Microsoft Access (.mdb) Driver Steps" section, later in this chapter.

Figure 16.5

This Create New Data Source window allows you to type in the name of the file DSN you are creating.


Figure 16.6

This Create New Data Source window displays confirmation information of the DSN you are about to create.


Common SQL Server Driver Steps

To add a new DSN, click the Add button. You should see the Create New Data Source dialog box (see Figure 16.7). Select the desired ODBC driver (for example, SQL Server) from the list of installed ODBC Drivers. Then click Finish.

Figure 16.7

The Create New Data Source window allows you to select from the currently installed ODBC drivers.

The first screen of the Create a New Data Source to SQL Server Wizard appears (see Figure 16.8). This wizard steps you through the process of creating a new DSN for SQL Server.

Figure 16.8

The first screen of the Create a New Data Source to SQL Server Wizard allows you to name your DSN and Server.

In the Name field, type in the name you want to use for this DSN. If the client program includes a DSN parameter in its ODBC Connect String, then you should type exactly the same name. Using the earlier example, enter InventoryDSN. If the ODBC Connect String does not include a DSN, create a name that is easily identified.

In the Description field you may optionally type a short description to identify the usage of this DSN.

In the Server field, type the name of the server. Do not use an UNC name (a double backslash). If the server is your Windows 98 machine you can choose Local from the drop-down list.

When you are finished, click Next. The second screen of the wizard appears (see Figure 16.9).

Figure 16.9

In Screen 2 of the Create a New Data Source to SQL Server Wizard you can choose how SQL Server will authenticate you.

The DBA can set up the SQL Server to perform user authentication to use Windows NT user accounts, or to use accounts local to SQL Server. The DBA who administers the SQL Server can tell you which authentication method you should choose. Choose the appropriate option from the two choices based on the DBA's answer.

The Client Configuration button is used to locate and change DLLs used for ODBC to network protocol connectivity. Named Pipes is the default choice, but other choices can increase the speed of connection depending on how the SQL Server is configured. Options available under this button are addressed later in the section "Advanced ODBC Tuning Options."

You can complete the rest of the DSN by using the default values, or you can have the DSN Creation Wizard connect to the SQL Server at this time to query it for specific information such as the names of available databases. To gather the specific information, select the checkbox next to Connects to SQL Server to Obtain. If you choose the With Windows NT Authentication option, your current logon UserID and password are used to connect. Otherwise, with the With SQL Server Authentication option you can type a valid UserID and password into the respective Login ID and Password fields. If you don't select the Connects to SQL Server to Obtain box, then default information is used on the following wizard screens.


NOTE: The Logon ID and Password do not pose a security concern because they are not retained in the DSN. They are used only to query the database for specifics during the creation of the DSN.


When you are finished, click the Next button. The third screen of the wizard appears (see Figure 16.10).

If on the previous screen, you chose to let the wizard query the SQL Server for specifics, you can select Change the Default Database To and also select which database you want to use as the default for this DSN. Choosing a specific database can speed the initial connection during user logon.

Figure 16.10

Screen 3 of the Create a New Data Source to SQL Server Wizard allows you to choose the default database for this DSN.

If the wizard did not obtain specifics, then (default) is used and cannot be changed. During user logon to SQL Server the database designated as default for that user account is used.

The second checkbox and two option buttons determine how long to retain temporary prepared SQL Statements on the SQL Server. These selections can influence the speed of application execution and the amount of memory (tempdb) used on the SQL Server. For example, when the checkbox is not selected you cannot create temporary prepared statements on the SQL Server, which causes additional, slower SQL processing but requires less memory utilization. When this option is selected, a prepared statement can be stored for future use, thus increasing access speed upon subsequent use. You may want to ask the application programmer which option works best for a given application.

ANSI syntax uses double quotes for table and column names, and single quotes for strings. SQL Statements generated from Access do not differentiate between single and double quotes. You should deselect the Use ANSI Quoted Identifiers option if Access generates the SQL Statements. You should select Use ANSI Nulls, Paddings and Warnings only if you want to use the ANSI handling for these items.

If on the previous screen you allowed the wizard to query the SQL Server and the SQL Server was set up for failover to an alternate server, you then have the ability to select or deselect the last checkbox; otherwise, this option is grayed out and cannot be changed. This selection determines whether to use the failover server in the event of a failure.

When you are finished, click the Next button. The fourth screen of the wizard appears (see Figure 16.11).

Figure 16.11

This screen allows you to make choices that affect character sets and local representations for Time, Dates, and Currency.

Choices that you make on this screen affect how data and messages are presented to the user. In many cases these options are left at their default values.

The first option allows SQL Server to display error messages and other dialog boxes in any of several languages, including English, French, German, and so on.

The group of four options in the center of the screen allows you to select a specific character- translation set. You should check with the SQL Server DBA to determine if it is necessary to change from the default value.

Select the Use Regional Settings check box if the SQL Server is located in one country and clients are located in other countries. This allows the display of dates, time, and currency using local formats. For example, October 11, 1998, is represented as 11/10/98 internationally and 10/11/98 domestically.

When you are finished, click the Next button. The fifth screen of the wizard appears (see Figure 16.12).

Figure 16.12

Screen 5 of the Create a New Data Source to SQL Server Wizard allows you to create log files of ODBC activity.

If you want to determine whether any query is taking an especially long time to complete, you can select Save Long Running Queries to the Log File. Enter the path and name of the log file you want created. Also set the time threshold in milliseconds. This log can identify long-running queries. You might consider running applications with long queries during non-peak processing periods.

If you want to keep statistics of ODBC operations, you can select the Log ODBC Driver Statistics to the Log File option. Enter the path and filename of the log file you want created. The file generated is tab delimited, and can be analyzed by Excel or any other program that uses this format.

When you are finished, click Finish. The confirmation screen of the wizard appears (see Figure 16.13).

Figure 16.13

The ODBC Microsoft SQL Server Setup screen allows you to review a summary of the DSN you are about to create.

You will probably want to test the DSN before you save it to ensure that it works correctly. To do this, select Test Data Source. You should see a screen similar to Figure 16.14. If the tests completed successfully, click OK twice to complete and save the DSN you just created.

Figure 16.14

The SQL Server ODBC Data Source Test screen shows the results of an connection request using your DSN.

Common Steps for Microsoft Access Driver (.mdb)

Click Add from the User DSN, System DSN, or File DSN tab (refer to Figure 16.4). You will see the Create New Data Source screen (see Figure 16.15).

Figure 16.15

The Create New Data Source screen allows you to select an ODBC driver.

Select the desired ODBC driver (for example, Microsoft Access Driver (.mdb))from the list of installed ODBC Drivers. Then click Finish. The ODBC Microsoft Access 97 Setup screen appears (see Figure 16.16).

Figure 16.16

The ODBC Microsoft Access 97 Setup screen allows you to create a DSN for your Access database as well as to perform several maintenance functions.

A dialog box corresponding to the ODBC Driver you selected appears. In this example it is named ODBC Microsoft Access 97 Setup.

In the Data Source Name field, type in the name you want to use for this DSN. If the client program includes a DSN parameter in its ODBC Connect String, type in exactly the same name. This example creates a DSN named NorthwindDSN. If the ODBC Connect String does not include a DSN, create a name that is easily identifiable.

In the Description field, you may optionally type a description to help distinguish this DSN.

Click Select. The Select Database screen appears (see Figure 16.17).

Figure 16.17

The Select Database dialog box allows you to select the path and filename of the file type database.

The Select Database dialog box allows you to search locally or on the network for the desired database file. Navigate until you locate the Access database for which you want to create a DSN. Select the database name and click OK. You will return to ODBC Microsoft Access 97 Setup screen (refer to Figure 16.16). You should see the name of the database file you selected. Click OK to complete the DSN creation.

Installing or Upgrading ODBC

Windows 98 does not automatically install or upgrade ODBC. Therefore, you may not see a 32bit ODBC icon within Control Panel, or if the 32bit ODBC icon is present, it may not include File DSNs or the ODBC Wizards described earlier in this chapter.

Installing or upgrading ODBC follows identical steps. Open the Add/Remove Programs applet in Control Panel by selecting Start, Settings, Control Panel, Add/Remove Programs. When the Add/Remove Programs Properties applet appears, select the Windows Setup tab and click the Have Disk button. Insert the Windows 98 CD-ROM when the Install From Disk screen appears, choose the Browse button. The Open file dialog box appears next (see Figure 16.18). Select the iis.inf file from the \add-ons\pws directory on your Windows 98 CD-ROM.

Figure 16.18

Use the Open dialog box to select the iis.inf file from your Windows 98 CD-ROM.

Select OK in the Open dialog box and OK again from the Install From Disk dialog box.

The Have Disk screen appears again; choose Microsoft Data Access Components 1.5 and click the Details button (see Figure 16.19). The Microsoft Data Access Components 1.5 screen appears (see Figure 16.20).

Figure 16.19

Use the Have Disk dialog box to select Microsoft Data Access Components 1.5.

Figure 16.20

Use the Microsoft Data Access Components 1.5 dialog box to select Data Sources and ODBC.

Choose both the Data Sources and the MDAC, ADO, ODBC, and OLE DB selections, then click OK. This returns you to the Have Disk screen; click the Install button to install the files.

Troubleshooting ODBC Connection Problems

Problems in establishing an ODBC connection generally fall into one of five categories. These problems should be corrected or ruled out in the following order:

1. Network connectivity problems
2. Security problems
3. Basic ODBC parameter problems
4. Advanced ODBC parameter problems
5. Functionality problems

Correcting Network Connectivity Problems

In order for your Windows 98 machine to connect to a remote server, both machines and the intervening network routers typically must support the transport protocol that you choose to use. There are exceptions, however, such as when you use a tunneling protocol, or if there is mainframe access through an SNA gateway.

If both machines are using TCP/IP, you can use the Ping command to check for basic connectivity.


NOTE: Ping does not work through proxy servers.


If the server is located on a Windows NT or other Server Message Block (SMB)-based server you can use the NET VIEW \\<server-name> command to determine if any share points are visible on the server.

The driver and server ODBC parameters are both used at this time in order for ODBC to start the process of establishing a connection. If you have errors in the connection, be sure these parameters are correct.

Correcting Security Problems

Assuming that basic network connectivity is present, you must next present valid identification in the form of an acceptable UserID and Password to the operating system on the database server. Often this is where people run into the greatest difficulty. If you are connecting to an NT Server you must present the following identification:

Sometimes supplying the proper account information still is not sufficient to establish a connection. Try to establish a persistent connection to a share point on the server that hosts the database that you want to connect to. Sometimes, simply having an existing connection allows the database connection to be made more readily. If the database server has no share points that you can connect to, try to establish a connection to another server in the same NT domain as the database server.

If you are able to successfully log on to the server that hosts your database you have eliminated many variables and unknowns.

Correcting Basic ODBC Parameter Problems

The UID, PWD, and database parameters come into play at this time. The UID and PWD are used to gain access to the database engine. On a SQL Server the UID and PWD can be used by either standard or integrated security. Standard security uses user accounts within SQL Server, while integrated security uses NT domain accounts. If you use incorrect UID or PWD parameters you typically receive a message such as Login failed. If the database server authenticates your account, the database parameter locates the database. If the database name is incorrect, you do not connect. Lastly, the supplied UserID and Password require permission to access the designated Database. Accounts are typically granted access only to those databases for which the user has a business need.

Correcting Advanced ODBC Parameter Problems

Changes made by choosing the Client Configuration... button (refer to Figure 16.9), can make or prevent a client connection to the database server. Basically the client computer and the database server must both be set up with choices that are compatible with each other. For instance, when SQL Server is installed, it defaults to Named Pipes as the IPC mechanism for network communication. Likewise, the default option when creating a DSN for SQL Server is also Named Pipes (see Figure 16.23, later in this chapter). As you can imagine, if either the server or the client is set up with nonmatching values, the mismatch can prevent ODBC communication even though previous checks of connectivity using Ping and the Net Use commands work successfully.

Therefore, if you have not yet been able to establish a connection you may want to check with the DBA for the database server to find out which IPC network choice(s) the database server supports. Then choose a matching option for the client.


NOTE: For more information on IPC network choices see the section named "Advanced ODBC Tuning Options" later in this chapter.


If you are still having difficulty connecting, you can generate a trace of every ODBC command and response. The file output is a standard ASCII file viewable from WordPad. You will most likely need someone familiar with ODBC API programming to interpret the trace and determine where the problem lies.

To perform a trace, select the Tracing tab from the 32bit ODBC applet (see Figure 16.21), then choose Start Tracing Now. Leaving the ODBC Data Source Administrator window open, switch to your app and attempt to connect. Wait for the connection attempt to fail. Return to the Tracing tab of ODBC Data Source Administrator and press the same button, which should now display Stop Tracing Now, and close the ODBC Data Source administrator to release the log file. Use Explorer to locate and display the log file. (SQL.LOG is the default name of the log.)

Figure 16.21

Use the Tracing tab of the 32bit ODBC applet to start and stop tracing ODBC Commands.


NOTE: The logfile can quickly grow to a very large size; for example, opening a connection to the pubs database, reading the Authors table once, and closing the connection creates a logfile of about 117KB.


Correcting Functionality Problems

Sometimes, although an application program is able to establish a connection with a particular database server, the client program does not offer full functionality. Yet the same application is fully functional when connected to a different database server.

This difference in functionality can be due to an ODBC driver that does not support the levels of ODBC conformance required by the application program. Check the version number and manufacturer of the ODBC driver in order to locate specifications as to the level of conformance offered. You can use the ODBC Drivers tab of the 32bit ODBC applet to display the version levels of installed ODBC drivers (see Figure 16.22).

Figure 16.22

Use the ODBC Drivers tab of the 32bit ODBC applet to determine the manufacturer and version number of installed ODBC drivers.

You can also check with the programmer or company that developed the client application to determine the level of conformance required. If the program requires a level of conformance higher that what the ODBC driver provides, then you will need to install a newer version of the driver, or an ODBC driver from another manufacturer, that provides the level of conformance required by the application.

Advanced ODBC Tuning Options

Most options that you can control when creating a DSN have little effect on performance or speed of connection and operation. The performance of the application is much more in the programmer's hands. The programmer's use of SQL queries of varying degrees of efficiency can greatly affect the performance. The options and ODBC parameters under your control generally determine whether you connect at all, rather than how fast.

With that in mind, there are a few things you can do that affect performance. You can change the name of the default database to the name of the database you want to connect to. Refer to Figure 16.10 and the accompanying description for information about creating a SQL Server DSN.

If the database server supports multiple IPC mechanisms, you can also see an increase in speed by choosing a different network library. For instance, changing from Named Pipes to TCP/IP Sockets can speed up the connection process. Be sure to check with the DBA for the database server to find out which IPC network choice(s) the database server supports.


NOTE: Changing the Network Library parameters could cause your client program to stop working, or to start working if it currently is not. Also be aware that integrated security on SQL Server uses only the Named Pipes network library.


To change the network library, click the Client Configuration button (refer to Figure 16.9) and choose the Net Library tab (see Figure 16.23). Choose the Default Network drop-down list to display a list of network IPC types. Select the desired IPC type from the list, then click the Done button. Complete the creation or update of the DSN as described earlier in this chapter.

Figure 16.23

Use the Net Library tab of the 32bit ODBC applet to select a different IPC network type.

Conclusion

The need to configure client workstations for ODBC will increase as corporations in America and around the world migrate their applications to client/server and N-tier models. Fortunately, the wizards included with Windows 98 make it easier than ever before to create and test the DSN's ability to connect.

Because ODBC is so widely used, database customers have a great deal of freedom in selecting both platforms and database vendors. DSNs make it easy for system administrators to react to changes that inevitably occur in today's dynamically changing business environment.


Previous chapterNext chapterContents

Copyright, Macmillan Computer Publishing. All rights reserved.