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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
makingitbigAuthor Commented:
Also, the SP executes properly within SQL.  It creates the needed table with no errors.
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.