Stored Procedure Returns No Data

[jtrapat1]

I'm using CR8 against a backend database of SQL Server 7.

I'm trying to use an sp as a datasource so I select it from the ODBC drop down, pull in the fields that I want but when I got to print preview I get no data back.

What am I doing wrong?

The stored procedure is basically a view; selecting the fields I need for my report.

I'll post the sp if it will help.

Thanks in advance.

 

[kenhamady]

If you use a single table instead of the SP as your data source, can you return records?

Do you get records if you run the SP within SQL Server?

Does the SP result in a table?
Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[jtrapat1]

Ken,

Thanks for your help.

You were right; I took the sp back to Query Analyzer and it did have some syntax errors. I fixed them and now they fill up the report fine.

The reason I was trying to use a sp was that I couldn't get my View working correctly.

Which would you rather use: A View or a stored procedure for better performance?
I'll probably have about 10,000 records coming back and I'll try to filter them as much as possible but there are still going to be some big reports.

When I tried this view it brought back the first page in about 20 seconds (very good) but if I want to page forward one page at a time, it takes a long time to display the next page. It's almost as if the query is being executed again each time the user goes to the next page.

Any suggestions?
Thanks again for the help.

Here's the View":
(I put in the TOP 100 PERCENT so I could get the ORDER BY done on the server).

CREATE VIEW dbo.vwHotBills_rev
AS
SELECT TOP 100 PERCENT Bill.BillNum, Bill.Sponsor, Bill.Title,
Bill.CommitteeDate, Bill.OriginalCommittee, Bill.BAMCode,
Bill.BillMemoCode, CommitteeActions.ActionCode,
BillAssignments.Analyst, Bill.Bill99Code, Bill.SenateNum,
Requests.Requester, BillComment.Comment
FROM (((Bill INNER JOIN
BillAssignments ON Bill.BillNum = BillAssignments.BillNum)
LEFT JOIN
CommitteeActions ON
(Bill.BillNum = CommitteeActions.BillNum) AND
(Bill.BillNum = CommitteeActions.BillNum)) LEFT JOIN
Requests ON Bill.BillNum = Requests.BillNum) LEFT JOIN
BillComment ON Bill.BillNum = BillComment.BillNum
WHERE ((BillAssignments.BillNum IS NOT NULL AND
Requests.BillNum IS NOT NULL) OR
(Bill.Sponsor <> 'Budget' AND (Bill.Bill99Code = 'Y' OR
(Bill.InWAM = 'Y' AND (Bill.BAMCode IN ('R', 'X', 'U')))) AND
CommitteeActions.ActionCode IS NULL))
ORDER BY Bill.BillNum, BillAssignments.Analyst

 

[kenhamady]

Most of my customers use the tables directly, so I don't have enough experience to make that recommendation. It probably depends on some environmental factors. Maybe someone else will weigh in. Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[jtrapat1]

Ken,

Thanks for your input.

Since I'm getting the data back from my View very fast, but my ORDER BY clause seems to be slowing the report down,
How would I go about doing an ORDER BY on the client side?

I can't find any info on the seagate site or in the crystal booklet.

I know you can do a GROUP BY, so it must be possible.

Is there a way to do this from the crystal IDE?

Thanks

John

 

[kenhamady]

You can have Crystal do the sorting and grouping on the client.
Report - Sort Records or
Insert - Group Ken Hamady

http://www.kenhamady.com/

On-site custom Crystal Reports Training and Consulting.
Quick Reference Guide to using Crystal in VB.

 

[balves]

What's faster depends on your overall architecture, how big the server is, how fast the PC is, etc.

Generally, the big fast server with lots of memory is the place to do the ORDER BY. However, if you're server is swamped with jobs and you have a fast PC, then it might be better to sort on the PC.

You can run your stored procedure or view through the query analyzer and see if your query it hitting table indexes versus doing table scans. As your data tables get large, you want to avoid table scans. Depending upon the database, WHERE clauses like IS NOT NULL or IS NULL force a table scan. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer
(Currently available for consulting in Chicago)

 

[MalcolmW]

Also, because of the way you have linked your tables, the first two clauses in your WHERE statement (the NULL tests) are redundant, and can be removed.
I don't understand the reason for the top 100% clause - the ordering will be done on the server based on what is in the ORDER BY clause. To ensure that Crystal Reports doesn't do try to do any sorting on the client, change the groups in the report to use the &quot;original&quot; sort order option. Then ensure that your sp or view sorts the records correctly for the report through the order by clause.
Ordering of records is usually very cheap to do on the database server - that is extremely unlikely to be the problem here. I suspect the performance problem is the large number of ORs in the WHERE clause (your IN statement is a shorthand for multiple ORs as well).
Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[jtrapat1]

Thanks for all the help.

We are taking this query from a mainframe written in Pascal and transferring it to SQL Server;

When I removed the two NULL tests, I got back about 632 records compared to over 1200 for the original report, so I'll have to review my SQL again.

I had to use the TOP 100 PERCENT clause in my View when it gave me this error:

&quot;An ORDER BY clause is invalid in views, ... etc. &quot;
and this was the only way I could get the query processed on the server.

I tried to sort by the method you suggested but couldn't get it to work;
I already have a Group section by Analyst but when I tried to sort by original, I couldn't figure out how to keep the Analysts together (and in ascending order).

I did run it thru sql query analyzer and it is being slowed down by the table scans like you mentioned.

Thanks again for all of the observations and tips on the query.

 

[MalcolmW]

Your view is ordered by billing number, then analyst. It sounds like you want it ordered by analyst, then billing number. That will keep your analysts together.
You might wish to consider using a stored procedure instead of a view. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net

 

[jtrapat1]

Malcolm,

Thanks for the tips.

I tried this as a view and as a stored procedure and both executed in about the same speed.
It's like you said - my sql statement has a lot of stuff that slow it down; I'm going to try to rewrite it so it will be more optimized.

I'll try to explain my report output better:

Besides the above sql, I defined a Group section on my report.

So, the output looks like this:

Analyst: Arnold

BillNum Analyst
A00600 David
Ellerton
Franklin

A00602 Turner

Analyst: Becker

BillNum Analyst
A00450 Henson
Jackson
.
.
.

This gets more complicated because my Analyst Group Header is actually a Primary Analyst field and the Analyst field in the detail section is a subreport which I linked to the main report recordsource (the same stored procedure).

If there is an easier way to do this, please let me know.
I've been trying everything.

I also tried this:
Since I have three subreports on this report, I tried it as a View of the main report, and then I defined three other separate smaller views for each of the subreports (linked by BillNum).
I also did this same technique with a stored procedure and three separate sps.
I thought it would work faster than having one sp and then linking all three subreports to the main stored procedure.

Thanks in advance.

 

[MalcolmW]

Subreports are your performance problem. Every time a subreport gets called, the SQL for it is executed. If your subreport is in the detail section, it is executed once for every record in the main report.
The paging through the report is slow because it sounds like each page requires this view/stored proc to be created several times.
The advantage of using a stored procedure is that you can write SQL to avoid the use of sub reports.
While you may be able to tweak your view, the biggest performance boost will be in eliminating those pesky subreports. Malcolm Wynden
I'm for sale at

http://wynden.net

malcolm@wynden.net