The Microsoft® SQL Server™ ODBC driver ignores the setting of SQL_ATTR_CONNECTION_TIMEOUT. The SQL Server ODBC driver will not time out on any operations other than login and query processing.
The SQL Server ODBC driver implements repeatable read transaction isolation as serializable. Setting SQL_ATTR_TXN_ISOLATION to SQL_TXN_REPEATABLE_READ is exactly equivalent to setting the transaction isolation attribute to SQL_TXN_SERIALIZABLE.
Promoting ODBC statement attributes to connection attributes can have unintended consequences. Statement attributes that request server cursors for result set processing can be promoted to the connection. For example, setting the ODBC statement attribute SQL_ATTR_CONCURRENCY to a value more restrictive than the default SQL_CONCUR_READ_ONLY directs the driver to use dynamic cursors for all statements submitted on the connection. Executing an ODBC catalog function on a statement on the connection returns SQL_SUCCESS_WITH_INFO and a diagnostic record indicating that the cursor behavior has been changed to read-only. Attempting to execute a Transact-SQL SELECT statement containing a COMPUTE clause on the same connection fails.
The SQL Server ODBC driver supports a number of driver-specific extensions to ODBC connection attributes that are defined in Odbcss.h. The SQL Server ODBC driver may require that the attribute be set prior to connection, or it may ignore the attribute if it is already set. See the following table for restrictions.
SQL Server attribute |
Set before or after connection to server |
|---|---|
| SQL_COPT_SS_ANSI_NPW | Before |
| SQL_COPT_SS_ATTACHDBFILENAME | Before |
| SQL_COPT_SS_BCP | Before |
| SQL_COPT_SS_CONCAT_NULL | Before |
| SQL_COPT_SS_ENLIST_IN_DTC | After |
| SQL_COPT_SS_ENLIST_IN_XA | After |
| SQL_COPT_SS_FALLBACK_CONNECT | Before |
| SQL_COPT_SS_INTEGRATED_SECURITY | Before |
| SQL_COPT_SS_PERF_DATA | After |
| SQL_COPT_SS_PERF_DATA_LOG | After |
| SQL_COPT_SS_PERF_DATA_LOG_NOW | After |
| SQL_COPT_SS_PERF_QUERY | After |
| SQL_COPT_SS_PERF_QUERY_INTERVAL | After |
| SQL_COPT_SS_PERF_QUERY_LOG | After |
| SQL_COPT_SS_PRESERVE_CURSORS | Before |
| SQL_COPT_SS_QUOTED_IDENT | Either |
| SQL_COPT_SS_TRANSLATE | Either |
| SQL_COPT_SS_USE_PROC_FOR_PREP | Either |
| SQL_COPT_SS_USER_DATA | Either |
SQL_COPT_SS_ANSI_NPW enables or disables the use of SQL-92 handling of NULL in comparisons and concatenation, character data type padding, and warnings. For more information, see SET ANSI_NULLS, SET ANSI_PADDING, SET ANSI_WARNINGS, and SET CONCAT_NULL_YIELDS_NULL.
| Value | Description |
|---|---|
| SQL_AD_ON | Default. The connection uses SQL-92 default behavior handling NULL, padding and warnings. |
| SQL_AD_OFF | The connection uses SQL Server-defined handling of NULL, character data type padding and warnings. |
SQL_COPT_SS_ATTACHDBFILENAME specifies the name of the primary file of an attachable database. This database is attached and becomes the default database for the connection. To use SQL_COPT_SS_ATTACHDBFILENAME you must specify the name of the database as the value of the connection attribute SQL_ATTR_CURRENT_CATALOG or in the DATABASE = parameter of a SQLDriverConnect. If the database was previously attached, SQL Server will not reattach it. This option is not valid when connected to SQL Server version 6.5 or earlier.
| Value | Description |
|---|---|
| SQLPOINTER to a character string | The string contains the name of the primary file for the database to attach. Include the full path name of the file. |
SQL_COPT_SS_BCP enables bulk copy functions on a connection. For more information, see Bulk-Copy Functions.
| Value | Description |
|---|---|
| SQL_BCP_OFF | Default. Bulk copy functions are not available on the connection. |
| SQL_BCP_ON | Bulk copy functions are available on the connection. |
SQL_COPT_SS_CONCAT_NULL enables or disables the use of SQL-92 handling of NULL when concatenating strings. For more information, see SET CONCAT_NULL_YIELDS_NULL.
| Value | Description |
|---|---|
| SQL_CN_ON | Default. The connection uses SQL-92 default behavior for handling NULL values when concatenating strings. |
| SQL_CN_OFF | The connection uses SQL Server-defined behavior for handling NULL values when concatenating strings. |
The client calls the MS DTC OLE ITransactionDispenser::BeginTransaction method to begin an MS DTC transaction and create an MS DTC transaction object that represents the transaction. The application then calls SQLSetConnectAttr with the SQL_COPT_SS_ENLIST_IN_DTC option to associate the transaction object with the ODBC connection. All related database activity will be performed under the protection of the MS DTC transaction. The application calls SQLSetConnectAttr with SQL_DTC_DONE to end the connection’s DTC association. For more information, see your Microsoft Distributed Transaction Coordinator documentation.
| Value | Description |
|---|---|
| DTC object* | The MS DTC OLE transaction object that specifies the transaction to export to SQL Server. |
| SQL_DTC_DONE | Delimits the end of a DTC transaction. |
To begin an XA transaction with an XA-compliant Transaction Processor (TP), the client calls the X/Open tx_begin function. The application then calls SQLSetConnectAttr with a SQL_COPT_SS_ENLIST_IN_XA parameter of TRUE to associate the XA transaction with the ODBC connection. All related database activity will be performed under the protection of the XA transaction. To end an XA association with an ODBC connection, the client must call SQLSetConnectAttr with a SQL_COPT_SS_ENLIST_IN_XA parameter of FALSE. For more information, see your Microsoft Distributed Transaction Coordinator documentation.
This attribute is valid only when connected to SQL Server 6.5. It applies only to standby servers. It does not apply to a virtual server in a cluster/failover configuration. SQL_COPT_SS_FALLBACK_CONNECT enables fallback attempts on a connection. When successfully connected to the primary server, the SQL Server ODBC driver automatically determines the current fallback server and verifies that fallback information is stored in the Registry. If an attempt to connect to a primary server fails (the connection time-out must be greater than 0 for this to occur), the SQL Server ODBC driver will attempt to connect to the fallback server. For more information about configuring SQL Server 7.0 to support clustering, see Configuring SQL Server Failover Support.
| Value | Description |
|---|---|
| SQL_FB_OFF | Default. Fallback connection processing is not performed on connect. |
| SQL_FB_ON | Fallback connection will be attempted on login time-out. |
SQL_COPT_SS_INTEGRATED_SECURITY forces use of Windows NT Authentication for access validation on server login. When Windows NT Authentication is used, the driver ignores user identifier and password values provided as part of SQLConnect, SQLDriverConnect, or SQLBrowseConnect processing.
| Value | Description |
|---|---|
| SQL_IS_OFF | Default. SQL Server Authentication is used to validate user identifier and password on login. |
| SQL_IS_ON | Windows NT Authentication Mode is used to validate a user’s access rights to the SQL Server. |
SQL_COPT_SS_PERF_DATA starts or stops performance data logging. The data log file name must be set prior to starting data logging. See SQL_COPT_SS_PERF_DATA_LOG below.
| Value | Description |
|---|---|
| SQL_PERF_START | Starts the driver sampling performance data. |
| SQL_PERF_STOP | Stops the counters from sampling performance data. |
SQL_COPT_SS_PERF_DATA_LOG assigns the name of the log file used to record performance data. The log file name is an ANSI or Unicode, null-terminated string depending upon application compilation. The StringLength argument should be SQL_NTS.
SQL_COPT_SS_PERF_DATA_LOG_NOW instructs the driver to write a statistics log entry to disk.
SQL_COPT_SS_PERF_QUERY starts or stops logging for long running queries. The query log file name must be supplied prior to starting logging. The application can define “long running” by setting the interval for logging.
| Value | Description |
|---|---|
| SQL_PERF_START | Starts long running query logging. |
| SQL_PERF_STOP | Stops logging of long running queries. |
SQL_COPT_SS_PERF_QUERY_INTERVAL sets the query logging threshold in milliseconds. Queries that do not resolve within the threshold are recorded in the long running query log file. There is no upper limit on the query threshold. A query threshold value of zero causes logging of all queries.
SQL_COPT_SS_PERF_QUERY_LOG assigns the name of a log file for recording long running query data. The log file name is an ANSI or Unicode, null-terminated string depending upon application compilation. The StringLength argument should be SQL_NTS.
SQL_COPT_SS_PRESERVE_CURSORS defines the behavior of cursors when manual-commit mode is used. The behavior is exposed as transactions are either committed or rolled back using SQLEndTran.
| Value | Description |
|---|---|
| SQL_PC_OFF | Default. Cursors are closed on SQLEndTran. |
| SQL_PC_ON | Cursors remain open on SQLEndTran. |
SQL_COPT_SS_QUOTED_IDENT allows quoted identifiers in ODBC and Transact-SQL statements submitted on the connection. By supplying quoted identifiers, the SQL Server ODBC driver allows otherwise illegal object names such as “My Table,” which contains a space character in the identifier. For more information, see SET QUOTED_IDENTIFIER.
| Value | Description |
|---|---|
| SQL_QI_OFF | The SQL Server connection does not allow quoted identifiers in submitted Transact-SQL. |
| SQL_QI_ON | Default. The connection allows quoted identifiers in submitted Transact-SQL. |
SQL_COPT_SS_TRANSLATE causes the driver to translate characters between the client and server code pages as MBCS data is exchanged. The attribute affects only data stored in SQL Server char, varchar, and text columns.
| Value | Description |
|---|---|
| SQL_XL_OFF | The driver does not translate characters from one code page to another in character data exchanged between the client and the server. |
| SQL_XL_ON | Default. The driver translates characters from one code page to another in character data exchanged between the client and the server. The driver automatically configures the character translation, determining the code page installed on the server and that in use by the client. |
This attribute is only valid when connected to SQL Server 6.5 or earlier. SQL_COPT_SS_USE_PROC_FOR_PREP defines the use of temporary stored procedures when ODBC and Transact-SQL statements are prepared for execution. For more about prepared statement execution, see SQLPrepare.
| Value | Description |
|---|---|
| SQL_UP_OFF | The driver does not generate stored procedures when the application prepares statements. |
| SQL_UP_ON | Default. The driver generates a temporary stored procedure when a statement is prepared. The stored procedure is dropped when the application disconnects from the server. |
| SQL_UP_ON_DROP | The driver generates a temporary stored procedure when a statement is prepared. The stored procedure is dropped when the statement handle is freed. |
SQL_COPT_SS_USER_DATA sets the user data pointer. User data is client-owned memory that is recorded per connection.
This example logs performance data.
SQLPERF* pSQLPERF;
SQLINTEGER nValue;
// See if you are already logging. SQLPERF* will be NULL if not.
SQLGetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA, &pSQLPERF,
sizeof(SQLPERF*), &nValue);
if (pSQLPERF == NULL)
{
// Set the performance log file name.
SQLSetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA_LOG,
(SQLPOINTER) "\\My LogDirectory\\MyServerLog.txt", SQL_NTS);
// Start logging...
SQLSetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA,
(SQLPOINTER) SQL_PERF_START, SQL_IS_INTEGER);
}
else
{
// Take a snapshot now so that your performance statistics are discernible.
SQLSetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA_LOG_NOW, NULL, 0);
}
// ...perform some action...
// ...take a performance data snapshot...
SQLSetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA_LOG_NOW, NULL, 0);
// ...perform more actions...
// ...take another snapshot...
SQLSetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA_LOG_NOW, NULL, 0);
// ...and disable logging.
SQLSetConnectAttr(hDbc, SQL_COPT_SS_PERF_DATA,
(SQLPOINTER) SQL_PERF_STOP, SQL_IS_INTEGER);
// Continue on...
| Bulk-Copy Functions | SET QUOTED_IDENTIFIER |
| SET ANSI_PADDING | SQLPrepare |
| SET ANSI_WARNINGS | SET ANSI_NULLS |
| SET CONCAT_NULL_YIELDS_NULL |