sql server - Azure Web App, Python, and MS SQL. pyodbc error [08S01] Error code 0x68 (104) (SQLExecDirectW) - Stack Overflow

admin管理员组

文章数量:1122847

I have a basic website, that execute multiple selects from a MSSQL. No one uses the website (just a website for me to test my knowledge). My challenge is that after a while, I have this error:

pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')

I already did a internet search. Came across a few suggestions, including this one: Handle TCP Provider: Error code 0x68 (104) On this article, it's an intermittent error. Mine looks like a closed connection after a period of time. If I restart the Azure Web App and keep doing refresh during the day, I don't face the issue. It's only after a few hours of inactivity.

I tried a reconnect without success. This is my code

odbc_str = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};Encrypt=yes;TrustServerCertificate=no;autocommit=True'
connection = pyodbc.connect(odbc_str, readonly=True)
cursor_conn = connection.cursor()

def get_results(seasonID):
    global connection, cursor_conn, odbc_str # Usage of global variables is not best practice
    if not connection:  # Because the website does not have lots of traffic, I suspect the connection is drop
        connection = pyodbc.connect(odbc_str, readonly=True)
        cursor_conn = connection.cursor()
    resultsQuery = "SELECT * FROM season_view"
    return cursor_conn.execute(resultsQuery).fetchall()

Any suggestion?

I have a basic website, that execute multiple selects from a MSSQL. No one uses the website (just a website for me to test my knowledge). My challenge is that after a while, I have this error:

pyodbc.OperationalError: ('08S01', '[08S01] [Microsoft][ODBC Driver 18 for SQL Server]TCP Provider: Error code 0x68 (104) (SQLExecDirectW)')

I already did a internet search. Came across a few suggestions, including this one: Handle TCP Provider: Error code 0x68 (104) On this article, it's an intermittent error. Mine looks like a closed connection after a period of time. If I restart the Azure Web App and keep doing refresh during the day, I don't face the issue. It's only after a few hours of inactivity.

I tried a reconnect without success. This is my code

odbc_str = f'DRIVER={driver};SERVER={server};DATABASE={database};UID={username};PWD={password};Encrypt=yes;TrustServerCertificate=no;autocommit=True'
connection = pyodbc.connect(odbc_str, readonly=True)
cursor_conn = connection.cursor()

def get_results(seasonID):
    global connection, cursor_conn, odbc_str # Usage of global variables is not best practice
    if not connection:  # Because the website does not have lots of traffic, I suspect the connection is drop
        connection = pyodbc.connect(odbc_str, readonly=True)
        cursor_conn = connection.cursor()
    resultsQuery = "SELECT * FROM season_view"
    return cursor_conn.execute(resultsQuery).fetchall()

Any suggestion?

• python
• sql-server
• azure-web-app-service
• pyodbc

Share Improve this question Follow edited Dec 4, 2024 at 10:23 AlbertoN asked Dec 4, 2024 at 9:47 AlbertoNAlbertoN 2755 bronze badges 8

• This question is similar to: Handle TCP Provider: Error code 0x68 (104). If you believe it’s different, please edit the question, make it clear how it’s different and/or how the answers on that question are not helpful for your problem. – GuidoG Commented Dec 4, 2024 at 9:52
• You shouldn't cache a connection forever, use pools or at least check if connecton is still alive and recreate it. Also, what happens if multiple users call get_results – siggemannen Commented Dec 4, 2024 at 10:03
• Multiple users, means two devices accessing the site simultaneously don't create an issue. I was thinking that "if not connection" was an attempt to see if it was still alive, Bad coding? Looks like I need to try to figure out how to use pools – AlbertoN Commented Dec 4, 2024 at 10:06
• What do you mean with "don't create an issue" – siggemannen Commented Dec 4, 2024 at 10:08
• Sorry. Means more than user accessing the website, don't create errors – AlbertoN Commented Dec 4, 2024 at 10:12

 |  Show 3 more comments

1 Answer 1

Sorted by: Reset to default 1

Changed my code to open close a connection every time a query is made. Double checked and the errors were gone.

def get_results(seasonID):
    global odbc_str
    connection = pyodbc.connect(odbc_str, readonly=True)
    cursor_conn = connection.cursor()
    resultsQuery = "SELECT * FROM season_view"
    execute_qry = cursor_conn.execute(resultsQuery).fetchall()
    cursor_conn.close()
    connection.close()
    return execute_qry

本文标签：