Article ID: 254492
Article Last Modified on 10/17/2003
APPLIES TO
- Microsoft SQL Server 7.0 Standard Edition
- Microsoft SQL Server 2000 Standard Edition
This article was previously published under Q254492
SYMPTOMS
Very infrequently, after you use the SQL Server 7.0 Upgrade Wizard, a view may not successfully upgrade.
The failed view upgrade is seen in the Mssql7\Upgrade directory under the file name of Servername.Databasename.viw and contained text similar to the following:
-- ********** Statement #1 FAILED **********
/****** Object: View dbo.qryA Script Date: 1/21/00 7:16:38 PM ******/
/****** Object: View dbo.qryA ******/
CREATE VIEW qryA AS SELECT COLUMN1, COLUMN2
FROM tblBase
GO
/******
MESSAGE: Microsoft SQL-DMO (ODBC SQLState: 42S02)
DESCRIPTION: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'tblBase'.
sysmessages error: '208', SCODE_SEVERITY: '1'
-- ********** Statement #6 FAILED **********
/****** Object: View dbo.qryB Script Date: 1/21/00 7:16:39 PM ******/
/****** Object: View dbo.qryB ******/
CREATE VIEW qryB AS SELECT * FROM dbo.tblBase
GO
/******
MESSAGE: Microsoft SQL-DMO (ODBC SQLState: 42S02)
DESCRIPTION: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.tblBase'.
sysmessages error: '208', SCODE_SEVERITY: '1'
-- ********** Statement #12 FAILED **********
/****** Object: View dbo.qryC Script Date: 1/21/00 7:16:39 PM ******/
/****** Object: View dbo.qryC ******/
CREATE VIEW qryC AS SELECT * FROM dbo.tblBase
GO
/******
MESSAGE: Microsoft SQL-DMO (ODBC SQLState: 42S02)
DESCRIPTION: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.tblBase'.
sysmessages error: '208', SCODE_SEVERITY: '1'
-- ********** Statement #15 FAILED **********
/****** Object: View dbo.qryD Script Date: 1/21/00 7:16:39 PM ******/
/****** Object: View dbo.qryD ******/
CREATE VIEW qryD AS SELECT * FROM dbo.tblBase
GO
/******
MESSAGE: Microsoft SQL-DMO (ODBC SQLState: 42S02)
DESCRIPTION: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.tblBase'.
sysmessages error: '208', SCODE_SEVERITY: '1'
-- ********** Statement #21 FAILED **********
/****** Object: View dbo.qryE Script Date: 1/21/00 7:16:39 PM ******/
/****** Object: View dbo.qryE ******/
CREATE VIEW qryE AS SELECT COLUMN3, COLUMN4 FROM dbo.tblBase
GO
/******
MESSAGE: Microsoft SQL-DMO (ODBC SQLState: 42S02)
DESCRIPTION: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.tblBase'.
sysmessages error: '208', SCODE_SEVERITY: '1'
Conversely, the source view from which the preceding views are based is successfully created, although the source view is created at a later point in the upgrade process.
The successful creation of the source view is seen in the file Servername.Databasename.viw.out with text similar to the following:
-- ********** Statement #178 PASSED **********
/****** Object: View dbo.tblBase Script Date: 1/21/00 7:16:44 PM ******/
/****** Object: View dbo.tblBase ******/
CREATE VIEW tblBase AS SELECT COLUMN_1, COLUMN_2, COLUMN_3, COLUMN_4,
COLUMN_5, T1.COMPANY, T1.OFFICE, SEE,
FROM tblFirst T1, tblSecond T2 Where T2.COLUMN_1=COLUMN_2
AND (T1.COMPANY = T2.NOTCOMPANY Or T2.NOTCOMPANY="all")
AND (T1.OFFICE = T2.NOTOFFICE Or T2.NOTOFFICE="all" Or
(T2.NOTOFFICE="secure" And T1.SECURE = 0) ) AND T1.COLUMN_1=0
CAUSE
The specific scenario in the "Symptoms" section involves views that are dependent upon another view (tblBase). The "tblBase" view is dependent on a table that has been dropped and recreated, but the first level view and subsequent nested views are not. This information is missing from the sysdepends system table. Therefore, the migration procedure did not create the views in the correct order for a successful migration. If the sysdepends table is not up to date, the migration tool migrates the views in alphabetical order. In this case, the dependencies were later in the alphabet, hence they are created in the wrong order. If the views at both levels are also dropped and then recreated, then deferred object resolution is used effectively.
This behavior is expected in cases where objects (views in this case) are referencing object IDs that are no longer valid (per the dropping and recreating of the original table). Consequently, the SQL Server 7.0 UPGRADE WIZARD cannot rely upon the SQL Server 6.5 sysdepends table to assist in the orderly scripting of nested views if those views were not also dropped and recreated. Deferred object resolution cannot be in effect in this situation.
WORKAROUND
After the upgrade completes, take the view script from the .out file and rerun the script using the SQL 7.0 Query Analyzer. By performing this step, the missing object is created.
MORE INFORMATION
After an upgrade completes, it is always recommended that customers review the upgrade directory's *.out files to make sure that all the processes of the migration were successful. By reviewing the *.out files and finding any object creation failures, users can then rerun any script that failed during the upgrade.
Keywords: kbprb KB254492
