Error while connecting to the Sql Server with pyodbc

Question

I'm trying to fetch data from SQL database with pyodbc with the code given below.The connection works rarely, most of the time it gives the error,

OperationalError: ('HYT00', '[HYT00] [Microsoft][ODBC SQL Server Driver]Login timeout expired (0) (SQLDriverConnect)')

import numpy as np
import pyodbc as odbc

conn_string = ('DRIVER={SQL Server};SERVER=test;DATABASE=DEV;UID=me;PWD=whatever;')
cnxn = odbc.connect(conn_string)
cursor = cnxn.cursor()
cursor.execute("Select * from PurchaseOrders")
rows = cursor.fetchall()
ID = [i[1] for i in rows]
ID_array = np.fromiter(ID, dtype= np.int32)

I have tried setting the timeout to zero and DRIVER={ODBC Driver 11 for SQL Server} as I'm using SQL Server 2014. None of these works.

I'd suspect that the connection between you and your server is bad. How are you connected to the server? Can you do anything to improve the connection? Like switching from WLAN to LAN. — Phonolog
The connection works perfectly with other .NET applications. I'm already in my office LAN. — Animate_Ant
Two notes: (1) SQL Server does not use UTF-8 encoding, so cnxn.setencoding(encoding='utf-8') is likely to cause problems. (2) If your connection string includes UID and PWD then you don't want Trusted_Connection=yes — Gord Thompson

Animate_Ant Animate_Ant · Accepted Answer · 2018-02-22T12:21:03

There was a problem with DNS. I used the IP address of the server instead, works fine now.

Error while connecting to the Sql Server with pyodbc

1 Answers