Having trouble calling a stored procedure in SQL Server from Python pyodbc

user_5272759 0
Viewed 324

This is a common question if looks like, and I've gotten some good clues from other questions, but I still am not successful.

I have a complicated stored procedure in SQL Server that does a bunch of things in order to populate a table. It takes no parameters and does not return any fields. After I call the SP I then query the table -- it's a lot of data to I need to do it in batches, which works fine. Calling the SP from Python is proving the difficult part.

def executeSP():
    cnxn = pyodbc.connect('DRIVER={ODBC Driver 17 for SQL Server};SERVER=' +
            server+';DATABASE='+database+';UID='+username+';PWD=' + password)
    cnxn.autocommit = True
    cursor = cnxn.cursor()
    cursor.execute("SET NOCOUNT ON; exec [schema].[SPName]")   
    cursor.close()
    del cursor
    cnxn.close()

I set NOCOUNT to on so that python will wait until the SP is complete before returning. But when I run this, the SP isn't called. The same connection parameters work for querying the table in the same schema. No errors are produced.

I'm running out of ideas.

pyodbc python-3.x stored-procedures sql-server
1 Answers

Setting NOCOUNT ON is not always enough. To be sure, loop until pyodbc thinks there are no more resultsets, or you may cancel the execution before it's complete. eg

cursor.execute(sql)
while cursor.nextset():
    pass 
cursor.close()
user_72977000
Related