SUMMARY
When you upgrade from Microsoft SQL Server 6.5, the objects
that already exist on later versions of SQL Server are not transferred. A
"Differences" report file is created if any objects from the SQL Server 6.5
database cannot be created on the later version database. This does not mean
that the objects do not exist in the upgraded SQL Server 7.0 or the SQL Server
2000 database. You receive the following error message whenever a stored
procedure is already present in the later version database:
-- ********** Statement #number FAILED **********
Create procedure script
.
.
GO
/******
MESSAGE: Microsoft
SQL-DMO (ODBC SQLState: 42000)
DESCRIPTION: [Microsoft][ODBC SQL Server
Driver][SQL Server]Procedure
'Stored procedure name' group number 1
already exists in the database.
Choose another procedure name.
sysmessages error: '2729', SCODE_SEVERITY: '1'
******/
When you
upgrade to SQL Server 2000, the following error message displays:
/******
MESSAGE: Microsoft SQL-DMO (ODBC SQLState:
42S01)DESCRIPTION:
[Microsoft][ODBC SQL Server Driver][SQL Server]There is
already an object
named 'select_version' in the database.
sysmessages
error: '2714', SCODE_SEVERITY: '1'
******/
At the end of the
upgrade process, some information files appear. The file with the .prc
extension includes the CREATE PROCEDURE statements of all the stored procedures
that were not transferred, and the error message shown earlier in this
article.
After the error message occurs, the upgrade continues and
transfers other objects or records more error messages as the case may
be.
The error message occurs when the stored procedure already exists
in the destination database. Here are two reason why the stored procedure may
be present in the later version databases:
- The model database in SQL Server 6.5 has stored procedures with the same
name as the user database in SQL Server 6.5. When the model database in SQL
Server 6.5 is upgraded, all the stored procedures exist in the model database
of the later version of SQL Server. When a user database is being upgraded, the
model database in the later version of SQL Server is used as a template to
create the database and all the stored procedures in the model database are included in the new database. Consequently, when the
Upgrade Wizard tries to create a stored procedure that already exists, an error
message occurs.
- Running the Upgrade Wizard on a database that has already
been upgraded. When you upgrade the second time, an error message occurs, which
states that the database already exists. Ignoring this error message and
continuing the upgrade results in the failure to create any stored procedures
that have already been upgraded; consequently, the same error message
occurs.