Python 3 and pypyodbc stored procedure error

Question

I am currently running Python 3.4.2 and pypyodbc 1.3.6.

I am trying to run a stored procedure on a SQL server. When I run the stored procedure without the needed parameter (a date) I get an error stating that a parameter is needed (this was expected).

When I add in this date I get an error stating:

Invalid cursor state.

I have verified the date is in the correct format (YYYY-MM-DD) and I have tried it multiple ways but get the same error every time.

Below is the code that I am using:

import pypyodbc as odbc

connection_string = "DRIVER={SQL SERVER};SERVER=SERVERNAME;DATABASE=DATABASE;Trusted_Connection=yes"
conn = odbc.connect(connection_string)
cur = conn.cursor()
cur.execute("exec stored_procedure '2017-05-01'")
report_temp = cur.fetchall()

Error received:

line 975, in ctrl_err raise ProgrammingError(state,err_text)
pypyodbc.ProgrammingError: ('24000', '[24000] [Microsoft][ODBC SQL Server Driver]Invalid cursor state')

Any help is appreciated.

Answer 1

Ensure that the stored procedure is not outputting any row count or other messages before the result set. This can confuse the python drivers. EG this procedure fails with that error for that code

create or alter procedure stored_procedure @date datetime
as
begin
  --set nocount on
  select * into #t from sys.objects
  select * from #t
end

But uncommenting the 'set nocount on' allows it to succeed

create or alter procedure stored_procedure @date datetime
as
begin
  set nocount on
  select * into #t from sys.objects
  select * from #t
end