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?
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
ASKER
yes
which single record is printing in crystals ? when you execute in sql is it the first or last? or random
ASKER
its the first
ASKER
i think i see!!
im going to create a temp table and insert the results into that - ill post back if it works!
im going to create a temp table and insert the results into that - ill post back if it works!
Hi Sorry was on the phone, Okay sure NO probs ;)
ASKER
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?
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?
ASKER
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_mraff_2](@parent
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_ALLOC
STOCK_CODE_QUANTITY varchar(20),CHANGE_LEVEL_I
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
FETCH NEXT FROM STOCKCHECK INTO
@STOCK_ID
END
CLOSE STOCKCHECK
SELECT * FROM #temp
drop table #temp
set QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[sp_mraff_2](@parent
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_ALLOC
STOCK_CODE_QUANTITY varchar(20),CHANGE_LEVEL_I
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
FETCH NEXT FROM STOCKCHECK INTO
@STOCK_ID
END
CLOSE STOCKCHECK
SELECT * FROM #temp
drop table #temp
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
ah yes,, i had this at the start ! head is all over the place!!
hehe, as long as you got it sorted ;)
ASKER
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'
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'
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
ASKER
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!
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!
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
But it could also be how you create the temp table
Have a read here,
https://forums.sdn.sap.com/thread.jspa?messageID=7986824
ASKER
ill have a look, thanks kingjely
ASKER
ill close this question off kingjely as i think weve moved onto another question now!
thanks for helping me along tho!
thanks for helping me along tho!
Hehe yea we have, no probs ;)
Are you putting them in the details section?