Its range is from 1 (default) to 60 seconds and it’s applied after the first reconnection attempt: ConnectRetryInterval adds a wait time between each ConnectRetr圜ount attempt. To pass connect retry interval, use ConnectRetryInterval parameter. The default value is one reconnection attempt. Zero (0) means don't attempt to reconnect. You need to pass ConnectRetr圜ount parameter and pass the values range from 0 to 255. In the connection string shared by you, you are passing ConnectionRetr圜ount parameter, which is wrong. You need to use Connection Retry Interval and Connection Retry Count features to keep your connection active. There is no KeepAlive option in ODBC Driver for SQL Server. I currently trying to add C/C++ code to handle this, but have so far not succeeded. Running a test where I ask for a cursor to all rows in a table ("select col1,col2 from tab1"), and each minute ask for the next row, I've held the db connection open 24 hours before I closed the client.Īre there any hints and/or tricks to keep an idle SQL Server ODBC session alive when using Azure? I'm 100% sure there is, since using PostgreSQL on AWS it works flawless. We are using prepared statements and cursors. They (i) always have the same values of 1 and 10. SQLGetConnectAttr(hdbc, SQL_COPT_SS_CONNECT_RETRY_INTERVAL, &i, SQL_IS_INTEGER, NULL) Result = SQLDriverConnect(hdbc,NULL,(UCHAR *)conStr,inLen,(UCHAR *)rConStr,MAX_DC_LEN, &rConLen, SQL_DRIVER_COMPLETE_REQUIRED) Īfter successful connect using SQLDriverConnect(), I'm checking the ConnectionRetr圜ount and ConnectionRetryInterval by: SQLGetConnectAttr(hdbc, SQL_COPT_SS_CONNECT_RETRY_COUNT, &i, SQL_IS_INTEGER, NULL) The connection to the db is standard: dbStatus=SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &EnvHandle) ĭbStatus=SQLSetEnvAttr(EnvHandle, SQL_ATTR_ODBC_VERSION, (SQLPOINTER)SQL_OV_ODBC3_80, 0) ĭbStatus=SQLAllocHandle(SQL_HANDLE_DBC, EnvHandle, &hdbc) To me it seems like they are only there for linux/mac? No matter what and how I try to append the "KeepAlive=50 KeepAliveInterval=10" to the connection string, it makes no difference. I have found articles from Microsoft about "KeepAlive" for their "ODBC Driver 17 for SQL Server", but I do not get it to work. We have seen this also on AWS using PostgreSQL, and solved the issue using the "KeepAlive" parameter in the PostgreSQL ODBC driver. When accessing the connection, ODBC throws an error saying the connection has been terminated at the server. The problem occurs after the program has been idle for some time (20 minutes approx). Everything seems normal after switching the driver connect to the Azure db using: DRIVER=ODBC driver 17 for SQL server SERVER=,1433 DATABASE= ENCRYPT=yes TRUSTSERVERCERTIFICATE=no Connection Timeout=60 ConnectionRetr圜ount=5 One of the users are currently in the process of moving his production database to Azure. It uses ODBC to access SQL Server (and PostgreSQL among others). I'm maintaining a legacy desktop environment for Windows written in C/C++.
0 Comments
Leave a Reply. |