Home » Categories » CHARTrunner

CR: SQL Server Stored Procedures Returns No Records

Revision Date: 2005-09-19

Problem:

In some cases when using a SQL Server stored procedure to fetch data for a chart no data is returned and an error message is displayed, or the program appears to “hang”.

Several different error messages may be returned depending upon the version of CHARTrunner.  Some customers have reported that CHARTrunner appears to “hang” when the chart is drawn.  Starting in version 1.6.49 the error message says “The stored procedure did not return any records”.

Solution:

This situation can occur if the stored procedure contains one or more action (e.g. INSERT, DELETE, UPDATE) or SELECT statements prior to the final SELECT statement that actually returns the records for the chart.  In this case the solution is to use SET NOCOUNT ON at the top of the stored procedure.

Apparently with SET NOCOUNT OFF (the default) SQL Server sends a "number of rows affected" message to the ADO provider for each action statement or SELECT statement in the stored procedure.  This confuses the ADO provider and results in the error condition.

Using SET NOCOUNT ON tells SQL Server not to send these "number of rows affected" messages and thus ADO doesn't get confused.

Attachments Attachments
There are no attachments for this article.
Related Articles RSS Feed
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4036 times since Wed, Oct 14, 2009
CR: A custom query for Length of Stay data
Viewed 2511 times since Fri, Nov 9, 2007
CR: Charting pass-fail data as a p-chart
Viewed 4450 times since Fri, Feb 29, 2008
CR: Charts not working on a new PC
Viewed 2000 times since Fri, Nov 9, 2007
CR: Charting counted incidents
Viewed 1935 times since Thu, Feb 23, 2012
CR: Largest Subgroup Size Supported by CHARTrunner
Viewed 2025 times since Fri, Nov 9, 2007
CR: Charts with large amounts of data
Viewed 1914 times since Fri, Nov 9, 2007
CR: Time Display
Viewed 2075 times since Fri, Nov 9, 2007
CR: CHARTrunner and SharePoint
Viewed 3970 times since Fri, Mar 9, 2012
CR: Background on out-of-control test rules
Viewed 2298 times since Fri, Nov 9, 2007