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

• Microsoft SQL Server version 6.5

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 version 6.5.

Additional query words:

Keywords : kbSQLServ650bug
Version : winnt:6.5
Platform : winnt
Issue type : kbbug