Crystal 9.0 and stored procedures for SQL Server 2000

I have a stored procedure that I want to execute from crystal.  This procedure truncates a table, inserts records into the table and exits.  I then want to use the content of the table in Crystal?

How do I get the SP to execute first?
CREATE PROCEDURE [DBO].[sp_StudentEvaluationSummary]

@product_code as varchar(10),
@begin_date as datetime,
@end_date as Datetime

AS
--set @product_code = '2120141P01'
--set @begin_date = '09/23/2011'
--set @end_date = '9/23/2011'


--select mm.meeting,mm.begin_date into #tmp1 from meet_master mm where left(mm.meeting,7) = left(@product_code,7) and
--		mm.begin_date between @begin_date and @end_date 
--select * from #tmp1
--drop table #tmp1
--drop table #tmp1
select mm. meeting,mm.begin_date 
into #tmp1
from meet_master mm where left(mm.meeting,7) = left(@product_code,7) and
		mm.begin_date between @begin_date and @end_date 

--select * from #tmp1

--drop table #tmp2
--
-- Set up variables
--

truncate table summaryevalreport
--
-- Define cursor
--
	DECLARE program CURSOR FOR
	select meeting
	from #tmp1
--
--
-- Open Cursor
--

	OPEN program
-- 
-- Fetch loop
--
	FETCH NEXT FROM program into @product_code
	WHILE @@FETCH_STATUS = 0
	BEGIN
	insert into summaryevalreport
		select 	left(mm.meeting_type,2) as PA,
		mm.meeting as SeminarNumber,
		mm.muf_8 As Program,
		mm.begin_date,
		mm.muf_6 as hours,
		mm.city as Location,
		(select count (id) from activity where product_code = @product_code and activity_type = 'VOLUNTEER') as faculty_count,
		(mm.total_registrants - mm.total_cancelations) as Student_count,
		(select count(product_code) from student_evals where @product_code = product_code) as number_evals,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 1) as Poor,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 2) as Fair,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 3) as Good,
		(select count(product_code) from student_evals where @product_code = product_code and rating = 4) as Excellent,
		(select count(product_code) from student_evals where @product_code = product_code and rating not in (1,2,3,4)) as NoAnswer,
		(select count(product_code) from student_evals_faculty where @product_code = product_code and cast(comment as varchar(250)) <> '') as facultyevalcount,
		(select sum(rating) from student_evals_faculty where @product_code = product_code) as sumrating

		from meet_master mm
		where mm.meeting = @product_code and
		mm.begin_date between @begin_date and @end_date
	FETCH NEXT FROM Program into @product_code
	END

	CLOSE program
	DEALLOCATE program
--	select * from summaryevalreport
GO

Open in new window

Jeff_KingstonAsked:
Who is Participating?
 
Jeff_KingstonConnect With a Mentor Author Commented:
I wrote a sp that gets run every 4 hours an puts data into a truncated table which is used in Crystal

Klutzy I know but it is doing the job.

We had control of batch jobs and wait for completion options with VMS in 1984! when the hell is microsoft and their supporting theiving minions going to get on the train!!!
0
 
mlmccCommented:
Did you try using the SP as the data source?

That is the only way with Crystal by itself to execute an SP.

One idea would be to run the report from an application.  The pplication could execute the SP before calling the report.

mlmcc
0
 
James0628Commented:
The obvious thing would be to just have the SP output the table at the end and try using that as the datasource, as mlmcc suggested.  No guarantees, but that might work.  I've never tried to do something like what you're doing in a SP for a report.  You may need to turn row counts off at the beginning of the SP.  CR seems to have problems if a SP produces messages like row counts or warnings before the actual data.

 A slight variation on that would be to create a new SP for the report that exec'd that SP, and then used a SELECT to output the table.  Again, no guarantees, but it might work.  That way you don't have to change the existing SP to output data at the end, which would be useful if you want to use it in other places where you don't want that output.

 If CR 9 supports Commands (where you create the query manually in CR), then you might be able to create a Command that exec'd the SP and then had a SELECT to output the table.  Again, not something that I've tried.

 James
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
mlmccCommented:
I've requested that this question be closed as follows:

Accepted answer: 250 points for James0628's comment http:/Q_27373784.html#36896047
Assisted answer: 250 points for mlmcc's comment http:/Q_27373784.html#36892536

for the following reason:

This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0
 
Jeff_KingstonAuthor Commented:
Neither worked, I rewrote the entire process and have solved the problem
0
 
mlmccCommented:
What did you do to reqrite the process?

mlmcc
0
 
Jeff_KingstonAuthor Commented:
Please close this question
0
 
mlmccCommented:
You should be able to accept your last comment as the solution.

mlmcc
0
 
Jeff_KingstonAuthor Commented:
a work around
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.