BUG: Stored Procedure Having NULLIF or COALESCE with a Correlated Subquery Causes Error 2805 After Restore (240848)


The information in this article applies to:

  • Microsoft SQL Server 6.5
This article was previously published under Q240848
BUG #: 18840 (SQLBUG_65)

SYMPTOMS

An error similar to the following is returned by SQL Server when you attempt to execute a stored procedure right after a database RESTORE operation:
Msg 2805, Level 18, State 0 Bad pointer 0x11d4d66 encountered while remapping stored procedure 'pr_DailyBalanceWire'. Must re-create procedure.

CAUSE

This behavior has been isolated to a stored procedure fragment having the following properties:
  • A topmost SELECT statement referencing a particular table (Table1).
  • An output field within (#1) that uses a COALESCE or NULLIF expression.
  • The expression within (#2) is a subquery with a correlation to the outer table (Table2).
  • The subquery (#3) has a GROUP BY clause.

WORKAROUND

To work around the behavior, use these steps:
  1. Drop and re-create the stored procedure after restoring the database.
  2. Modify the stored procedure to avoid the properties mentioned previously.

STATUS

Microsoft has confirmed this to be a problem in SQL Server 6.5.
Modification Type:MajorLast Reviewed:10/3/2003
Keywords:kbBug kbpending KB240848
©2003 Microsoft Corporation. All rights reserved.