Azure SQL: An existing connection was forcibly closed by the remote host

Question

Hi,

I'm connecting to an Azure SQL DB remotely from a Windows 10 PC. I'm using Python 2.7 with the following connection string:

'DRIVER={ODBC Driver 17 for SQL Server};SERVER=server_name;PORT=1433; DATABASE=db_name;UID=user_id;PWD=user_password'

and with:

engine = sqlalchemy.create_engine(new_con, fast_executemany=True)

and I get this error if I try to write the whole 225 MB file:

OperationalError: (pyodbc.OperationalError) ('08S01', u'[08S01] [Microsoft][ODBC Driver 17 for SQL Server]TCP Provider: An existing connection was forcibly closed by the remote host.\r\n (10054) (SQLExecute); [08S01] [Microsoft][ODBC Driver 17 for SQL Server]Communication link failure (10054)')
[SQL: INSERT INTO ....

I can connect and write to the DB a portion of a file but when I try to write to whole file, I get this error after about 5 minutes every time. So it's not happening randomly.

Any suggestions?

Thanks.

Answer 1

@AnuragSharma-MSFT, thank you for the suggestion. I actually managed to get this going. I had to specify the chunksize in my call:

df.to_sql(table, conn, index=False, if_exists='append',
method=None, chunksize = 50000)

Thanks!

Answer 2

Hi @Chinook_pdx , welcome to Microsoft Q&A forum.

This could be because of multiple reasons related to connectivity but you mentioned it worked for small portion of data. So it could be related to usage limit of Azure SQL database too.

Just for testing purpose, can you please increase the compute size of the database and run the code again?

If it still does not work, we can look into below thread where retry logic is being used for similar error:
https://stackoverflow.com/questions/41473137/pyodbc-how-to-retry-to-recover-from-transient-errors

Please let me know once you try these changes.

Answer 3

Hi @AnuragSharma-MSFT,

Thanks for your response. I posted the Compute Utilization and our current Hardware Configuration below. Please let me know what shall I set the Compute Size of the database to?

Thanks.