Allocating a Connection Handle

THIS TOPIC APPLIES TO: yesSQL ServeryesAzure SQL DatabaseyesAzure SQL Data Warehouse yesParallel Data Warehouse

Before the application can connect to a data source or driver, it must allocate a connection handle. This is done by calling SQLAllocHandle with the HandleType parameter set to SQL_HANDLE_DBC and InputHandle pointing to an initialized environment handle.

The characteristics of the connection are controlled by setting connection attributes. For example, because transactions occur at the connection level, the transaction isolation level is a connection attribute. Similarly, the login time-out, or number of seconds to wait while trying to connect before timing out, is a connection attribute.

Connection attributes are set with SQLSetConnectAttr, and their current settings are retrieved with SQLGetConnectAttr. If SQLSetConnectAttr is called before a connection is attempted, the ODBC Driver Manager stores the attributes in its connection structure and sets them in the driver as part of the connection process. Some connection attributes must be set before the application attempts to connect; others can be set after the connection has completed. For example, SQL_ATTR_ODBC_CURSORS must be set before a connection is made, but SQL_ATTR_AUTOCOMMIT can be set after connecting.

Applications running against SQL Server version 7.0 or later can sometimes improve their performance by resetting the tabular data stream (TDS) network packet size. The default packet size is set at the server, at 4 KB. A packet size of 4 KB to 8 KB generally gives the best performance. If testing shows that it performs better with a different packet size, the application can reset the packet size. ODBC applications can do this before connecting by calling SQLSetConnectAttr with the SQL_ATTR_PACKET_SIZE option. Some applications perform better with a larger packet size, but performance improvements are generally minimal for packet sizes larger than 8 KB.

The SQL Server Native Client ODBC driver has a number of extended connection attributes that an application can use to increase its functionality. Some of these attributes control the same options that can be specified in data sources and used to override whatever option is set in a data source. For example, if an application uses quoted identifiers, it can set the driver-specific attribute SQL_COPT_SS_QUOTED_IDENT to SQL_QI_ON to ensure this option is always set regardless of the setting in any data source.

See Also

Communicating with SQL Server (ODBC)