"Invalid cursor state" error when executing a batch that includes a USE statement

Question

I tried retrieving data from a Microsoft SQL database using pypyodbc 1.3.3 with Python 3.5 on Windows but got a pypyodbc.ProgrammingError '[24000] [Microsoft] [SQL Server Native Client 11.0] Invalid cursor state' using the following code:

import pypyodbc
conn = pypyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=database;UID=uid;PWD=pwd')
cursor = conn.cursor()
sql = '''USE database;
SELECT R0
FROM table;'''
cursor.execute(sql)
results = cursor.fetchone()
print(results)

The SQL works in Microsoft SQL Server Management Studio, the connection and executing worked in another script i wrote to insert into the same database and also works if i remove

results = cursor.fetchone()

So far I tried cursor.fetchone(), cursor.fetchall() and list(cursor) but all produced the same result which leads me to believe that the command itself isn't the problem. According to this microsoft site it means that there isn't an open cursor, but I can get it's description, so from my understanding there has to be.

Answer 1

I had a similar issue. I was able to resolve this by removing the "USE Database" statement.

You already connected to your db here:

conn = pypyodbc.connect(r'DRIVER={SQL Server Native Client 11.0};SERVER=server;DATABASE=database;UID=uid;PWD=pwd')

Answer 2

It's not a matter of being unable to execute a USE ... statement at all, it's just that we cannot do that as part of a multi-statement batch. So, this will not work ...

crsr.execute("""\
USE master;
SELECT TOP 2 name FROM sys.tables ORDER BY name;
""")
rows = crsr.fetchall()  # error

... but this will work fine

crsr.execute("USE master")
crsr.execute("SELECT TOP 2 name FROM sys.tables ORDER BY name")
rows = crsr.fetchall()

(Tested with both pypyodbc 1.3.4 and pyodbc 4.0.21)