MS Sql Server: Cannot create new connection because in manual or distributed

[vinjvinj]

My first query works but the second query gets this error. I'm using
the db_conn.execute function to execute direct sql. I also tried to
create a session object and explicitly create a transaction and commit
the transaction thinking that might solve the problem. However, that
did not fix the problem.

VJ

Exception:

adoRetVal=self.cmd.Execute()
File "<COMObject ADODB.Command>", line 3, in Execute
File "C:\Python24\Lib\site-packages\win32com\client\dynamic.py",
line 258, in
_ApplyTypes_
result = self._oleobj_.InvokeTypes(*(dispid, LCID, wFlags,
retType, argTypes
) + args)
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft OLE DB
Provider
for SQL Server', 'Cannot create new connection because in manual or
distributed
transaction mode.', None, 0, -2147467259), None)

[vinjvinj]

This link explains the problem:

http://support.microsoft.com/default.aspx?scid=kb;en-us;272358

Unfortunately, I'm not sure what the solution is. I've already tried
to create a transaction in a session.

VJ

[Rick Morrison]

This is an ADODBAPI /OLEDB  issue. Solution is to either use client-side cursor for the first query, or to fetch all the results from the first query before issuing another one on another connection.

Just so you know, there are others working on pyodbc for the MSSQL SA module, which I think (and hope) is not subject to this problem. Neither is pymssql.

Rick

[vinjvinj]

I figured the problem out:

I was doing the following:

rows_from_sql1 = db_conn.execute(select1)
rows_from_sql2 = db_conn.execute(select2)

You can't do the above since rows_from_sql1 is an iterator. You have
to iterate through all the rows before you can execute the second
query.

I can potentially see certain situations where you would have to be
able to execute two queries before you iterate through them all. Are
there any work around this?

Thanks,

VJ

On Mar 7, 4:05 pm, "Rick Morrison" <rickmorri...@gmail.com> wrote:
> This is an ADODBAPI /OLEDB issue. Solution is to either use client-side
> cursor for the first query, or to fetch all the results from the first query
> before issuing another one on another connection.
>
> Just so you know, there are others working on pyodbc for the MSSQL SA
> module, which I think (and hope) is not subject to this problem. Neither is
> pymssql.
>
> Rick
>