Commandtext does not return a result set error

Data Abstract
1

SQL Server 2017, ADO, DA 8.3.93.1183. Trying to run a stored procedure that returns a dataset. SP works fine in Management studio. Run as a table with SQL: “exec get_jotform_submissions :depot” where :depot is a simple string input parameter. Get “commandtext does not return a result set” error. Puzzled as I have done this before all OK
Rgards
Roger

2

Hi,

can you share DDL for this stored procedure, pls?
you can drop email to support@ for keeping privacy

3

Hi

Attached

Regards

Roger

sp.txt (8.73 KB)

4

Hi,

it said

Msg 208, Level 16, State 1, Procedure get_jotform_submissions, Line 47 [Batch Start Line 0]
Invalid object name ‘plan_scheduler’.

so I’ve reduced code to

/****** Object:  StoredProcedure [dbo].[GET_JOTFORM_SUBMISSIONS]    Script Date: 22.12.2021 13:35:38 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[GET_JOTFORM_SUBMISSIONS]
  @depot varchar(15)
 
AS
if cursor_status('global','jotformcursor')>= -1
begin
 deallocate jotformcursor
end

create table #resultstable(id int,resourceid int,plan_id int, system_id int,plot_id int,measure_id int,
measure_code varchar(30),measure_description varchar(100),startdate datetime,resource_name varchar(50),
measure_qty numeric(9,2),worksheet_no int,plot_no varchar(15) ,site_name varchar(50),worksheet_code varchar(10),
sub_count int, dropped varchar(15),prechecks varchar(5),postchecks varchar(5),signoff varchar(5),dropped_plot char(1)); /*temp table*/

select * 
from #resultstable
order by sub_count desc

drop table #resultstable
GO

it works in Schema Modeller w/o any issues

5

Hi

Interesting – ended up with adding “nocount on” at the beginning and “nocount off” at the end

Strange one

Thanks anyway :blush:

Regards

Roger