Solved

SQL : STORED PROCEDURE IN CRYSTAL REPORTS

Posted on 2010-08-25
18
1,004 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this article I will describe the Backup & Restore 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.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now