Solved

Crystal 9.0 and stored procedures for SQL Server 2000

Posted on 2011-09-30
11
233 Views
Last Modified: 2012-08-14
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

0
Comment
Question by:Jeff_Kingston
  • 4
  • 4
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 
LVL 34

Expert Comment

by:James0628
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
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
 

Author Comment

by:Jeff_Kingston
Comment Utility
Neither worked, I rewrote the entire process and have solved the problem
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
What did you do to reqrite the process?

mlmcc
0
 

Author Comment

by:Jeff_Kingston
Comment Utility
Please close this question
0
 

Accepted Solution

by:
Jeff_Kingston earned 0 total points
Comment Utility
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
 
LVL 100

Expert Comment

by:mlmcc
Comment Utility
You should be able to accept your last comment as the solution.

mlmcc
0
 

Author Closing Comment

by:Jeff_Kingston
Comment Utility
a work around
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

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. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

772 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

14 Experts available now in Live!

Get 1:1 Help Now