Solved

SQL : STORED PROCEDURE IN CRYSTAL REPORTS

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

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

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…
Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…
This is a video that shows how the OnPage alerts system integrates into ConnectWise, how a trigger is set, how a page is sent via the trigger, and how the SENT, DELIVERED, READ & REPLIED receipts get entered into the internal tab of the ConnectWise …

932 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

13 Experts available now in Live!

Get 1:1 Help Now