SQLSetConnectAttr

 

System_CAPS_ICON_warning.jpg Warning

SQL Server Native Client (SNAC) is not supported beyond SQL Server 2012. Avoid using SNAC in new development work, and plan to modify applications that currently use it. The Microsoft ODBC Driver for SQL Server provides native connectivity from Windows to Microsoft SQL Server and Microsoft Azure SQL Database.

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

System_CAPS_ICON_note.jpg 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 attributeSet before or after connection to server
SQL_COPT_SS_ANSI_NPWBefore
SQL_COPT_SS_APPLICATION_INTENTBefore
SQL_COPT_SS_ATTACHDBFILENAMEBefore
SQL_COPT_SS_BCPBefore
SQL_COPT_SS_BROWSE_CONNECTBefore
SQL_COPT_SS_BROWSE_SERVERBefore
SQL_COPT_SS_CONCAT_NULLBefore
SQL_COPT_SS_CONNECTION_DEADAfter
SQL_COPT_SS_ENCRYPTBefore
SQL_COPT_SS_ENLIST_IN_DTCAfter
SQL_COPT_SS_ENLIST_IN_XAAfter
SQL_COPT_SS_FALLBACK_CONNECTBefore
SQL_COPT_SS_FAILOVER_PARTNERBefore
SQL_COPT_SS_INTEGRATED_SECURITYBefore
SQL_COPT_SS_MARS_ENABLEDBefore
SQL_COPT_SS_MULTISUBMIT_FAILOVERBefore
SQL_COPT_SS_OLDPWDBefore
SQL_COPT_SS_PERF_DATAAfter
SQL_COPT_SS_PERF_DATA_LOGAfter
SQL_COPT_SS_PERF_DATA_LOG_NOWAfter
SQL_COPT_SS_PERF_QUERYAfter
SQL_COPT_SS_PERF_QUERY_INTERVALAfter
SQL_COPT_SS_PERF_QUERY_LOGAfter
SQL_COPT_SS_PRESERVE_CURSORSBefore
SQL_COPT_SS_QUOTED_IDENTEither
SQL_COPT_SS_TRANSLATEEither
SQL_COPT_SS_TRUST_SERVER_CERTIFICATEBefore
SQL_COPT_SS_TXN_ISOLATIONEither
SQL_COPT_SS_USE_PROC_FOR_PREPEither
SQL_COPT_SS_USER_DATAEither
SQL_COPT_SS_WARN_ON_CP_ERRORBefore

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

ValueDescription
SQL_AD_ONDefault. The connection uses ANSI default behavior for handling NULL comparisons, padding, warnings, and NULL concatenations.
SQL_AD_OFFThe 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.

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

ValueDescription
SQLPOINTER to a character stringThe 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.

ValueDescription
SQL_BCP_OFFDefault. Bulk copy functions are not available on the connection.
SQL_BCP_ONBulk copy functions are available on the connection.

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.

ValueDescription
SQL_MORE_INFO_NODefault. Returns a list of servers.
SQL_MORE_INFO_YESSQLBrowseConnect returns an extended string of server properties.

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.

ValueDescription
computernameSQLBrowseConnect 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).
NULLDefault. SQLBrowseConnect returns information for all servers in the domain.

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.

ValueDescription
SQL_CN_ONDefault. The connection uses ISO default behavior for handling NULL values when concatenating strings.
SQL_CN_OFFThe connection uses SQL Server-defined behavior for handling NULL values when concatenating strings.

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.

ValueDescription
SQL_EN_ONThe connection will be encrypted.
SQL_EN_OFFThe connection will not be encrypted. This is the default.

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.

ValueDescription
DTC object*The MS DTC OLE transaction object that specifies the transaction to export to SQL Server.
SQL_DTC_DONEDelimits the end of a DTC transaction.

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.

This attribute is no longer supported.

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

ValueDescription
SQL_IS_OFFDefault. SQL Server Authentication is used to validate user identifier and password on login.
SQL_IS_ONWindows Authentication Mode is used to validate a user's access rights to the SQL Server.

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.

ValueDescription
SQL_MARS_ENABLED_NODefault. Multiple Active Result Sets (MARS) is disabled.
SQL_MARS_ENABLED_YESMARS is enabled.

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

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

ValueDescription
SQL_IS_ONSQL Server Native Client provides faster reconnection if there is a failover.
SQL_IS_OFFSQL Server Native Client will not provide faster reconnection if there is a failover.

Password expiration for SQL Server Authentication was 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.

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

ValueDescription
SQL_PERF_STARTStarts the driver sampling performance data.
SQL_PERF_STOPStops the counters from sampling performance data.

For more information, see SQLGetConnectAttr.

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

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.

ValueDescription
SQL_PERF_STARTStarts long running query logging.
SQL_PERF_STOPStops logging of long running queries.

For more information, see SQLGetConnectAttr.

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

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

ValueDescription
SQL_PC_OFFDefault. Cursors are closed when transaction is committed or rolled back using SQLEndTran.
SQL_PC_ONCursors 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 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.

ValueDescription
SQL_QI_OFFThe SQL Server connection does not allow quoted identifiers in submitted Transact-SQL.
SQL_QI_ONDefault. 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 Serverchar, varchar, and text columns.

ValueDescription
SQL_XL_OFFThe driver does not translate characters from one code page to another in character data exchanged between the client and the server.
SQL_XL_ONDefault. 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 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.

ValueDescription
SQL_TRUST_SERVER_CERTIFICATE_NODefault. Encryption without certificate validation is not enabled.
SQL_TRUST_SERVER_CERTIFICATE_YESEncryption without certificate validation is enabled.

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.

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

This attribute is no longer supported.

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

For more information, see SQLGetConnectAttr.

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.

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

This is a read-only attribute.

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

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

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

Community Additions

ADD
Show: