Solved

SQL : STORED PROCEDURE IN CRYSTAL REPORTS

Posted on 2010-08-25
18
1,006 Views
Last Modified: 2012-05-10
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

0
Comment
Question by:malraff
  • 10
  • 8
18 Comments
 
LVL 8

Expert Comment

by:kingjely
ID: 33520308
where are you putting the fields on the report.

Are you putting them in the details section?
0
 

Author Comment

by:malraff
ID: 33520396
yes
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520421
which single record is printing in crystals ? when you execute in sql is it the first or last? or random
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:malraff
ID: 33520431
its the first
0
 

Author Comment

by:malraff
ID: 33520838
i think i see!!

im going to create a temp table and insert the results into that - ill post back if it works!
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33520856
Hi Sorry was on the phone, Okay sure NO probs ;)
0
 

Author Comment

by:malraff
ID: 33521886
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
 

Author Comment

by:malraff
ID: 33521925
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
 
LVL 8

Accepted Solution

by:
kingjely earned 500 total points
ID: 33522021

Hey

You may need to
DEALLOCATE STOCKCHECK

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

0
 

Author Comment

by:malraff
ID: 33522051
ah yes,, i had this at the start ! head is all over the place!!
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33522198
hehe, as long as you got it sorted ;)
0
 

Author Comment

by:malraff
ID: 33522244
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
 
LVL 8

Expert Comment

by:kingjely
ID: 33522296

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
 

Author Comment

by:malraff
ID: 33522363
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
 
LVL 8

Expert Comment

by:kingjely
ID: 33522581
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
 

Author Comment

by:malraff
ID: 33522767
ill have a look, thanks kingjely
0
 

Author Comment

by:malraff
ID: 33529422
ill close this question off kingjely as i think weve moved onto another question now!

thanks for helping me along tho!
0
 
LVL 8

Expert Comment

by:kingjely
ID: 33529430
Hehe yea we have,  no probs ;)
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question