Connection resiliency - ODBC Driver for SQL Server

link管理

链接快照平台

输入网页链接，自动生成快照
标签化管理网页链接

相关文章推荐

纯真的马铃薯 · Results - OpenURL ...· 昨天 ·

乖乖的数据线 · [email protected] | ...· 3 天前 ·

讲道义的热水瓶 · Connectors - PandasAI· 5 天前 ·

瘦瘦的烤红薯 · Quick Tip: SQLAlchemy ...· 5 天前 ·

冷静的豌豆 · Can't connect to ...· 5 天前 ·

飘逸的热带鱼 · python如何写一个sql解析器 | ...· 3 周前 ·

痛苦的双杠 · 最佳磁力吧搜索引擎 - 百度· 2 月前 ·

低调的数据线 · 关于新西兰奥克兰国际机场转机的问题_百度知道· 7 月前 ·

火星上的回锅肉 · Unity3D实时渲染是什么东西· 8 月前 ·

安静的斑马 · unity卡在gradle-掘金· 8 月前 ·

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

Download Microsoft Edge More info about Internet Explorer and Microsoft Edge Download ODBC driver

To ensure that applications remain connected to an Azure SQL Database, the ODBC driver can restore idle connections.

Important

The connection resiliency feature is supported on Microsoft Azure SQL Database and SQL Server 2014 (and later) server versions.

The feature is available on Windows starting with Microsoft ODBC Driver 11 for SQL Server. It is available on Linux starting in version 17.2 of Microsoft ODBC Driver 17 for SQL Server.

For more information about idle connection resiliency, see Technical Article - Idle Connection Resiliency .

To control reconnect behavior, the ODBC Driver for SQL Server has two options:

Connection retry count.

Connect retry count controls the number of reconnection attempts if there's a connection failure. Valid values range from 0 to 255. Zero (0) means don't attempt to reconnect. The default value is one reconnection attempt.

You can modify the number of connection retries when you:

Define or modify a data source that uses the ODBC Driver for SQL Server with the Connection Retry Count control.

Use the ConnectRetryCount connection string keyword.

To retrieve the number of connection retry attempts, use the SQL_COPT_SS_CONNECT_RETRY_COUNT (read only) connection attribute. If an application connects to a server that doesn't support connection resiliency, SQL_COPT_SS_CONNECT_RETRY_COUNT returns 0.

Connect retry interval.

The connect retry interval specifies the number of seconds between each connection retry attempt. Valid values are 1-60. The total time to reconnect can't exceed the connection timeout (SQL_ATTR_QUERY_TIMEOUT in SQLSetStmtAttr). The default value is 10 seconds.

You can modify the connection retry interval when you:

Define or modify a data source that uses the ODBC Driver for SQL Server with the Connect Retry Interval control.

Use the ConnectRetryInterval connection string keyword.

To retrieve the length of the connection retry interval, use the SQL_COPT_SS_CONNECT_RETRY_INTERVAL (read only) connection attribute.

If an application establishes a connection with SQL_DRIVER_COMPLETE_REQUIRED and later tries to execute a statement over a broken connection, the ODBC driver won't display the dialog box again. Also, during recovery in progress,

During recovery, any call to


    SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD)

, must return


    SQL_CD_FALSE

If recovery fails, any call to


    SQLGetConnectAttr(SQL_COPT_SS_CONNECTION_DEAD)

, must return


    SQL_CD_TRUE

The following state codes are returned by any function that executes a command on the server:

State Message


   IMC01


   The connection is broken and recovery is not possible. The client driver attempted to recover the connection one or more times and all attempts failed. Increase the value of ConnectRetryCount to increase the number of recovery attempts.


   IMC02


   The server did not acknowledge a recovery attempt, connection recovery is not possible.


   IMC03


   The server did not preserve the exact client TDS version requested during a recovery attempt, connection recovery is not possible.


   IMC04


   The server did not preserve the exact server major version requested during a recovery attempt, connection recovery is not possible.


   IMC05


   The connection is broken and recovery is not possible. The connection is marked by the server as unrecoverable. No attempt was made to restore the connection.


   IMC06


   The connection is broken and recovery is not possible. The connection is marked by the client driver as unrecoverable. No attempt was made to restore the connection.

