Edit

Share via

SQLSetConnectAttr

  • Article

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

The SQL Server Native Client ODBC driver ignores the setting of SQL_ATTR_CONNECTION_TIMEOUT.

SQL_ATTR_TRANSLATE_LIB is also ignored; specifying another translation library is not supported. To allow applications to easily be ported to use a Microsoft ODBC driver for SQL Server, any value set with SQL_ATTR_TRANSLATE_LIB will be copied into and out of a buffer in the Driver Manager.

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

SQL Server 2005 (9.x) introduced 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.

Note

SQL_ATTR_TXN_ISOLATION can be used to set all other isolation levels except for SQL_TXN_SS_SNAPSHOT. If you want to use snapshot isolation, you must set SQL_TXN_SS_SNAPSHOT through SQL_COPT_SS_TXN_ISOLATION. However, you can retrieve the isolation level by using either SQL_ATTR_TXN_ISOLATION or SQL_COPT_SS_TXN_ISOLATION.

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 Native Client ODBC driver supports a number of driver-specific extensions to ODBC connection attributes defined in sqlncli.h. The SQL Server 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 Server attribute Set before or after connection to server
SQL_COPT_SS_ANSI_NPW Before
SQL_COPT_SS_APPLICATION_INTENT Before
SQL_COPT_SS_ATTACHDBFILENAME Before
SQL_COPT_SS_BCP Before
SQL_COPT_SS_BROWSE_CONNECT Before
SQL_COPT_SS_BROWSE_SERVER Before
SQL_COPT_SS_CONCAT_NULL Before
SQL_COPT_SS_CONNECTION_DEAD After
SQL_COPT_SS_ENCRYPT Before
SQL_COPT_SS_ENLIST_IN_DTC After
SQL_COPT_SS_ENLIST_IN_XA After
SQL_COPT_SS_FALLBACK_CONNECT Before
SQL_COPT_SS_FAILOVER_PARTNER Before
SQL_COPT_SS_INTEGRATED_SECURITY Before
SQL_COPT_SS_MARS_ENABLED Before
SQL_COPT_SS_MULTISUBNET_FAILOVER Before
SQL_COPT_SS_OLDPWD 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_TRUST_SERVER_CERTIFICATE Before
SQL_COPT_SS_TXN_ISOLATION Either
SQL_COPT_SS_USE_PROC_FOR_PREP Either
SQL_COPT_SS_USER_DATA Either
SQL_COPT_SS_WARN_ON_CP_ERROR Before

Using a pre-connection attribute and the equivalent Transact-SQL command for the same session, database, or SQL Server state can produce unexpected behavior. For example,

SQLSetConnectAttr(SQL_COPT_SS_QUOTED_IDENT, SQL_QI_ON) // turn ON via attribute  
SQLDriverConnect(...);  
SQLExecDirect("SET QUOTED_IDENTIFIER OFF") // turn OFF via Transact-SQL  
SQLSetConnectAttr(SQL_ATTR_CURRENT_CATALOG, ...) // restores to pre-connect attribute value

SQL_COPT_SS_ANSI_NPW

SQL_COPT_SS_ANSI_NPW enables or disables the use of ISO 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 ANSI default behavior for handling NULL comparisons, padding, warnings, and NULL concatenations.
SQL_AD_OFF The connection uses SQL Server-defined handling of NULL, character data type padding, and warnings.

If you use connection pooling, SQL_COPT_SS_ANSI_NPW should be set in the connection string, rather than with SQLSetConnectAttr. After a connection has been made, any attempt to change this attribute will fail silently when connection pooling is used.

SQL_COPT_SS_APPLICATION_INTENT

Declares the application workload type when connecting to a server. Possible values are Readonly and ReadWrite. For example:

SQLSetConnectAttr(hdbc, SQL_COPT_SS_APPLICATION_INTENT, TEXT("Readonly"), SQL_NTS)

The default is ReadWrite. For more information about SQL Server Native Client's support for Always On availability groups AGs, see SQL Server Native Client Support for High Availability, Disaster Recovery.

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.

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

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

Value Description
SQL_MORE_INFO_NO Default. Returns a list of servers.
SQL_MORE_INFO_YES SQLBrowseConnect returns an extended string of server properties.

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.

Value Description
computername SQLBrowseConnect returns a list of instances of SQL Server on the specified computer. Double backslashes (\\) should not be used for the server name (for example, instead of \\MyServer, MyServer should be used).
NULL Default. SQLBrowseConnect returns information for all servers in the domain.

SQL_COPT_SS_CONCAT_NULL

SQL_COPT_SS_CONCAT_NULL enables or disables the use of ISO handling of NULL when concatenating strings. For more information, see SET CONCAT_NULL_YIELDS_NULL.

Value Description
SQL_CN_ON Default. The connection uses ISO 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.

SQL_COPT_SS_ENCRYPT

Controls encryption for a connection.

