pypyodbc - Invalid cursor state when executing stored procedure in a loop

Question

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 once (via python), there are no problems. However, when it is executed multiple times within a for loop, I get the following error:

pypyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][SQL Server Native Client 11.0]Invalid cursor state')

My python code details are below:

connection_string_prod = 'Driver={SQL Server Native Client 11.0};Server=PSCSQCD1234.TEST.AD.TEST.NET\SQLINS99,2222;Database=Test;Uid=python;Pwd=python;'

connection_prod = pypyodbc.connect(connection_string_prod)

cursor_prod = connection_prod.cursor()

get_files_to_load_query = "Get_Files_To_Load"

files_to_load = cursor_prod.execute(get_files_to_load_query)

for file_name in files_to_load:
    load_query = "Load_Query_Stored_Proc @File_Name = '" + file_name + "'"
    cursor_prod.execute(load_query)

cursor_prod.commit()
cursor_prod.close()
connection_prod.close()

In some posts it was suggested to use "SET NOCOUNT ON" at the top of the SQL stored procedure. I've done that already and it did not help with this issue.

Below is a code of a simple stored procedure that is causing the issue:

CREATE PROCEDURE [dbo].[Test]
AS
SET NOCOUNT ON

INSERT INTO Test(a)
SELECT 1

Why executing the stored procedure within a for loop only causes an issue?

Please advise.

Thank you!

Answer 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.