No rows returned via ASP but works in QA 1

[williamu]

Hi all,

I have a stored procedure that returns some 365 rows (one for each day of the coming year). When executed in Query Analyser or Enterprize Manager it works. However when executed via ASP it doesn't return a result set, I'm totally at a loss with this and I was wondering if anyone has experienced this before, and what the resolution to it is.

 

[gmmastros]

This may or may not work, but give it a shot.

In the SP, add 'SET NOCOUNT ON' to the beginning.

Ex.

Create Procedure Blah
  @Param1 VarChar(10),
  @Param2 Integer
AS
[red]SET NOCOUNT ON[/red]

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[williamu]

Cheers Man you've made my day, can you explain to me why this is, just so as I know.

 

[gmmastros]

Ok, I'm not really sure. Here's what I think and someone will correct me if I'm wrong.

I think that with NOCOUNT OFF, you get several recordsets back from the DB. On contains the record count, and the other contains the data.

I've made it a habit to use SET NOCOUNT ON in all my SP's that return data.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom