CODEWITHSUNDEEP
pythonmysqlsql-serverstored-procedurespyodbc
dmcoding
dmcoding

Reputation: 331

pyodbc stored procedures / Microsoft SQL Server error 42000

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.

Upvotes: 1

Views: 10945

Answers (2)

Manasa Saila
Manasa Saila

Reputation: 11

I have faced this issue while I'm trying to execute a sql query inside python script where table name needs to taken from tuple during iterations. I tried multiple methods like using back ticks, square brackets as escape character also, as I thought the error is due to curly braces. Finally its due to formatting issue (even though table name is in string). I used- f"select * from {source_table}" then I'm able to get desired output.

Upvotes: 1

dmcoding
dmcoding

Reputation: 331

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')

Upvotes: 3

Related Questions