I'm working on a script to automate a file load procedure. So, naturally I need to perform some stored procedures that already exist. I'm using pyodbc to connect to my database. I can SELECT perfectly fine from the database, but when I try to execute from the database I get this error:
pyodbc.ProgrammingError: ('42000', '[42000] [Microsoft][SQL Server Native Client 10.0]
Syntax error, permission violation, or other nonspecific error (0) (SQLExecDirectW)')
I can't figure out what the problem here is - the user has full DB admin permissions, the syntax is correct based off what the pyodbc official documentation says.
print("Executing SP")
conn.execute('{EXEC TEMP.s_p_test}')
print("SP Executed.")
Here, TEMP is the schema for the type of stored procedure in that specific database. I.e., it's the full name of the stored procedure. I feel like it's probably something stupidly obvious that I'm just missing.
I tried a couple of things to fix it. As @Brian Pendleton suggested, I had tried to change from an explicit database user defined via UID and PWD to trusted_connection=True. Unfortunately that did not change anything.
However, out of curiosity I decided to see what taking the curly braces out of the function call would do. The execution worked immediately and produced the desired output. It would seem that the documentation at pyodbc's wiki either shows bad examples or I found a bug I don't know how to replicate because I don't know what makes my situation abnormal.
Or, in other words, instead of
conn.execute('{EXEC TEMP.s_p_test}')
I used
conn.execute('EXEC TEMP.s_p_test')