FIX: Msg. 116 Doing Correlated Updates in a Stored Procedure

SYMPTOMS

Running a stored procedure that does a correlated UPDATE with trace flag 204 enabled may cause the following error:

The error only seems to occur consistently when running the procedure with trace flag 204 after the server is recycled (subsequent to creating the procedure).

The correlated UPDATE could be similar to the following:

   INSERT table1
   SELECT distinct x
   FROM table2
      UPDATE table1
      SET y = (SELECT SUM(z)
         FROM table2
         WHERE
   table1.x = table2.x
        )