Microsoft SQL Server - "Result set was generated for update"

If using Ignition (or FactorySQL/PMI, for that matter) with SQL Server and you run into the following error in various places:

you need to turn off the "no count" option for the server. It appears that the different versions of SQL Server are inconsistent as to whether it is on by default.

To change it:

  1. Go into the SQL Server management console
  2. Right-click on the server and select "properties".
  3. Select "connections"
  4. Under "Default Connection Options", turn off "no count".

The change will only take affect after the connections in Ignition's pool are re-established. You could restart the Ignition server, or go into the database connection configuration, show advanced options, and set "max idle connections" to 0. After saving, you can go back in and set it back to the previous value. This process will cause the existing connections to be closed and new ones created.

Just to clarify on step 4 when you say “turn off “no count”” do you mean to check the box which will turn off the messages or do you mean to turn off by unchecking the box which will turn on the messages?

I suspect I meant “uncheck the box”. We want/expect SQL Server to return the number of rows affected (though, since writing this in Jan 2011, we’ve adjusted some of those areas in Ignition to use a different scheme that doesn’t require this value).

Regards,

We are starting to see inconsistency with trying to insert a record into a sql server database that returns a key. sometimes the row is insert and a key is returned and other times the row is inserted and the following error is received leaving the database in a inconsistent state because it does not finish running the rest of the script.

yesterday this was happening on a different screen and after checking the box for no count in the default settings, restarting all the connections then changing the no count check box back to unchecked after finding out that caused all the inserts to fail the problem went away. Now today on a different window that uses the fpmi.db.runPrepStmtGetKey or we tried changing to system.db.runPrepUpdate(getKey=true) we get the same message that a result set was generated for update.

We have modified the code to use a transaction so that when the insert is performed and the error message is received the new row that is inserted is rolled back. we have found that if we click the button to insert the new record and it fails we can just keep pressing the button until the record is inserted and a key is returned successfully.

why does the statement sometimes fail to return a key and other times it returns a key just fine?

Thank you,