Reporting from Stored Procedures 2

[jduckwo]

I am creating a temp table in a Microsoft SQL Server 7 Stored Procedure (SP), populating it, and trying to return a recordset for the temp table from the SP for Crystal Reports to report off of. My report is not returning any data. Am I returning the recordset from the SP incorrectly or can I even use a temp table from the SP to return data to Crystal? Any info would be greatly appreciated. Thanks in advance.

Jake Duckworth
Senior Programmer

 

[MalcolmW]

You can use both local and global temp tables, other stored procs, in stored procs - Crystal Reports just sees what the final select statement in the stored procedure returns.
Can you execute the sp from the SQL Server Query Designer?
ie EXECUTE spname parameter1value, parameter2value, etc.
If that returns a record set, then Crystal should do the same thing - that is exactly what it does to get info from a stored procedure. Malcolm

 

[jduckwo]

Yes I can Execute my SP from Query Analyzer, and it returns the proper recordset. I thought Crystal could report off of the temp tables as well, but I am still not getting any data. Here is the format my SP is in. May be you can see where it might be screwed up.

CREATE PROCEDURE [RejectSummaryReport]
	@RecordSourceCode VARCHAR(4),
	@TransMonth CHAR(6)
AS 

CREATE TABLE #Summary (ForcedError  VARCHAR(10), Description VARCHAR(100), InvalidItem VARCHAR(100), Total VARCHAR(20)) 

INSERT INTO #Summary SELECT '1' AS Error, 'NULL Data' AS Description, CAST(DataField AS VARCHAR) AS InvalidItem, COUNT(DataField) AS Total WHERE TransMonth = @TransMonth AND RecSrc = @RecordSourceCode FROM Table1 

INSERT INTO #Summary SELECT '2' AS Error, 'Invalid Other Data' AS Description, CAST(DataField AS VARCHAR) AS InvalidItem, COUNT(DataField) AS Total WHERE TransMonth = @TransMonth AND RecSrc = @RecordSourceCode FROM Table1 

SELECT * FROM #Summary ORDER BY Error, InvalidItem, Description

Thanks again for any help.

Jake

 

[balves]

The format of the stored procedure is fine. The last SELECT * FROM #XXX is the recordset that gets returned to Crystal.

I would declare my input variables to be VARCHAR() so as to not worry about trimming spaces. Brian J. Alves
Terrier Consulting, Inc.
Email: brian.alves@worldnet.att.net
VB / Crystal / SQLServer

 

[jduckwo]

I found my problem. I wasn't setting NOCOUNT ON at the beginning of the SP, so the count messages for the inserts were throwing Crystal off. Thanks for your help!

Jake Duckworth
Senior Programmer

 

[MalcolmW]

Hmm that's strange. That doesn't give me problem with sQL Server 7, although I usually set nocount on just to improve performance and reduce network traffic. I wonder if there is a Crystal Reports setting that is relevant here... Malcolm

 

[Guest_imported]

Hey Friends,
What Jake says is true. That solved my problem too. I am using SQL server 6.5
Parag Jagtap

 

[yangrong]

Hi
I have the same problem like jake had, but "set nocount on" doesn't work for me. I have a couple temp table in my stored proc. If I use database driver "pdssql.dll [SQL SERVER]", it works fine. But when I use "pdsodbc.dll [ODBC]", it doesn't get any data. because this report is going to web, so I have to use "pdsodbc.dll [ODBC]". Any advise would be greatly appreciated.

yangrong

 

[giordan0]

I use Crystal report 8.5 with SP on SQL 7.
Without SET NOCOUNT ON, I received also an NO FIELD error.

 

[synapsevampire]

yanggrong: Why do you have to use the ODBC driver for WEB based reports?

-k

http://www.informeddatadecisions.com

kai@informeddatadecisions.com