FIX: Stored Procedure Can't Be Upgraded (164287)


The information in this article applies to:

  • Microsoft SQL Server 6.5
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
Modification Type:MinorLast Reviewed:2/22/2005
Keywords:kbBug kbfix kbsetup KB164287
©2004 Microsoft Corporation. All rights reserved.