A result set was generated for an update

Ignition
1

Ignition 7.9.20 on Windows 12 Server

SQL Server 2019 on another computer.

For 7 years we have had the following working in a python script:

sql = \
"""
INSERT INTO table (col_1, col_2, col_3)
VALUES (?, ?, ?)
"""
system.db.runPrepUpdate(sql, [1, 2, 3])

Yesterday this started throwing errors "A result set was generated for an update...."

The script was changed to:

sql = \
"""
SET NOCOUNT OFF
INSERT INTO table (col_1, col_2, col_3)
VALUES (?, ?, ?)
"""
system.db.runPrepUpdate(sql, [1, 2, 3])

And it now works. Any idea why this failure could happen and why the fix fixes it?

TIA
Mack

2

I would guess someone on the DB side applied a patch that changed the default for the NOCOUNT setting.

3

They swear not.

4

There's not much else it can be.

5

There are many other similar scripts that are not failing....

6

Recycled connections might be holding onto the original defautls. Try disabling and re-enabling the DB connection. You'll probably see the rest of the scripts start to fail.

Consider adding SET NOCOUNT ON to the DB connection's initialization.

7

Would that be in the Extra Connection Properties? Is a keyword required before it?

8

Yes, not sure. I haven't used it with MS SQL Server.