Microsoft SQL Server ODBC driver Info

Referenced: http://msdn.microsoft.com/library/en-us/dnodbc/html/odbcsql.asp?frame=true

Setup and Connecting

An ODBC application has two methods of giving an ODBC driver the information the driver needs to connect to the proper server and database. Either the application can connect using an existing ODBC data source containing this information, or it can call qDriverCon, which provides the information in the connection string parameter.

Setting up a Data Source

ODBC data sources contain information that tells a driver how to connect to a database. ODBC data sources can be created by using the ODBC Administrator application in Control Panel or by an application calling the ODBC Extenders qConfigData function.

Data source definitions are stored in C:\Windows\System\Odbc.ini for the Microsoft Windows� version 3.x and Windows for Workgroups version 3.x operating systems.

Win32 data sources fall into one of two categories:

Windows NT user-specific data sources and Windows 95 data sources
On the Microsoft Windows NT� operating system, user data sources are specific to the Windows NT account under which they were defined. User-specific data sources are not always visible to applications running as Windows NT services. Windows 95 data sources are stored in the following registry key:

HKEY_CURRENT_USER\Software\ODBC\Odbc.ini.

Windows NT�system data sources
On Windows NT, system data sources are visible to all Windows NT accounts on the computer. System data sources are always visible to applications running as Windows NT services. The ODBC driver manager that ships with Microsoft Office 97 also supports system data sources on Windows 95 clients. Windows NT system data sources are stored in the following registry key:

HKEY_LOCAL_MACHINE\Software\ODBC\Odbc.ini.

Information about the drivers installed on a client is stored in C:\Windows\System\Odbcinst.ini in Windows 3.x or Windows for Workgroups 3.x and in HKEY_LOCAL_MACHINE\Software\ODBC\Odbcinst.ini in Windows NT and Windows 95.

Each driver needs to store driver-specific information in its data sources. When a user adds a data source using ODBC Administrator, the driver displays a dialog box, where the user specifies data source information. When a data source is defined with qConfigData, the function accepts an attribute string parameter that can contain driver-specific keywords. All of the qConfigData driver-specific keywords for the SQL Server ODBC driver have counterparts in the dialog box that displays when using ODBC Administrator.

Here's an example qConfigData call that sets up a SQL Server data source referencing a server using DHCP on TCP/IP:

ODBC_ADD_DSN = 1
szDriver = "SQL Server";
szAttributes = "DSN=my65dsn|DESCRIPTION=SQLConfigDSN Sample|SERVER=my65server|ADDRESS=HRServer|NETWORK=dbmssocn|DATABASE=pubs|";
retcode = qConfigData(ODBC_ADD_DSN,szDriver,szAttributes)

Driver-specific qConfigData Keywords

The following sections describe the driver-specific keywords supported by the Microsoft SQL Server ODBC driver.

SERVER, NETWORK, and ADDRESS
The SERVER, NETWORK, and ADDRESS parameters associate a data source with a specific instance of SQL Server on the network. These parameters are directly related to the advanced entries created with the SQL Server Client Configuration Utility:

The SERVER parameter specifies a name or label for the connection entry.

The NETWORK parameter is the name of the Net-Library module to use, without the .dll suffix (for example, Dbmssocn, not Dbmssocn.dll).

The ADDRESS parameter is the network address of the Windows NT server running SQL Server. If ADDRESS is present, it is always used as the network address for the connection. If ADDRESS is not present, then SERVER is used as the network address for the connection.

Here's an example entry to make a named pipes connection to a server:

SERVER=xyz;NETWORK=dbnmpntw;ADDRESS=HRServer

The following entry evaluates to the same network address:

SERVER=HRServer;NETWORK=dbnmpntw

Here's an example entry to make a sockets connection to the same computer:

SERVER=tcpxyz;NETWORK=dbmssocn;ADDRESS=123.123.123.123,1433

There are two special cases to consider:

Connecting to a SQL Server running on the same computer as the client. The ODBC data source for this case is specified as:

SERVER=(local);NETWORK=(default);ADDRESS=(default)

When using this data source, the driver attempts to connect to a SQL Server on the same computer using Windows NT local-named pipes instead of a network implementation of named pipes.

Setting up a data source that connects to a server using whatever Net-Library is currently set as the default on the client. An example of an entry for this case is:

SERVER=HRServer;NETWORK=(default);ADDRESS=(default)

The default Net-Library is set using the SQL Server Client Configuration Utility.

