6

I'm very new to Python and I have Python 3.2 installed on a Win 7-32 workstation. Trying to connect to MSSQLServer 2005 Server using adodbapi-2.4.2.2, the latest update to that package.

The code/connection string looks like this:

conn = adodbapi.connect('Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=XXX;Data Source=123.456.789');

From adodbapi I continually get the error (this is entire error message from Wing IDE shell):

Traceback (most recent call last): File "D:\Program Files\Wing IDE 4.0\src\debug\tserver_sandbox.py", line 2, in if name == 'main': File "D:\Python32\Lib\site-packages\adodbapi\adodbapi.py", line 298, in connect raise InterfaceError #Probably COM Error adodbapi.adodbapi.InterfaceError:

I can trace through the code and see the exception as it happens.

I also tried using conn strings with OLEDB provider and integrated Windows security, with same results.

All of these connection strings work fine from a UDL file on my workstation, and from SSMS, but fail with the same error in adodbapi.

How do I fix this?

Vector
  • 10,879
  • 12
  • 61
  • 101
  • I don't know too much about adodbapi, but since it's not working for you maybe give pyodbc a shot. Good luck. – mechanical_meat May 22 '11 at 06:59
  • @Adam - thanks - will try it when they release for Python 3 - on web page they say it's pending - see http://code.google.com/p/pyodbc/. I just started with Python about a month ago and python.org recommends that newbies go right to 3 so that's what I have. I would prefer to go ADO direct and not have to bother with ODBC connnections but if that's all I can get to work, I'll go with it. Meanwhile let's see if someone else comes up with a solution. – Vector May 22 '11 at 08:10

3 Answers3

4

Try this connection string:

Initial Catalog=XXX; Data Source=<servername>\\<SQL Instance name>; Provider=SQLOLEDB.1; Integrated Security=SSPI

Update

Umm ok. Looking at the source for adodbapi I would have to say that you are suffering a COM error. (yeah I know the traceback says that). But specifically with initialising the relevant COM objects.

This means that your connection string has nothing to do with the traceback. I think a good place to start would be to make sure that your copy of pythoncom is up-to-date.

It could be that win32com/pythoncom does not support Python 3K (3.0 onwards) yet, but after a minute of googleing I have not found anything useful on that, I'll leave it to you.

This code should run successfully when you have fixed your problem (and should fail at the moment).

import win32com.client
import pythoncom
pythoncom.CoInitialize()
win32com.client.Dispatch('ADODB.Connection')

Also any exception that code throws would be useful to help debug your problem.

thomas
  • 949
  • 6
  • 20
  • Thanks, but your conn string using OLEDB didn't seem to make any difference. – Vector Jun 17 '11 at 04:01
  • @Mikey Take a quick look at my updated answer and post back the traceback you get. – thomas Jun 17 '11 at 14:37
  • will check it out over the weekend.Meanwhile I downgraded Python 2.72 since most of the libraries I want to use aren't P3 ready yet - but still got same error, both with native provider and OLEDB. Right now, the only exception/error message I ever see it what I posted in OP. I'm going to accept your answer because you've taken the time - would appreciate any further help - Tnx. – Vector Jun 17 '11 at 15:27
  • @Mikey Ummm ok. When you get the chance a traceback for the test code would be great, but if your having problems when using Python 2.7 then this could be tricky. – thomas Jun 17 '11 at 20:26
  • np - I have another machine that I can run with P3 – Vector Jun 17 '11 at 21:36
3

In case anyone else finds this thread looking for the resolution to a similar error that I saw with Python 2.7:

Traceback (most recent call last):
  File "get_data.py", line 10, in <module>
    connection = get_connection(r"XXX\YYY", "DB")
  File "get_data.py", line 7, in get_connection
    conn = adodbapi.connect(connstring)
  File "C:\Python27\lib\site-packages\adodbapi\adodbapi.py", line 116, in connect
    raise api.OperationalError(e, message)
adodbapi.apibase.OperationalError: (InterfaceError("Windows COM Error: Dispatch('ADODB.Connection') failed.",), 'Error opening connection to "Data Source=XXX\\YYY; Initial Catalog=DB; Provider=SQLOLEDB.1; Integrated Security=SSPI"')

In my case the simple solution was to install Python for Windows Extensions (pywin32) from here: http://sourceforge.net/projects/pywin32/files/pywin32/Build%20219/

Captain Whippet
  • 2,143
  • 4
  • 25
  • 34
2

I had the same problem, and I tracked it down to a failure to load win32com.pyd, because of some system DLLs that was not in the "dll load path", such as msvcp100.dll

I solved the problem by copying a lot of these dll's (probably too many) into C:\WinPython-64bit-3.3.3.2\python-3.3.3.amd64\Lib\site-packages\win32

Bjarke Ebert
  • 1,920
  • 18
  • 26