Example

The following sample contains two functions. func1 shows how you can connect with a data source name (DSN) that uses the ODBC Driver for SQL Server on Windows. The DSN uses SQL Server Authentication, and it specifies the user ID. func1 then retrieves the number of connection retries with SQL_COPT_SS_CONNECT_RETRY_COUNT .

func2 uses SQLDriverConnect , ConnectRetryCount connection string keyword, and connection attributes to retrieve the setting for connection retries and retry interval.

// Connection_resiliency.cpp
// compile with: odbc32.lib
#include <windows.h>
#include <stdio.h>
#include <sqlext.h>
#include <msodbcsql.h>
void func1() {
    SQLHENV henv;
    SQLHDBC hdbc;
    SQLHSTMT hstmt;
    SQLRETURN retcode;
    SQLSMALLINT i = 21;
    // Allocate environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    // Set the ODBC version environment attribute
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (void*)SQL_OV_ODBC3, 0);
        // Allocate connection handle
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc);
            // Set login timeout to 5 seconds
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                SQLSetConnectAttr(hdbc, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
                // Connect to data source
                retcode = SQLConnect(hdbc, (SQLCHAR*)"MyDSN", SQL_NTS, (SQLCHAR*)"userID", SQL_NTS, (SQLCHAR*)"password_for_userID", SQL_NTS);
                retcode = SQLGetConnectAttr(hdbc, SQL_COPT_SS_CONNECT_RETRY_COUNT, &i, SQL_IS_INTEGER, NULL);
                // Allocate statement handle
                if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                    retcode = SQLAllocHandle(SQL_HANDLE_STMT, hdbc, &hstmt);
                    // Process data
                    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                        SQLFreeHandle(SQL_HANDLE_STMT, hstmt);
                    SQLDisconnect(hdbc);
                SQLFreeHandle(SQL_HANDLE_DBC, hdbc);
        SQLFreeHandle(SQL_HANDLE_ENV, henv);
void func2() {
    SQLHENV henv;
    SQLHDBC hdbc1;
    SQLHSTMT hstmt;
    SQLRETURN retcode;
    SQLSMALLINT i = 21;
#define MAXBUFLEN 255
    SQLCHAR ConnStrIn[MAXBUFLEN] = "DRIVER={ODBC Driver 18 for SQL Server};SERVER=server_that_supports_connection_resiliency;Encrypt=yes;UID=userID;PWD= password_for_userID;ConnectRetryCount=2";
    SQLCHAR ConnStrOut[MAXBUFLEN];
    SQLSMALLINT cbConnStrOut = 0;
    // Allocate environment handle
    retcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &henv);
    // Set the ODBC version environment attribute
    if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
        retcode = SQLSetEnvAttr(henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER*)SQL_OV_ODBC3_80, SQL_IS_INTEGER);
        // Allocate connection handle
        if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
            retcode = SQLAllocHandle(SQL_HANDLE_DBC, henv, &hdbc1);
            // Set login timeout to 5 seconds
            if (retcode == SQL_SUCCESS || retcode == SQL_SUCCESS_WITH_INFO) {
                // SQLSetConnectAttr(hdbc1, SQL_LOGIN_TIMEOUT, (SQLPOINTER)5, 0);
                retcode = SQLDriverConnect(hdbc1, NULL, ConnStrIn, SQL_NTS, NULL, 0, NULL, SQL_DRIVER_NOPROMPT);
            retcode = SQLGetConnectAttr(hdbc1, SQL_COPT_SS_CONNECT_RETRY_COUNT, &i, SQL_IS_INTEGER, NULL);
            retcode = SQLGetConnectAttr(hdbc1, SQL_COPT_SS_CONNECT_RETRY_INTERVAL, &i, SQL_IS_INTEGER, NULL);
int main() {
    func1();
    func2();
See also
Microsoft ODBC Driver for SQL Server