PRB: Procedures May Fail w/ Msg 2821 After v.6.0 Upgrade

• Microsoft SQL Server, version 6.0

SYMPTOMS

After you upgrade to Microsoft SQL Server version 6.0, you may encounter Msg 2821 when you try to access a stored procedure, trigger, or view:

   You must drop and recreate procedure %.*s.

CAUSE

When you upgrade a database to SQL Server version 6.0, all procedures, triggers, and views are recreated from the text definition stored in syscomments. If any of these objects fail this process during the upgrade, any attempt to access the object fails with Msg 2821. The upgrade process continues to check each object within a database, even if one fails.

Any attempt to execute a procedure fails with Msg 2821 specifying the procedure name. Any attempt to access a view results in Msg 2821 and Msg 4401. Additionally, any attempt to execute a command that causes a trigger to fire results in Msg 2821 with no procedure name specified in the error message.

WORKAROUND

After you upgrade to SQL Server version 6.0, you should scan the UPGRADE.OUT file found in the SQL60\INSTALL directory for any errors reported while upgrading user databases. Pay close attention to the section titled "Upgrading procedures, triggers, views....". Once the cause of the error is found and corrected, run the sp_db_upgrade procedure in the database that failed during the upgrade. This ensures the database has been properly upgraded for version 6.0.