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 { -S  servername  -D  datasource  } -U  login  -P  password  

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 

NOTE

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

OLE DB

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.

NOTE

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

ODBC	OLE DB
Data Access API	Database Component API
C-Level API	COM API
SQL-based data	All tabular data
SQL-based standard	COM-based standard
Native providers	Component architecture