Access second result set of stored procedure with SQL or other work-around? Python\pyodbc

Question

I'm using python\pyodbc and would like to access the second result set of a stored procedure. As near as I can tell, pyodbc does not support multiple result sets. Additionally, I can't modify the stored procedure. Are there any options to access the second result set using SQL or some other work-around? Perhaps create a second stored procedure that only returns the second result set of the first?

Answer 1

There are a few possible methods here. If the result sets are all the same, you might be able to use the INSERT...EXEC method. Otherwise OPENQUERY might work.

Answer 2

No need for anything fancy. Just use nextset:

import pyodbc

db = pyodbc.connect ("")
q = db.cursor ()
q.execute ("""
SELECT TOP 5 * FROM INFORMATION_SCHEMA.TABLES
SELECT TOP 10 * FROM INFORMATION_SCHEMA.COLUMNS
""")
tables = q.fetchall ()
q.nextset ()
columns = q.fetchall ()

assert len (tables) == 5
assert len (columns) == 10