ExecuteNonQuery returns -1 even though the execution was successful

I'm running an asp.net 2.0 web application. Suddenly this morning, ExecuteNonQuery started returning -1, even though the commands in the SP that ExecuteNonQuery is running, are executing (I see items inserted and updated in the DB), and there is no exception thrown.

This happens only when I am connected to our production DB. When I'm connected to our development DB they return the correct number of rows affected.

Also, interestingly enough, ExecuteDataSet doesn't have problems - it returns DataSets beautifully.

So it doesn't seem like a connection problem. But then, what else could it be?

From stackoverflow

• -1 means "No rows effected".
  SELECT statement will also return -1.

  Check the effect of the SP on dev and live environments.

  Taken from SqlCommand::ExecuteNonQuery Method

  For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command. When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

• I encounter almost same problem (in Postgresql though) with NpgsqlDataAdapter, the Update method always returns 1.

  Perhaps your connection string to your development db is different from your production db. Or if there's really no difference between their connection strings, perhaps your production db's service pack is different from your development db.

• You can also check the procedure to see if there's this line of code in there -

  SET NOCOUNT ON
  

  This will cause the ExecuteNonQuery to always return -1.

  Lea Cohen : The problem was indeed NOCOUNT being on, but not in the SP level, but in the Database Server level. It was accidentally set when our DBA ran the following command: declare @option int set @option = @@options | 64 exec sp_configure 'user options', @option RECONFIGURE And the way to undo it is: In SQL Management Studio, right click the relevant Database Engine, and choose Properties. Then choose "connections" from the "Select a Page" list. There, under "Default connection options", un-check the checkbox near "no count"