Running a complex stored procedure in Microsoft SQL Server with Python and pyodbc

Data and Databases
1

I’m having trouble executing a complex stored procedure in MS SQL Server using Python and pyodbc. The procedure takes an Excel table as input and uses declared variables, including some that are dynamically derived.

Here’s what I’ve tried so far:

connection_string = 'DRIVER={ODBC Driver 17 for SQL Server};SERVER=myserver;DATABASE=mydb;Trusted_Connection=yes;'
conn = pyodbc.connect(connection_string)
cursor = conn.cursor()

input_date = '20230515'
input_period = '202304'

try:
    cursor.execute("EXEC my_database..my_procedure @input_date = ?, @input_period = ?", input_date, input_period)
    conn.commit()
except pyodbc.Error as e:
    print(f'Error: {str(e)}')
finally:
    cursor.close()
    conn.close()

The code runs without errors, but the target SQL table is not updated. I’m wondering if pyodbc can really handle stored procedures with declared variables, or if there is another approach to executing this type of procedure. Any advice is much appreciated!

2

yo, maybe check ur proc permissions? sometimes that can mess things up. also, have u tried running the proc directly in ssms to see if it works there? could be a data issue or sumthin. if all else fails, maybe try using sqlalchemy instead of pyodbc? just a thought.

3

hey, have you tried sp_executesql? it might help with that procedur.

do u see any error logs? maybe add print outs in the proc or check if excel data is parsed as expected. how’s your proc coded?

4

Your approach seems correct, but there are a few things to consider. First, ensure your stored procedure is actually designed to update the target table. Sometimes procedures might not commit changes or might be using transactions incorrectly. Second, verify that the input parameters are being passed correctly. You might want to print out the values of input_date and input_period before execution to confirm. Additionally, if your procedure is using dynamic SQL internally, you may need to adjust how parameters are passed. Consider using SQL Server Profiler to capture the actual SQL being executed on the server side. This can help identify any discrepancies between what you’re sending and what’s being executed.