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: Some Excel Spreadsheet Values Seem To Be Missing
Viewed 2241 times since Fri, Nov 9, 2007
CR: Changing the chart type will change the Default Chart Style to missing
Viewed 2140 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3067 times since Tue, Oct 6, 2009
CR: Formatting an Excel Date
Viewed 2497 times since Fri, Nov 9, 2007
CR: CR1.1 - Notes and Causes Problem
Viewed 2114 times since Fri, Nov 9, 2007
CR: Error “External table is not in the expected format” using Excel 2007 workbook
Viewed 4113 times since Wed, Oct 14, 2009
CR: Too many decimal places in the chart data grid for an Excel column that is calculated by a formula
Viewed 3759 times since Fri, Nov 9, 2007
CR: Histogram bars beyond specification lines
Viewed 2351 times since Fri, Nov 9, 2007
CR: Making an X-bar chart with a single data column
Viewed 2110 times since Fri, Nov 9, 2007
CR: Charts with large amounts of data
Viewed 1950 times since Fri, Nov 9, 2007