FIX: Stored Procedure Can't Be Upgraded

• Microsoft SQL Server version 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 

Additional query words: dbcc upgradedb

Keywords          : kbsetup SSrvInst SSrvStProc kbbug6.50 
Version           : 6.5
Platform          : WINDOWS 
Issue type        : kbbug 

Last Reviewed: April 8, 1999