Solved

Crystal 9.0 and stored procedures for SQL Server 2000

Posted on 2011-09-30
11
253 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
11 Comments
 
LVL 100

Expert Comment

by:mlmcc
ID: 36892536
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 35

Expert Comment

by:James0628
ID: 36896047
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
ID: 37058261
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 

Author Comment

by:Jeff_Kingston
ID: 37056947
Neither worked, I rewrote the entire process and have solved the problem
0
 
LVL 100

Expert Comment

by:mlmcc
ID: 37058262
What did you do to reqrite the process?

mlmcc
0
 

Author Comment

by:Jeff_Kingston
ID: 37082482
Please close this question
0
 

Accepted Solution

by:
Jeff_Kingston earned 0 total points
ID: 37084079
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
ID: 37084113
You should be able to accept your last comment as the solution.

mlmcc
0
 

Author Closing Comment

by:Jeff_Kingston
ID: 37105844
a work around
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
C# Crystal Reports 15 100
Can a formula be added to a cross tab report to report a % of change of two values? 5 38
Sum not calculating correctly 22 47
Crystal Report sorting 6 26
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…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

749 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