Accessing SQL Stored Procedures that create temporary tables

I've created a SQL stored procedure that I now need to access from Crystal Reports XI via the Database Expert.  When attempting to do so from CR, I am prompted for the single parameter in my stored procedure, so CR is successfully accessing the correct stored procedure, but then CR then won't allow the connection to complete giving the following error message:

Database Connection Error: 'Cannot obtain error message from server'

I can connect, however, to a simple version of this same stored procedure that does not include the creation of temporary tables so I am assuming this error has something to do with SQL table creation from Crystal, but can't figure out what the specific problem is so I've run short on ideas of what to do next.

makingitbigAsked:
Who is Participating?
 
Kurt ReinhardtSr. Business Intelligence Consultant/ArchitectCommented:
Crystal Reports accepts Stored Procedures as data sources. The proc may populate tables, but the end result needs to be a single recordset.  If it doesn't return a recordset then you're not using it right...

~Kurt
0
 
mlmccCommented:
Does the SP run in Query Analyzer

Is the SP returning a recordset?

mlmcc
0
 
makingitbigAuthor Commented:
I'm not familiar with Query Analyzer.  The SP doesn't return anything, just creates a table that I then need to read from.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
makingitbigAuthor Commented:
Also, the SP executes properly within SQL.  It creates the needed table with no errors.
0
 
Anthony PerkinsCommented:
>>I'm not familiar with Query Analyzer<<
If you are using SQL Server 2000 than it is a client tool to execute SQL Server queries.
If on the other hand you are using SQL Server 2005, 2008, 2008-R2 than it is the same as the query window in SSMS.

But as stated previously, if you are not returning a resultset there is not a lot Crystal Reports can do with your Stored Procedure.  If you are returning a resultset (as I suspect you are), than the fix may be as easy as adding SET NOCOUNT ON to the top of your Stored Procedure.
0
 
makingitbigAuthor Commented:
Got it.  You get the points.

Once I added a Select statement to output my data, CR could see it and my program now works.

Thanks!
0
 
makingitbigAuthor Commented:
An added question, as I mentioned, I was able to solve this by use of a Select statement to 'display' the fields.  You mentioned using SET NOCOUNT ON.  This was already in my procedure at the top - but I'm not clear how that would have helped (as it wasn't working properly without my adding the Select at the end to produce the recordset).
0
 
mlmccCommented:
WIthout the SET NOCOUNT ON, SQL returns a message before the data that indicates how many records were processed.  It gets passed to the report but can't be interpreted since it doesn't match the recordset.

mlmcc
0
 
makingitbigAuthor Commented:
So, the SET NOCOUNT ON really should have no effect other than to display how many records were processed?  I can see where that might be helpful in testing or debugging, but not for my purposes as it wouldn't match the recordset, as you indicated, correct?
0
 
Anthony PerkinsCommented:
>>So, the SET NOCOUNT ON really should have no effect other than to display how many records were processed?<<
I thought mlmcc had explained it very well.  But here it goes again in case it was not clear:  SET NOCOUNT ON is required in order for you to view any report that is generated by a Stored Procedure that contains more than one SQL Statement.

In general SET NOCOUNT ON is always a good idea.  Here is what SQL Server BOL has to say on the subject:
SET NOCOUNT ON prevents the sending of DONE_IN_PROC messages to the client for each statement in a stored procedure. For stored procedures that contain several statements that do not return much actual data, setting SET NOCOUNT to ON can provide a significant performance boost, because network traffic is greatly reduced.

I trust it is clear now.
0
 
makingitbigAuthor Commented:
Indeed.

Again, thank you and I really appreciate the info!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.