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

--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
	select meeting
	from #tmp1
-- Open Cursor

	OPEN program
-- Fetch loop
	FETCH NEXT FROM program into @product_code
	insert into summaryevalreport
		select 	left(mm.meeting_type,2) as PA,
		mm.meeting as SeminarNumber,
		mm.muf_8 As Program,
		mm.muf_6 as hours, 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

	CLOSE program
--	select * from summaryevalreport

Open in new window

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!!!
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.

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.

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.

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.
Jeff_KingstonAuthor Commented:
Neither worked, I rewrote the entire process and have solved the problem
What did you do to reqrite the process?

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

Jeff_KingstonAuthor Commented:
a work around
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.