Web2Py won't connect to MSSQL

Question

I'm unable to get web2py to connect to mssql.

<type 'exceptions.RuntimeError'>(Failure to connect, tried 5 times:
'NoneType' object has no attribute 'connect')

My connection string is:

db = DAL('mssql://testUser:password1@localhost/testDB')

Environment
  Windows Server 2008 R2, 64-bit operating system
  SQL Server 2008 R2, local.
  Web2py: source code install version 1.99.2 (2011-09-26 06:55:33) stable.
  pyodbc
  Python 2.7.2

I've tested that I can connect using the pyodbc. The following code works:

import pyodbc
cnxn = pyodbc.connect('DRIVER={SQL Server};SERVER=localhost;DATABASE=testDB;UID=testUser;PWD=password1')
cursor = cnxn.cursor()
cursor.execute("select * from tbUsers")
rows = cursor.fetchall()
for row in rows:
  print row

Thanks for your time.

Corey.

If you don't get an answer here (Massimo cannot be everywhere), try to ask on the GoogleGroup: http://groups.google.com/group/web2py — JMax, Oct 25 '11 at 15:13

score 2 · Accepted Answer · answered Oct 26 '11 at 14:05

I've just received a solution from Massimo Di Pierro on the Web2Py forum. He deduced the cause and provided a work around.

Not sure if the "import pyodbc" is needed. Once the driver was assigned it stayed, even after a restart of the server.

# Test if the mssql driver is assigned. Sets it up if it isn't.
import pyodbc
from gluon.dal import MSSQLAdapter
if not (MSSQLAdapter.driver):
  MSSQLAdapter.driver = globals().get('pyodbc',None)

db = DAL('mssql://testUser:password@localhost/testDB')

MSSQLAdapter.driver = globals().get('pyodbc',None) should just be MSSQLAdapter.driver = pyodbc. Not sure why this is not set automatically for you. Must be a sys.path issue. — Massimo, Oct 27 '11 at 19:08

score 0 · Answer 2 · answered Apr 11 '13 at 15:19

After confirming that your login is correct and that you have pyodbc installed, be sure that the connection string for the server is as follows if your db server name has a backslash in it (e.g. localhost\dbServerName):

    db = DAL('mssql://testUser:password@localhost\dbServerName/testDB')

You can substitute the localhost with an IP Address as well.

Environment:
Windows 7 Professional, 32-bit operating system
SQL Server 2008 R2 connecting over a network
Web2py: source code install version 2.4.6 stable
pyodbc: pyodbc-3.0.5.win32-py2.7.exe
Python 2.7.3

Web2Py won't connect to MSSQL

2 Answers2

Linked