The SERVER, NETWORK, and ADDRESS parameters specified on SQL Server ODBC driver data sources operate the same way as the Server, DLL, and Connection String parameters specified for advanced entries made with the SQL Server Client Configuration Utility. For more information about the advanced-entry parameters, see the Microsoft SQL Server Administrator's Companion. The same parameters can be specified in the data source creation dialog box displayed in ODBC Administrator.

The relationship between the parameters is illustrated in the following table.

qConfigData ODBC Administrator SQL Client Configuration Utility
SERVER Server Server
NETWORK Network Library DLL
ADDRESS Network Address Connection String

If a data source is defined with the SERVER, NETWORK, and ADDRESS parameters, a SQL Server advanced connection entry is made in the registry, and can be viewed using the SQL Client Configuration Utility.

DATABASE
This parameter specifies the default database for the ODBC data source.

LANGUAGE
This parameter specifies the default national language to use.

OEMTOANSI
This parameter specifies whether to convert extended characters to OEM values.

SQL Server is usually run with one of three code pages:

437 code page.
The default code page for U.S. MS-DOS computers.

850 code page.
The code page typically used by UNIX systems.

ISO 8859-1 (Lantin1 or ANSI) code page.
The code page defined as a standard by the ANSI and ISO standards organizations. The default code page for U.S. Windows computers. Sometimes called the 1252 code page.

The 437 and 850 code pages are sometimes collectively referred to as the OEM code pages.

All three code pages define 256 different values to use in representing characters. The values from 0 to 128 represent the same characters in all three code pages. The values from 129 to 255, which are known as the extended characters, represent different characters in all three code pages.

Because ODBC applications are Windows applications, they generally use ANSI code page 1252. If they are communicating with a SQL Server also running ANSI code page 1252, there is no need for character-set conversion. If they connect to a server running a 437 or 850 code page however, the driver must be informed that it should convert extended characters from their 1252 values to 437 or 850 values before sending them to the server. In this case, the data source should have OEMTOANSI=YES. For a more in-depth discussion of SQL Server code pages, see Microsoft Knowledge Base article Q153449.

TRANSLATIONDLL
This parameter specifies the name of the ODBC translation DLL to use with the data source.

TRANSLATIONNAME
This parameter specifies the name of the translator to use with the data source.

TRANSLATIONOPTION
This parameter specifies whether translation should be done on the data going to SQL Server. YES specifies translation; NO specifies no translation. For more information about ODBC translation, see the ODBC 2.0 Programmer's Reference.

USEPROCFORPREPARE
This parameter specifies whether the driver generates stored procedures to support the ODBC SQLPrepare function. For more information, see "SQLExecDirect vs. SQLPrepare/SQLExecute."

The following driver-specific qConfigData keywords are new in SQL Server 6.5 SP2.

QuotedID
This parameter specifies whether the driver should issue a SET QUOTED IDENTIFIERS ON option when connecting to a SQL Server version 6.0 or later database. YES specifies QUOTED_IDENTIFIERS is ON; NO specifies the option is OFF. For more information, see "SET Options Used by the Driver."

AnsiNPW
This parameter specifies whether the driver should SET ON the ANSI_NULLS, ANSI_PADDING, and ANSI_WARNINGS options when connecting to a SQL Server version 6.5 or later database. YES specifies the options are ON; NO specifies they are OFF. For more information, see "SET Options Used by the Driver."

The following driver-specific qConfigData keywords are new in SQL Server 6.5.

QueryLogFile
This parameter specifies the file name the driver should use to log long-running queries. Include the full path name for the file. For more information, see "ODBC Driver Profiling Features."

QueryLog_ON
This parameter specifies whether the data source should do query profiling. 1 specifies profiling is done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."

QueryLogTime
This parameter specifies the interval for long-running queries. The interval is specified in milliseconds. If a query is outstanding for a period exceeding the QueryLogTime, it is written to the QueryLogFile. For more information, see "ODBC Driver Profiling Features."

StatsLogFile
This parameter specifies the file name the driver should use to log long performance statistics. Include the full path name for the file. For more information, see "ODBC Driver Profiling Features."

StatsLog_On
This parameter specifies whether the data source should log performance statistics. 1 specifies profiling is done; omitting the parameter specifies no profiling. For more information, see "ODBC Driver Profiling Features."

Trusted_Connection
This parameter specifies whether the data source should use trusted connections when connecting to SQL Server. 1 specifies trusted connections; omitting the parameter specifies no trusted connections. For more information, see "Integrated and Standard Security."

Article ID:   W16341

File Created: 2005:02:18:12:19:54

Last Updated: 2005:02:18:12:19:54

Database Search

TechHome