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

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.

Source: Python-3x Questions

LEAVE A COMMENT