Microsoft KB Archive/279153

= INF: Upgrade Wizard Does Not Transfer Existing Stored Procedures =

Article ID: 279153

Article Last Modified on 7/27/2006

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q279153



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 &quot;Differences&quot; 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.



MORE INFORMATION
You can use any of the following methods if you encounter the error message described in the &quot;Summary&quot; section:
 * 1) After you complete the Upgrade Wizard, drop the stored procedures that are displayed in the SERVERNAME.DBNAME.prc file and run the SERVERNAME.DBNAME.prc file on the later version (SQL Server 7.0 or SQL Server 2000) database to re-create those objects. This ensures that any modifications to the stored procedures on the SQL Server 6.5 user database are reflected in the upgraded database.
 * 2) Drop all the user defined stored procedures from the model database. Drop the upgraded user database, and then rerun the Upgrade Wizard.
 * 3) As a preemptive measure, do not upgrade the model database until after you upgrade all the user databases.

For more information, visit the following Microsoft Web site:

How to Upgrade SQL Server 6.5 and 7.0 to SQL Server 2000

http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx

Additional query words: Upgrade Differences Report .prc

Keywords: kbinfo KB279153

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.