I have a developer that has databound datagrids as follows:
Datagrid.datasource= DataSet (yes, the compiler allows this)
The dataset is returned from stored procedures. Each of the sps return
a single recordset, but periodically we are seeing some
nondeterministic behavior where the code for the datasource above is
causing a runtime error. More than one recordset is being returned for
some reason.
Of course, the easiest way to fix this is to have the developer change
code to
Datagrid.datasource = DataSet.Table("Some Table or Index"),
but is there are anything that can be done from a SQL Server
standpoint, that will only return one recordset ?
TIAMJKulangara wrote:
> I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
The usual cause of extra resultsets is the lack of a "SET NOCOUNT ON"
statement in the stored procedure. This statement tells the query engine to
suppress the informational "x rows affected" messages that you see when
running queries in QA. These messages get sent as extra resultsets.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||Thanks Bob. I checked each of the sps called and they all have "SET
NOCOUNT ON". The strange thing here is that we don't always get an
error. Most of the time (90%) applications rnus without a problem.|||"MJKulangara" <RutgersDBA@.gmail.com> wrote in message
news:1140627492.706939.31610@.z14g2000cwz.googlegroups.com...
>I have a developer that has databound datagrids as follows:
> Datagrid.datasource= DataSet (yes, the compiler allows this)
> The dataset is returned from stored procedures. Each of the sps return
> a single recordset, but periodically we are seeing some
> nondeterministic behavior where the code for the datasource above is
> causing a runtime error. More than one recordset is being returned for
> some reason.
> Of course, the easiest way to fix this is to have the developer change
> code to
> Datagrid.datasource = DataSet.Table("Some Table or Index"),
> but is there are anything that can be done from a SQL Server
> standpoint, that will only return one recordset ?
> TIA
Run the stored procedure in Query Analyser (with parameters that cause the
error in your page).
Check if any messages are generated.
I had a similar problem with a stored procedure and found that it was
because of null values in aggregate functions.
Adding SET ANSI_WARNINGS OFF to the procedure solved the problem.
Note: make sure ANSI_WARNINGS is ON in QA options before doing the test.
