1

Consider the following (Semi-psuedocode):

CREATE TABLE foobar (
    id INT NOT NULL
    message VARCHAR(40) NOT NULL
)

INSERT INTO foobar (1, "hello");
INSERT INTO foobar (2, "world");


my $resultset = "SELECT * FROM foobar";
while(!$resultset->EOF) {
    "UPDATE foobar SET message='blah' WHERE id = ".$resultset->id;
    $resultset->moveNext;
}

In essence, I am trying to select all records from a table, and then loop over each one updating a few fields. However, when I do this, I get the following error:

[Microsoft][SQL Server Native Client 10.0][SQL Server]A trigger returned a resultset and/or was running with SET NOCOUNT OFF while another outstanding result set was active. (SQL-42000) at continuous.pl line 493.

The problem is that I am not running any triggers. I thought that maybe this was because I am trying to update an active result set, so I added the intermediary step of selecting all the data into a temp table, and then iterating over the temp table to update the original one, but I still get the same error.

Any ideas what is going on?

dmarra
  • 853
  • 8
  • 23
  • 1
    How do those pieces of SQL get executed? In most languages, you would have at least a connection object or command object involved. I'd expect that error message if you're reusing the same connection object. – Damien_The_Unbeliever Jan 09 '13 at 15:41
  • So, did you check if there is indeed such a trigger on `foobar`? – Andriy M Jan 09 '13 at 15:42
  • And by the way, if your update statement is really setting columns in all rows to fixed values, you could avoid the loop and execute just `UPDATE foobar SET column1=value1, column2=value2...` (without any WHERE clause). – Andriy M Jan 09 '13 at 15:47
  • Well aside from the trigger problem, why on earth are you doing this in a loop? Why not a single update? – Aaron Bertrand Jan 09 '13 at 15:47
  • Also, I know you said this is pseudocode - but if all the callee is doing is looping through one result set to issue `UPDATE`s, I'd recommend replacing it with an entirely SQL based solution without loops. – Damien_The_Unbeliever Jan 09 '13 at 15:47

1 Answers1

1

Well I feel stupid. I DID have a trigger on the table, and it DID have nocount set to on, BUT, there was a single line of code right before setting nocount on in the trigger that only runs under rare circumstances, and it just so happened to run this time. Fixing the trigger fixed my issue. I should have more coffee.

The reason why I am doing this in a loop instead of a single update statement is because the real query is massively complicated (formatted nicely, it is about 100 lines), and the data being updated relies partly on the data in the row being updated, and other tables in the database. Granted, I could probably use a clever set of joins, but this is one of those situations where it is so complicated that I prefer to optimize later.

dmarra
  • 853
  • 8
  • 23