SQLSetConnectAttr

The SQL Native Client ODBC driver ignores the setting of SQL_ATTR_CONNECTION_TIMEOUT. The SQL Native Client ODBC driver will not time out on any operations other than login and query processing.

The SQL Native Client ODBC driver implements repeatable read transaction isolation as serializable.

SQL Server 2005 adds support for a new transaction isolation attribute, SQL_COPT_SS_TXN_ISOLATION. Setting SQL_COPT_SS_TXN_ISOLATION to SQL_TXN_SS_SNAPSHOT indicates that the transaction will take place under the snapshot isolation level.

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 Native Client ODBC driver supports a number of driver-specific extensions to ODBC connection attributes defined in sqlncli.h. The SQL Native Client ODBC driver may require that the attribute be set prior to connection, or it may ignore the attribute if it is already set. The following table lists restrictions.

SQL_COPT_SS_ANSI_NPW

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.

SQL_COPT_SS_ATTACHDBFILENAME

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.

SQL_COPT_SS_BCP

SQL_COPT_SS_BCP enables bulk copy functions on a connection. For more information, see Bulk Copy Functions.

SQL_COPT_SS_BROWSE_CONNECT

This attribute is used to customize the result set returned by SQLBrowseConnect. SQL_COPT_SS_BROWSE_CONNECT enables or disables the return of additional information from an enumerated instance of SQL Server. This can include information such as whether the server is a cluster, names of different instances, and the version number.

SQL_COPT_SS_BROWSE_SERVER

This attribute is used to customize the result set returned by SQLBrowseConnect. SQL_COPT_SS_BROWSE_SERVER specifies the server name for which SQLBrowseConnect returns the information.

SQL_COPT_SS_CONCAT_NULL

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.

SQL_COPT_SS_ENLIST_IN_DTC

The client calls the Microsoft Distributed Transaction Coordinator (MS DTC) OLE DB 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 MS DTC Distributed Transactions.

SQL_COPT_SS_ENLIST_IN_XA

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 the Microsoft Distributed Transaction Coordinator documentation.

SQL_COPT_SS_FALLBACK_CONNECT

This attribute is no longer supported because SQL Native Client does not support connecting to SQL Server 6.5.

SQL_COPT_SS_FAILOVER_PARTNER

Used to specify the name of the failover partner used for database mirroring in SQL Server 2005, and it is a null terminated character string which must be set before the connection to SQL Server is initially made.

After making the connection, the application can query this attribute using SQLGetConnectAttr to determine the identity of the failover partner. If the primary server has no failover partner this property will return an empty string. This allows a smart application to cache the most recently determined backup server, but such applications should be aware that the information is only updated when the connection is first established, or reset, if pooled, and can become out of date for long term connections.

For more information, see Using Database Mirroring.

SQL_COPT_SS_INTEGRATED_SECURITY

SQL_COPT_SS_INTEGRATED_SECURITY forces use of Windows Authentication for access validation on server login. When Windows Authentication is used, the driver ignores user identifier and password values provided as part of SQLConnect, SQLDriverConnect, or SQLBrowseConnect processing.

SQL_COPT_SS_MARS_ENABLED

This attribute enables or disables Multiple Active Result Sets (MARS). By default, MARS is disabled. This attribute should be set before making a connection to SQL Server. Once the connection SQL Server is opened, MARS will remain enabled or disabled for the life of the connection.

For more information about MARS, see Using Multiple Active Result Sets (MARS).

SQL_COPT_SS_OLDPWD

Password expiration for SQL Authentication is a new feature introduced in SQL Server 2005. The SQL_COPT_SS_OLDPWD attribute has been added to allow the client to provide both the old and the new password for the connection. When this property is set, the provider will not use the connection pool for the first connection or for subsequent connections, since the connection string will contain the “old password” which has now changed.

For more information, see Changing Passwords Programmatically.

SQL_COPT_SS_PERF_DATA

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.

SQL_COPT_SS_PERF_DATA_LOG

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

SQL_COPT_SS_PERF_DATA_LOG_NOW instructs the driver to write a statistics log entry to disk.

SQL_COPT_SS_PERF_QUERY

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.

SQL_COPT_SS_PERF_QUERY_INTERVAL

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

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

This attribute allows you to query and set whether or not the connection will preserve the cursor(s) when you commit/rollback a transaction. The setting is either SQL_PC_ON or SQL_PC_OFF. The default value is SQL_PC_OFF. This setting controls whether or not the driver will close the cursor(s) for you when you call SQLEndTran (or SQLTransact).

SQL_COPT_SS_QUOTED_IDENT

SQL_COPT_SS_QUOTED_IDENT allows quoted identifiers in ODBC and Transact-SQL statements submitted on the connection. By supplying quoted identifiers, the SQL Native Client ODBC driver allows otherwise invalid object names such as "My Table," which contains a space character in the identifier. For more information, see SET QUOTED_IDENTIFIER.

SQL_COPT_SS_TRANSLATE

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.

SQL_COPT_SS_TRUST_SERVER_CERTIFICATE

SQL_COPT_SS_TRUST_SERVER_CERTIFICATE causes the driver to enable or disable certificate validation when using encryption. This attribute is a read/write value, but setting it after a connection has been established has no effect.

Client applications can query this property after a connection has been opened to determine the actual encryption and validation settings in use.

SQL_COPT_SS_TXN_ISOLATION

SQL_COPT_SS_TXN_ISOLATION sets the SQL Server 2005 specific snapshot isolation attribute. Snapshot isolation cannot be set using SQL_ATTR_TXN_ISOLATION because the value is SQL Server specific. However, it can be retrieved using either SQL_ATTR_TXN_ISOLATION or SQL_COPT_SS_TXN_ISOLATION.

For more information about snapshot isolation, see Working with Snapshot Isolation.

SQL_COPT_SS_USE_PROC_FOR_PREP

This attribute is no longer supported because SQL Native Client does not support connecting to SQL Server 6.5.

SQL_COPT_SS_USER_DATA

SQL_COPT_SS_USER_DATA sets the user data pointer. User data is client-owned memory recorded per connection.

SQL_COPT_SS_WARN_ON_CP_ERROR

When this attribute is set to SQL_COPT_YES, you get a warning if there is a loss of data during a code page conversion. This applies to only data coming from the server.

Example

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...

See Also

Reference

Bulk Copy Functions

Concepts

ODBC API Implementation Details
SQLPrepare
SQLGetInfo

Other Resources

SET ANSI_NULLS (Transact-SQL)
SET ANSI_PADDING (Transact-SQL)
SET ANSI_WARNINGS (Transact-SQL)
SET CONCAT_NULL_YIELDS_NULL (Transact-SQL)
SET QUOTED_IDENTIFIER (Transact-SQL)
SQLSetConnectAttr Function

Help and Information

Getting SQL Server 2005 Assistance