Installing ODBC

An alternative to specifying all of the connection information (including server name, user, and password) when connecting to SQL Server is to set up an ODBC datasource. Each ODBC datasource has a unique datasource name (DSN) that can be referenced by ODBC applications. The DSN definition contains all of the pertinent connection information that allows applications to simply reference the DSN to connect to the database server.

The ODBC Administration tool and SQL Server driver are installed from the SQL Server CD-ROM. After it is installed, you can use the ODBC Administrator, accessed from the Control Panel, to create user, system, or file datasources:

  • User DSNs are local to a computer and can be used by only the current user.

  • System DSNs are local to a computer, rather than dedicated to a user. This arrangement allows the system?or any user with the correct privileges?to use the datasource.

  • File DSNs are stored in files on the client machine instead of the registry.

Configuring ODBC Datasources

To configure ODBC datasources, you can use the ODBC Datasource Administrator by selecting the ODBC applet in the Control Panel. With this tool, you can check driver versions and add and modify datasources that utilize the SQL Server ODBC driver. When you create or modify a SQL Server datasource, the Microsoft SQL Server DSN Configuration Wizard opens to help step you through the configuration.

Troubleshooting ODBC Connections

Most of the same steps described in the "Troubleshooting Client Connections" section earlier in this chapter also apply to investigating ODBC connection problems. However, there is one additional tool in your ODBC repertory: odbcping. odbcping allows you to test the visibility of a server through a datasource and to check whether ODBC is correctly installed. This 32-bit utility is not installed by default, but it can be copied from the SQL Server installation disc. The syntax for odbcping is shown here:

odbcping { -Sservername | -Ddatasource } -Ulogin -Ppassword 

In this syntax, servername is the name of the server to which you want to test the connection, datasource is the ODBC datasource name on the local machine, and login and password are valid values for that server. odbcping returns either the version of the SQL Server ODBC driver and the server name if successful, or an error message as specified by the ODBC driver.

Following is an example verifying a connection through a datasource:

odbcping /DWin2000svrDSN /Usimon /Psecret 

Next is an example of connecting directly to a server:

odbcping /SWin2000svr /Usimon /Psecret 


The odbcping utility is not installed as part of the SQL Server 2000 installation, but it can be copied from the installation disc.


There is some misunderstanding about where the OLE DB interface from Microsoft fits in the grand scheme of client connectivity. OLE DB is actually a specification for a data access interface rather than an interface. It is intended to provide the basis for client access to a wide range of data storage systems.

Microsoft has provided a guide, available from its Web site, shown in Table 9.2, for which technology should be used for a given situation.

Table 9.2. ODBC Versus OLE DB
Situation Technology
Accessing standard relational databases from a non-OLE environment ODBC is the best choice.
Exposing a data interface to non-SQL data OLE DB is the best choice
Programming in an OLE environment OLE DB is the best choice.
Building interoperable database components. OLE DB is the only choice.


An integral part of OLE DB is a driver manager that enables OLE DB clients to talk to ODBC providers.

Table 9.3 shows the actual technical differences between the ODBC and OLE DB interfaces.

Table 9.3. Technical Differences Between ODBC and OLE DB
Data Access API Database Component API
SQL-based data All tabular data
SQL-based standard COM-based standard
Native providers Component architecture

    Part III: SQL Server Administration
    Part IV: Transact-SQL
    Part V: SQL Server Internals and Performance Tuning
    Part VI: Additional SQL Server Features