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.

score 5 · Answer 1 · answered Jun 02 '17 at 15:43

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

score 2 · Answer 2 · edited Jun 03 '17 at 14:35

I would STRONGLY recommend that you use parameter replacement in a query like this, and use the CALL functionality to call the stored procedure as well. Try code like this:

cur.execute('{CALL stored_procedure(?)}', ('2017-05-01',))

There are a number of reasons why this could fail with a 24000 error. The most common (as David Browne alludes to above) is not using a SET NOCOUNT ON statement in your stored procedure, but anything returning multiple result sets will also generate this error.

There are other things that can generate this error, such as an outsized data element in the returned values (e.g. a 1MB text block when you are configured to return a max of 8KB of text). If the NOCOUNT fix does not work, we may need to see your stored procedure as well.

Python 3 and pypyodbc stored procedure error

2 Answers2