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:
Configuring ODBC DatasourcesTo 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 ConnectionsMost 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
OLE DBThere 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
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
|