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.
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.
ASKER
I'm not familiar with Query Analyzer. The SP doesn't return anything, just creates a table that I then need to read from.
ASKER
Also, the SP executes properly within SQL. It creates the needed table with no errors.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>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.
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.
ASKER
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!
Once I added a Select statement to output my data, CR could see it and my program now works.
Thanks!
ASKER
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).
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
mlmcc
ASKER
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?
>>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.
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.
ASKER
Indeed.
Again, thank you and I really appreciate the info!
Again, thank you and I really appreciate the info!
Is the SP returning a recordset?
mlmcc