Knowledge Base

FIX: Stored Procedure Can't Be Upgraded

Article ID: 164287

Article Last Modified on 2/22/2005

APPLIES TO

Microsoft SQL Server 6.5 Standard Edition

This article was previously published under Q164287

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

Keywords: kbbug kbfix kbsetup KB164287

Microsoft KB Archive/164287

Contents