Connect to SQLServer 2005 with adodbapi

Question

I am very new to Python, and I have Python 3.2 installed on a Win 7-32 workstation. An attempt was made to connect to the MSSQLServer 2005 Server using adodbapi-2.4.2.2, the latest update for this package.

The line of code / connection is as follows:

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

From adodbapi I constantly get an error (this is a complete error message from the Wing IDE shell):

Traceback (last last call): 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 a connection raise InterfaceError #Probably COM Error adodbapi.adodbapi.InterfaceError:

I can track the code and see the exception as it happens.

I also tried using connectors with an OLEDB provider and an integrated Windows security system with the same results.

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

How to fix it?

Answer 1

Try this connection string:

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

Update

Umm, good. Looking at the source for adodbapi, I have to say that you are suffering from a COM error. (Yes, I know that the trace says this). But specifically with the initialization of the corresponding COM objects.

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

Win32com / pythoncom may not yet support Python 3K (3.0), but after a minute of googleing I did not find anything useful in this, I will leave it to you.

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

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

Also, any exception that raises code would be helpful to help debug your problem.

Answer 2

I had the same problem and tracked it to the failure of loading win32com.pyd due to some system dlls that were not in the "dll load path", eg msvcp100.dll

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

Answer 3

In case anyone else finds this thread looking for a resolution similar to the error 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, a simple solution was to install Python for Windows Extensions (pywin32) here: http://sourceforge.net/projects/pywin32/files/pywin32/Build%20219/