Encryption uses the certificate on the server. This must be verified by a certificate authority, unless the connection attribute SQL_COPT_SS_TRUST_SERVER_CERTIFICATE is set to SQL_TRUST_SERVER_CERTIFICATE_YES or the connection string contains "TrustServerCertificate=yes". If either of these conditions is true, a certificate generated and signed by the server can be used to encrypt the connection if no certificate is on the server.

Value Description
SQL_EN_ON The connection will be encrypted.
SQL_EN_OFF The connection will not be encrypted. This is the default.

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.

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.

SQL_COPT_SS_ENLIST_IN_XA

To begin an XA transaction with an XA-compliant Transaction Processor (TP), the client calls the Open Group 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.

SQL_COPT_SS_FAILOVER_PARTNER

Used to specify or retrieve the name of the failover partner used for database mirroring in SQL Server, 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.

Value Description
SQL_IS_OFF Default. SQL Server Authentication is used to validate user identifier and password on login.
SQL_IS_ON Windows Authentication Mode is used to validate a user's access rights to the SQL Server.

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.

Value Description
SQL_MARS_ENABLED_NO Default. Multiple Active Result Sets (MARS) is disabled.
SQL_MARS_ENABLED_YES MARS is enabled.

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

SQL_COPT_SS_MULTISUBNET_FAILOVER

If your application is connecting to a Always On availability groups availability group (AG) on different subnets, this connection property configures SQL Server Native Client to provide faster detection of and connection to the (currently) active server. For example:

SQLSetConnectAttr(hdbc, SQL_COPT_SS_MULTISUBNET_FAILOVER, SQL_IS_ON, SQL_IS_INTEGER)

For more information about SQL Server Native Client's support for Always On availability groups AGs, see SQL Server Native Client Support for High Availability, Disaster Recovery.

Value Description
SQL_IS_ON SQL Server Native Client provides faster reconnection if there is a failover.
SQL_IS_OFF SQL Server Native Client will not provide faster reconnection if there is a failover.

SQL_COPT_SS_OLDPWD

Password expiration for SQL Server Authentication was introduced in SQL Server 2005 (9.x). 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.

Value Description
SQL_COPT_SS_OLD_PASSWORD SQLPOINTER to a character string containing the old password. This value is write-only, and must be set before connection to the server.

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.

Value Description
SQL_PERF_START Starts the driver sampling performance data.
SQL_PERF_STOP Stops the counters from sampling performance data.

For more information, see SQLGetConnectAttr.

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. The StringLength argument should be SQL_NTS.

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.

Value Description
SQL_PERF_START Starts long running query logging.
SQL_PERF_STOP Stops logging of long running queries.

For more information, see SQLGetConnectAttr.

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 or the length of the string in bytes.

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

Value Description
SQL_PC_OFF Default. Cursors are closed when transaction is committed or rolled back using SQLEndTran.
SQL_PC_ON Cursors are not closed when transaction is committed or rolled back using SQLEndTran, except when using a static or keyset cursor in asynchronous mode. If a rollback is issued while the population of the cursor is not complete, the cursor is closed.

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

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

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

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.

Value Description
SQL_TRUST_SERVER_CERTIFICATE_NO Default. Encryption without certificate validation is not enabled.
SQL_TRUST_SERVER_CERTIFICATE_YES Encryption without certificate validation is enabled.

SQL_COPT_SS_TXN_ISOLATION

SQL_COPT_SS_TXN_ISOLATION sets the SQL Server 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.

Value Description
SQL_TXN_SS_SNAPSHOT Indicates that from one transaction you cannot see changes made in other transactions and that you cannot see changes even when requerying.

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

SQL_COPT_SS_USE_PROC_FOR_PREP

This attribute is no longer supported.

SQL_COPT_SS_USER_DATA

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

For more information, see SQLGetConnectAttr.

SQL_COPT_SS_WARN_ON_CP_ERROR

This attribute determines whether you will get a warning if there is a loss of data during a code page conversion. This applies to only data coming from the server.

Value Description
SQL_WARN_YES Generate warnings when data loss is encountered during codepage conversion.
SQL_WARN_NO (Default) Do not generate warnings when data loss is encountered during codepage conversion.

SQLSetConnectAttr Support for Service Principal Names (SPNs)

SQLSetConnectAttr can be used to set the value of the new connection attributes SQL_COPT_SS_SERVER_SPN and SQL_COPT_SS_FAILOVER_PARTNER_SPN. These attributes cannot be set when a connection is open; if you attempt to set these attributes when a connection is open, error HY011 is returned with the message "Operation invalid at this time". (SQLSetConnectOption can also be used to set these values.)

For more information about SPNs, see Service Principal Names (SPNs) in Client Connections (ODBC).

SQL_COPT_SS_CONNECTION_DEAD

This is a read-only attribute.

For more information about SQL_COPT_SS_CONNECTION_DEAD, see SQLGetConnectAttr and Connecting to a Data Source (ODBC).

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

SQLSetConnectAttr Function
ODBC API Implementation Details
Bulk Copy Functions
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)
SQLPrepare Function
SQLGetInfo