Neither DSN nor SERVER keyword supplied
Solution 1
The Windows ODBC Driver Manager is quite fussy about keywords in connection strings. They must be immediately followed by the equal sign, so SERVER=...
will work, but SERVER =...
will not.
Solution 2
Its crazy but I managed to solve it by actually passing an option: extra_params: server=WHEREYOURSERVERLIVES\DBSERVER
I am using it pyodbc on django BTW.
Must be some bug.
something like this
'ENGINE': 'sql_server.pyodbc',
'NAME': 'YOURGREATESTDATABASE',
'USER': 'YOURGREATESTUSERNAME',
'PASSWORD': 'YOURGREATESTPASSWORD',
'HOST': 'WHEREYOURSERVERLIVES\DBSERVER',
'PORT': '',
'OPTIONS': {
'driver': 'ODBC Driver 17 for SQL Server',
'extra_params': "Persist Security Info=False;server=WHEREYOURSERVERLIVES\\DBSERVER"
Solution 3
Try this format:
This should work
connection = pyodbc.connect("DRIVER={/opt/microsoft/msodbcsql17/lib64/libmsodbcsql-17.4.so.2.1};SERVER=%s;DATABASE=%s;UID=%s;PWD=%s"
% (server, database, username, password))
Serdia
Updated on July 03, 2022Comments
-
Serdia almost 2 years
I am trying to connect to SQL database slightly different ways: with and without use of parameter. Why without use of parameters works fine, but with use of parameters - gives me an error. Did I make a syntax error? I went through each letters and couldn't see anything.
import pandas as pd import pyodbc #parameters: server = 'SQLDEV' db = 'MEJAMES' #Create the connection conn = pyodbc.connect('DRIVER={SQL Server};server =' + server + ';DATABASE = ' + db + ';Trusted_Connection=yes;') # query db sql = """ select top 10 PolicyNumber, QuoteID, ProducerName from tblQuotes """ df = pd.read_sql(sql,conn) df
The statement above gives me an error
But if I do the same but without use of parameters then it works fine:
import pandas as pd import pyodbc #parameters: #server = 'SQLDEV' #db = 'MEJAMES' #Create the connection conn = pyodbc.connect("DRIVER={SQL Server};server=SQLDEV;database=MEJAMES;Trusted_Connection=yes;") # query db sql = """ select top 10 PolicyNumber, QuoteID, ProducerName from tblQuotes """ df = pd.read_sql(sql,conn) df