Pypyodbc - Invalid Cursor State When Executing Stored Procedure In A Loop

I have a python program which uses pypyodbc to interact with MSSQL database. A stored procedure is created in MSSQL and is run via python. If I execute the stored procedure only

Solution 1:

You are using cursor_prod values to control the for loop and then using that same cursor object inside the loop to run the stored procedure, thus invalidating the previous state of the cursor for controlling the loop. The first iteration of the loop is where the cursor gets overwritten, which is why you don't encounter the error until you try to go through the loop a second time.

You don't need to create a second connection, but you do need to use a second cursor to execute the stored procedure. Or, you could use .fetchall to stuff all of the file names into a files_to_load list and then iterate over that list, freeing up the cursor to run the stored procedure.