ipython-sql mssql+pyodbc Error: Connection is busy with results for another command

Hello,

this project seems really useful, but sadly, I can't get it to work with MSSql over odbc. SqlAlchemy itself works fine with this setup, but I cannot execute a single query with ipython-sql. Maybe ipython-sql does a query without fetching the results? ODBC+MSql does not like that.

%%sql mssql+pyodbc://@myDSN SELECT TOP 100 * FROM SomeDB.dbo.tbl_sometable;

(pyodbc.Error) ('HY000', '[HY000] [Microsoft][SQL Server Native Client 11.0]Connection is busy with results for another command (0) (SQLExecDirectW)') [SQL: 'commit']

ablepharus

Apr 19 '16 09:04 ablepharus

Any joy working out a solution to this? I'm getting the same issue

Jun 03 '16 15:06 aenikata

No, i didn't get it to work. I used pandas instead. I load the Sql Data into a DataFrame which is displayed nicely.

Jun 05 '16 10:06 ablepharus

I am getting the same. Was hoping to find an answer. Wondering if I need to make a configuration change in SQL Server.

Jul 14 '16 17:07 DieselAnalytics

I got this error for a mistake on my part that was not related to the error text. I was attempting to query a table with a cursor that was assigned to another database. In other words, the table was not in the database I was querying. This mistake resulted in the above error. Hope this helps someone else!

Jun 27 '17 14:06 josephephillipsjr

Also getting the same error with MSSQL Server.

Nov 02 '17 01:11 QuinRiva

I am facing the same issue. Is there any solution to this error?

Nov 17 '17 16:11 dalalkrish

In the connection string, try setting MARS_Connection=Yes

Nov 22 '17 01:11 jgeo23

MARS_Connection is best option. Thanks for your help.

Sep 21 '18 09:09 sandy0110

Hello anyone in this thread @sandy0110 @jgeo23 can you please show exactly how you specify the MARS_Connection=Yes in the python connection for the magic function, nothing I am doing seems to work.

Oct 22 '18 13:10 cmosguy

@cmosguy try this:

import pyodbc import pandas as pd

server = 'your_servert' database = 'your_database' username = 'your_username' password = 'your_password' driver= '{ODBC Driver 13 for SQL Server}' con = 'Yes'

cnxn = pyodbc.connect('DRIVER='+driver+';SERVER='+server+';PORT=1433;DATABASE='+database+';UID='+username+';PWD='+ password+'; MARS_Connection=' + con)

Oct 26 '18 17:10 mkl04

"MARS_Connection=Yes" solves this issue.

Oct 29 '18 15:10 waltertschwe

Hi all,

I went ahead and put these lines in

connection_string = "mssql+pyodbc://{user}:{password}@host/db?driver=SQL+Server+Native+Client+11.0&Mars_Connection=Yes".format(user='someuser', password='somepassword')
%sql $connection_string

%sql select * from MY..SOMETABLE where 0=0 and SomeField= 'foobar'

And I get this error message:

(pyodbc.ProgrammingError) ('42000', '[42000] [Microsoft][SQL Server Native Client 11.0][SQL Server]The transaction operation cannot be performed because there are pending requests working on this transaction. (3981) (SQLExecDirectW)') [SQL: 'commit'] (Background on this error at: http://sqlalche.me/e/f405)

Going to the http://sqlalche.me/e/f405 was useless information. What do I do here to make this work?

Thank you!

Nov 06 '18 19:11 cmosguy

MARS_Connection=Yes

is case sensitive.

Nov 06 '18 21:11 waltertschwe

Awesome MARS_Connection=Yes worked for me