Stored Procedures and Crystal Reports?

[TheWall]

Is it possible to use a stored procedure (that creates a Global Temporary Table, populates it and at the end select all its content) as a data source for Crystal Reports?

I get an error stating that no rowset was returned when I try to add the SP as a record source in Crysal. Yet, it executes fine and displays the records in Query Analyzer.

Thanks for the help.

 

[bnagel2]

We are using version 8.5 of Crystal with SQL Server 2000, and stored procedures work just fine. Crystal even automatically creates a parameter field to prompt the user for any parameters necessary to run the Crystal report. It could be that your "no rowset returned" error is due to a permissions problem. Try running the report using the same authentication you use for Query Analyzer.

Good luck!

 

[cdukes]

My suggestion would be to check the output from Query Analyser. Are you doing Updates/Inserts, if so Does Query Analyser come up with

... 1 Row Affected.

Followed by your recordset.

If so, Crystal reports thinks that this is the recordset you are returning.

Put

SET NOCOUNT ON in your SP so that counts are not returned.

Hope this helps,

Chris Dukes

 

[TheWall]

Thanks guys, however, with regular stored procedures, I have no problems with Crystal. The error comes out when I use temporary tables within the stored procedure. Any ideas?

 

[troysf]

Add "set nocount on" right after the BEGIN statement in the stored procedure.

For full details, see:

http://support.crystaldecisions.com/library/kbase/articles/c2005342.asp

Troy
San Francisco, CA