1. Paul Weir
  2. InfoMaker
  3. Thursday, 13 July 2023 21:24 PM UTC

I have a user who is trying to reference a stored procedure from InfoMaker.

 

Here are the steps she's taking.

 

Create a New Tabluar Object.

 

 

In Tabular Report Generator, click Stored Procedure, and then click Next.

 

 

Choose the stored procedure that the user created, and then click Next.

 

 

Here's the code behind that stored procedure.

 

USE [TmsEPrd]

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROCEDURE [dbo].[SHU_SP_CHS_REPOPULATE_TEL_WEB_GRP_CDE]

    AS

UPDATE sm

SET sm.TEL_WEB_GRP_CDE = 'CH'

FROM NameMaster nm

INNER JOIN STUDENT_MASTER sm ON nm.ID_NUM = sm.ID_NUM

INNER JOIN DEGREE_HISTORY dh ON nm.ID_NUM = dh.ID_NUM AND dh.CUR_DEGREE = 'Y' AND dh.MAJOR_1 = 'CHS'

WHERE sm.TEL_WEB_GRP_CDE IS NULL AND

      ISNUMERIC(sm.UDEF_10A_1) = 1 AND

      -- You can modify the > to >= if you want the years greater than the current year AND the current year

      sm.UDEF_10A_1 > (SELECT CONVERT(varchar,YEAR(getdate())))

 

Click Next on Select Color and Border Settings.

 

 

Click Finish on Ready to Create Tabluar Report.

 

 

This error message appears and doesn't allow for the continuation of creating the stored procedure object.

 

 

DataWindow Wizard

Cannot create DataWindow.

'Requested result set number 1 not found.

1 execute dbo.shu_sp_chs_repopulate_tel_web_grp_cde; 1'

 

When I run the stored procedure in SSMS it returns 0 rows. Is that the problem? It doesn't give an error - it just returns nothing.

 

Thanks as always!

 

Paul Weir

Accepted Answer
Chris Pollach @Appeon Accepted Answer Pending Moderation
  1. Friday, 14 July 2023 22:08 PM UTC
  2. InfoMaker
  3. # Permalink
Hi Paul; No ... IM does not support that but you can do this in PowerBuilder as any DataWindow (ie: IM Report) as PB allows you to assign a SP to each action (ie: Retrieve, Update, Insert & Delete). IM only supports the Retrieve action - same for Forms. Sorry. Regards ... Chris
Comment
There are no comments made yet.
René Ullrich Accepted Answer Pending Moderation
  1. Friday, 14 July 2023 04:54 AM UTC
  2. InfoMaker
  3. # 1

Hi Paul,

a datawindow needs a resultset. Your stored procedure seems not to select data. Instead it looks like it should update data (if I understand it right). This is not how a datawindow works.

HTH,

René

Comment
There are no comments made yet.
Paul Weir Accepted Answer Pending Moderation
  1. Friday, 14 July 2023 11:54 AM UTC
  2. InfoMaker
  3. # 2

Thanks, René! I see what you're saying here.

Now, is there any way to set up something in InfoMaker that just runs a stored procedure even if the SP itself returns no results but instead just updates some field(s)?

Comment
  1. John Fauss
  2. Saturday, 15 July 2023 02:40 AM UTC
Hi, Paul -

I’m unsure what kind of result set the user wants to return, but you might try adding a SQL SELECT statement after the SQL UPDATE statement, just so the stored procedure generates a result set.
  1. Helpful 1
  1. Miguel Leeuwe
  2. Saturday, 15 July 2023 05:39 AM UTC
yes, that's what I thought too.

You could for example put a SELECT at the end of the amount of updated rows (@@rowcount).
  1. Helpful
There are no comments made yet.
Roland Smith Accepted Answer Pending Moderation
  1. Friday, 14 July 2023 13:44 PM UTC
  2. InfoMaker
  3. # 3

In InfoMaker open the Database painter, type the below command in the 'ISQL Session' pane and click the Execute toolbar button or press Ctrl+L.

Comment
There are no comments made yet.
  • Page :
  • 1


There are no replies made for this question yet.
However, you are not allowed to reply to this question.