SQL : STORED PROCEDURE IN CRYSTAL REPORTS

hi all

i have the below sql sp, which when run in sql returns many rows as expected,

when i use crystal reports to display the results from the sp, it shows only the first result and ends

can anyone help me show all the row results in crystal?
create procedure sp_mr_geof2(@parent_part varchar(100) )
as

DECLARE @STOCK_ID int

DECLARE STOCKCHECK CURSOR FOR
select   STOCK_ID from  BomStructure with (NOLOCK), STOCK_MASTER SM 
 where ParentPart = @parent_part
   and Route = 0 
   and ERP_STOCK_CODE = Component 
order by STOCK_ID


OPEN STOCKCHECK

FETCH NEXT FROM STOCKCHECK INTO
  @STOCK_ID

WHILE @@FETCH_STATUS = 0
  BEGIN

  exec SP_CREATE_PICK_PIECE @STOCK_ID,-1,'AF',0,-1,2,0,1,0,1,0,0,0,44
FETCH NEXT FROM STOCKCHECK INTO
  @STOCK_ID

  END


CLOSE STOCKCHECK
DEALLOCATE STOCKCHECK

Open in new window

malraffAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
kingjelyConnect With a Mentor Commented:

Hey

You may need to
DEALLOCATE STOCKCHECK

as it will still exist next time you call the procedure if you don't

0
 
kingjelyCommented:
where are you putting the fields on the report.

Are you putting them in the details section?
0
 
malraffAuthor Commented:
yes
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
kingjelyCommented:
which single record is printing in crystals ? when you execute in sql is it the first or last? or random
0
 
malraffAuthor Commented:
its the first
0
 
malraffAuthor Commented:
i think i see!!

im going to create a temp table and insert the results into that - ill post back if it works!
0
 
kingjelyCommented:
Hi Sorry was on the phone, Okay sure NO probs ;)
0
 
malraffAuthor Commented:
hmmm,

ok i created the temp table and i copy the sp resullts into this table, and then delete the table

  works first time but when i reattempt to run the report i get an error stating

database connection error associated statement is not prepared  -- this is crystal

i think it may be something to do with the sp deleting the temp table again?

but in sql i get the error..

A cursor with the name 'STOCKCHECK' already exists.

any ideas?
0
 
malraffAuthor Commented:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_mraff_2](@parent_part varchar(100) )
as
 
CREATE TABLE #temp (CONTAINER_ID varchar(20),STOCK_ID varchar(20),MAIN_BIN_ID varchar(20),RACK_ID varchar(20),WH_ID varchar(20),CLASS_ID varchar(20)
,WAITING_MOVEMENT varchar(20),FREE_STK varchar(20),DEL_NOTE_ALLOCATION_QTY varchar(20),ORIG_QTY varchar(20),
STOCK_CODE_QUANTITY varchar(20),CHANGE_LEVEL_ID varchar(20),WH_SEQ varchar(20),CLASS_SEQ varchar(20),ProductGroup varchar(20),ProductClass varchar(20) )
 
DECLARE @STOCK_ID int
 
DECLARE STOCKCHECK CURSOR FOR
select   STOCK_ID from  BomStructure with (NOLOCK), STOCK_MASTER SM
 where ParentPart = @parent_part
   and Route = 0
   and ERP_STOCK_CODE = Component
order by STOCK_ID
 
 
OPEN STOCKCHECK
 
FETCH NEXT FROM STOCKCHECK INTO
  @STOCK_ID
 
WHILE @@FETCH_STATUS = 0
  BEGIN
 
  Insert into #temp
  exec SP_CREATE_PICK_PIECE @STOCK_ID,-1,'AF',0,-1,2,0,1,0,1,0,0,0,44
 
FETCH NEXT FROM STOCKCHECK INTO
  @STOCK_ID
 
  END
 
CLOSE STOCKCHECK
 
SELECT * FROM #temp
drop table #temp
0
 
malraffAuthor Commented:
ah yes,, i had this at the start ! head is all over the place!!
0
 
kingjelyCommented:
hehe, as long as you got it sorted ;)
0
 
malraffAuthor Commented:
lol now for the next one...

it works perfectly in crystal designer... when i then try to run the report out of designer it simply falls over

database connector error 'cannot  obtain  message from server'
0
 
kingjelyCommented:

When you say it falls over out of the designer, where do you mean? In MsSql?

Hrrm, What ODBC connector are you using, you may need to update the driver
0
 
malraffAuthor Commented:
in report viewer - its a crystal plugin to our erp system and where i give users access to the reports

its the same odbs in use when i create the report - and it works fine - u still think odbc issue?

or it may be a security issue? - although i do have admin rights on my account!
0
 
kingjelyCommented:
Unfortunately could be alot of things, SOmeone asked the same thing about 10 minutes ago, worked out we had to update the ODBC connector, but he was using oracle and needed an oracle ODBC connector otherwise SP dont function correctly, they worked for simple select Stored Procedures With Parameters, but INSERT INTO, or Creating TEMP tables got the same error.

But it could also be how you create the temp table

Have a read here,  
https://forums.sdn.sap.com/thread.jspa?messageID=7986824
0
 
malraffAuthor Commented:
ill have a look, thanks kingjely
0
 
malraffAuthor Commented:
ill close this question off kingjely as i think weve moved onto another question now!

thanks for helping me along tho!
0
 
kingjelyCommented:
Hehe yea we have,  no probs ;)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.