Web2py won't connect to MSSQL

[CJM]

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.

[Bruno Rocha]

are you using source code or .exe version of web2py?

http://zerp.ly/rochacbruno

[CJM]

It's a source code install. Hench the line:

> >  Web2py: source code install version 1.99.2 (2011-09-26 06:55:33)

:)

On Oct 25, 12:10 pm, Bruno Rocha <rochacbr...@gmail.com> wrote:
> are you using source code or .exe version of web2py?
>
> http://zerp.ly/rochacbruno

[Massimo Di Pierro]

try:

from gluon.dal import MSSQLAdapter
print MSSQLAdapter.driver

My guess isthat it is failing to import pyodbc from dal.py somehow and
the driver should should be set to

driver = globals().get('pyodbc',None)

is set to None instead.

[Omi Chiba]

CJM,

Are you using apache using wsgi? if so, you're with me. You cannot
import pyodbc.
http://ochiba77.blogspot.com/2011/10/how-to-setup-web2py-apache-wsgi.html

If you're using rocket server, it should connect because I'm
connecting without any problem. Only difference with your environment
is that I'm using Windows 7. I use actual server(computer) name
instead of localhost. You can try that to see if it makes any
difference.

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

if it's mssql server express,

db = DAL('mssql:// testUser:password1@ServerName\SQLEXPRESS/testDB')

[CJM]

Thank you Massimo!

The driver did return None and setting the driver manually did the
trick.

Is this an issue with web2py or did I somehow setup my machine
incorrectly?



On Oct 25, 6:15 pm, Massimo Di Pierro <massimo.dipie...@gmail.com>
wrote:

[Omi Chiba]

CJM,

Can I ask how you set the driver manually ?
I want to try on my machine too.

[Massimo Di Pierro]

check web2py/gluon/dal.py

there is a try: import pyodbc. remove the try and check whay it fails.
Must be a patch issue.

On Oct 25, 9:39 pm, Omi Chiba <ochib...@gmail.com> wrote:
> CJM,
>
> Are you using apache using wsgi? if so, you're with me. You cannot

> import pyodbc.http://ochiba77.blogspot.com/2011/10/how-to-setup-web2py-apache-wsgi....

[chris_g]

I recently had a similar problem where I could import pyodbc at the
python shell, but I was getting a DLL error when I tried to import
pyodbc under mod_wsgi.
I suggest that you put "import pyodbc" inside your wsgihandler.py (or
at the top of db.py) and see what errors are being produced.
I had recently had this problem and resolved it by following the steps
described here:
http://code.google.com/p/pyodbc/issues/detail?id=214

Chris Guest

[Omi Chiba]

Massimo, Chris

Thank you both of your reply!

I added import pyodbc in db.py and here's what I get. This is expected just like Chris described.

"ImportError: DLL load failed: A dynamic link library (DLL) initialization routine failed."

Then I tried mt.exe things. (See attached file) 

Strangely, I got error with python27.dll but went through with python.exe. At least, I confirmed the modified date has been changed on the .pyd files. But I still have the same problem...maybe I'm unlucky one :(

Chris,

Can you send me the fixed .pyd files so I can try in my environment ?