Solved

SQL : STORED PROCEDURE IN CRYSTAL REPORTS

Posted on 2010-08-25
18
1,015 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 

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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
In this video we outline the Physical Segments view of NetCrunch network monitor. By following this brief how-to video, you will be able to learn how NetCrunch visualizes your network, how granular is the information collected, as well as where to f…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

627 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