Why this error when getting data from MSSQL using pyodbc?

Question

I am using pyodbc to retrieve data from MSSQL and this is the code I am using:

import pyodbc
server = 'xxxxxxxx\DEV'
database = 'SandBox'
username = 'zzzzzzz'
password = 'xxxxxxx'
driver = '{SQL Server}'

cnxn = pyodbc.connect('DRIVER='+driver+';PORT=4853;SERVER='+server+';PORT=4853;DATABASE='+database+';UID='+username+';PWD='+ password)
cursor = cnxn.cursor()
cursor.execute("select * from fieldscreenscheme ")
row = cursor.fetchone()
if row:
    print row

This is the error massage I got:

cnxn = pyodbc.connect('DRIVER='+driver+';PORT=43853;SERVER='+server+';PORT=43853;DATABASE='+database+';UID='+username+';PWD='+ password)

pyodbc.Error: ('08001', '[08001] [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. (17) (SQLDriverConnect); [01000] [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Connect()). (53); [01S00] [Microsoft][ODBC SQL Server Driver]Invalid connection string attribute (0)')

I installed the ODBC driver. Any recommendation how to solve this error? I looked at these two but did not help me to solve this. Python - Can't connect to MS SQL pyodbc + MySQL + Windows: Data source name not found and no default driver specified
Microsoft Documentation: https://github.com/Microsoft/azure-docs/blob/master/articles/sql-database/sql-database-develop-python-simple.md

Still it does not any other recommendations?

EhsanHK
– EhsanHK

2017-03-30 15:28:04 +00:00
Commented Mar 30, 2017 at 15:28 — EhsanHK
– EhsanHK, Commented Mar 30, 2017 at 15:28

Gord Thompson · Accepted Answer · 2017-03-23 22:48:21Z

5

Two problems:

Normally, one supplies either the \INSTANCENAME or the port number, not both.
ODBC connection strings for SQL Server do not use PORT=, they put the port number in the SERVER= parameter, e.g., SERVER=xxxxxxxx,43853. (Note that the instance name is omitted and the separator is a comma, not a colon.)

answered Mar 23, 2017 at 22:48

Gord Thompson

125k38 gold badges251 silver badges458 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sakulachi8 · Accepted Answer · 2020-12-10 18:25:52Z

I was facing the same issue whole day wasted and I tried all possible ODBC Driver for SQL Server here is the list, I just try one by one and it works for me

Driver={ODBC Driver 11 for SQL Server} for SQL Server 2005 - 2014
Driver={ODBC Driver 13 for SQL Server} for SQL Server 2005 - 2016
Driver={ODBC Driver 13.1 for SQL Server} for SQL Server 2008 - 2016
Driver={ODBC Driver 17 for SQL Server} for SQL Server 2008 - 2017

And these are some others we can use, somehow in my case the last one works :)

Driver={SQL Server} for SQL Server 2000
Driver={SQL Native Client} for SQL Server 2005
Driver={SQL Server Native Client 10.0} for SQL Server 2008
Driver={SQL Server Native Client 11.0} for SQL Server 2012

Stephen Rauch · Accepted Answer · 2018-03-23 02:07:51Z

0

I've met same problem and fixed it changing connection string like below. Writing

driver = '{ODBC Driver 13 for SQL Server}'

instead of

driver = '{SQL Server}'

edited Mar 23, 2018 at 2:07

Stephen Rauch♦

50.1k32 gold badges118 silver badges143 bronze badges

answered Mar 23, 2018 at 1:46

Mil

3312 silver badges3 bronze badges

3 Comments

Eswar Over a year ago

which version of pyodbc ere you using? I used 4.0.26 and I had to do exactly opposite of your answer.

Mil Over a year ago

Version of pyodbc at execution environment could be 4.0.23, but I'm not sure.

sakulachi8 Over a year ago

@Eswar can you see my answer below,

Collectives™ on Stack Overflow

Why this error when getting data from MSSQL using pyodbc?

3 Answers 3

Comments

Comments

3 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

3 Comments

Linked

Related