FIX: Stored Procedure Can't Be Upgraded

- Microsoft SQL Server, versions 6.5 BUG #: 14451 (Windows NT: 6.5)

SYMPTOMS

If a Microsoft SQL Server 6.0 database contains a stored procedure that references a view contained in a SELECT INTO statement, the upgrade process for the database may fail with the following errors when running sp_db_upgrade3 (with results found in the Upgrad1b.out file in the Mssql\Install directory or directly from LOAD DATABASE or sp_db_upgrade):

• Msg 2821, Level 16, State 2 You must drop and recreate procedure zview.
• Msg 4401, Level 16, State 2 View 'zview' no longer exists
• Msg 208, Level 16, State 1 Invalid object name '#x' Error occurred upgrading object myproc in database <name>, please drop And recreate.

WORKAROUND

To work around this problem, drop and re-create the procedure.

STATUS

Microsoft has confirmed this to be a problem in Microsoft SQL Server version 6.5. This problem has been corrected in U.S. Service Pack 3 for Microsoft SQL Server version 6.5. For more information, contact your primary support provider.

MORE INFORMATION

The following is a sample script to demonstrate objects that exhibit this problem:

   use pubs
   go
   create table mytab (col1 int)
   go
   create view zview as select * from mytab
   go
   create proc myproc as
   select *
   into #x
   from zview
   select * from #x
   go