Sqlalchemy issue on Windows Server 2008 R2 sp1

[Massi]

Hi everyone, as the title says I'm trying to run sqlalchemy (pyodbc) with SQL Server 2008 on Windows Server 2008 R2 sp1 but I'm failing with the database connection. Here is a small script I'm using to test the connection:

from sqlalchemy import *
from sqlalchemy.engine import reflection

url = "mssql+pyodbc://user:pass...@my.server.address/server_test"
e = create_engine(url)
insp = reflection.Inspector.from_engine(e)
print insp.get_table_names()

If I the script I get the following error:

Traceback (most recent call last):
  File "connection.py", line 6, in <module>

  File "C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 118
, in from_engine
    return Inspector(bind)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\reflection.py", line 94,
 in __init__
    bind.connect().close()
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2316, in
connect
    return self._connection_cls(self, **kwargs)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 872, in _
_init__
    self.__connection = connection or engine.raw_connection()
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\base.py", line 2402, in
raw_connection
    return self.pool.unique_connection()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 169, in unique_c
onnection
    return _ConnectionFairy(self).checkout()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 371, in __init__

    rec = self._connection_record = pool._do_get()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 697, in _do_get
    con = self._create_connection()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 174, in _create_
connection
    return _ConnectionRecord(self)
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 256, in __init__

    self.connection = self.__connect()
  File "C:\Python27\lib\site-packages\sqlalchemy\pool.py", line 316, in __connec
t
    connection = self.__pool._creator()
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\strategies.py", line 80,
 in connect
    return dialect.connect(*cargs, **cparams)
  File "C:\Python27\lib\site-packages\sqlalchemy\engine\default.py", line 280, i
n connect
    return self.dbapi.connect(*cargs, **cparams)
sqlalchemy.exc.DBAPIError: (Error) ('08001', '[08001] [Microsoft][ODBC SQL Serve
r Driver][DBNETLIB]Invalid connection. (14) (SQLDriverConnect); [01000] [Microso
ft][ODBC SQL Server Driver][DBNETLIB]ConnectionOpen (Invalid Instance()). (14)')
 None None

The connection tested with SQL Server Management Studio works fine, so it should not be an authentication problem.
Has anyone an idea of what I'm doing wrong?
Thanks in advance!

[Michael Bayer]

I'd first recommend creating a real ODBC datasource, since that's how ODBC is meant to be used.  Then you can try connecting to it using ODBC test tools (such as on my Mac here, I have a program called "iodbctest" which tests an ODBC datasource) and then perhaps creating a plain pyodbc connection to it.

--
You received this message because you are subscribed to the Google Groups "sqlalchemy" group.
To view this discussion on the web visit https://groups.google.com/d/msg/sqlalchemy/-/g0c-MFla6pAJ.
To post to this group, send email to sqlal...@googlegroups.com.
To unsubscribe from this group, send email to sqlalchemy+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en.

[David Moore]

Hi everyone, as the title says I'm trying to run sqlalchemy (pyodbc) with SQL Server 2008 on Windows Server 2008 R2 sp1 but I'm failing with the database connection. Here is a small script I'm using to test the connection:

from sqlalchemy import *
from sqlalchemy.engine import reflection

url = "mssql+pyodbc://user:pass...@my.server.address/server_test"
e = create_engine(url)
insp = reflection.Inspector.from_engine(e)
print insp.get_table_names()

You're probably using a named instance, in which case you need to use the instance name as part of the server name.  You can find it in the connection details Management Studio connects to, or in the service name.  For instance, the default named instance for SQL Server Express is SQLEXPRESS, so your url above should be:

"mssql+pyodbc://user:pass...@my.server.address\\SQLEXPRESS/server_test"

HTH,
Dave
[snip]

--

David Moore
Senior Software Engineer
St. James Software
Email: dav...@sjsoft.com