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: Data grouping tardiness
Viewed 3423 times since Tue, Oct 6, 2009
CR: Label your X-Axis with more than one identifier
Viewed 2655 times since Tue, Oct 6, 2009
CR: Installing CHARTrunner disables desktop wallpaper
Viewed 2152 times since Fri, Nov 9, 2007
CR: Excel tips
Viewed 3125 times since Tue, Oct 6, 2009
CR: Making an X-bar chart with a single data column
Viewed 2160 times since Fri, Nov 9, 2007
CR: Making a chart transportable
Viewed 2198 times since Fri, Nov 9, 2007
CR: Multiple chart lines from one data column
Viewed 2357 times since Fri, Nov 9, 2007
CR: Why is my first row of data not included when I get my chart from Excel
Viewed 2202 times since Fri, Nov 9, 2007
CR: A custom query for Length of Stay data
Viewed 2656 times since Fri, Nov 9, 2007
Cr: Charting elapsed time
Viewed 2558 times since Fri, Nov 9, 2007