Microsoft KB Archive/243198

= PRB: Renaming Stored Procedure, View or Trigger Does Not Update SYSCOMMENTS Table =

Article ID: 243198

Article Last Modified on 11/14/2003

-

APPLIES TO


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

-



This article was previously published under Q243198



SYMPTOMS
Renaming a Stored Procedure, View or Trigger object will not change the name of the corresponding Stored Procedure, View or Trigger object in the SYSCOMMENTS table. This may cause problems when a script is generated for the renamed object. The script for the renamed object is generated with the old name of the object in the CREATE statement.

For example, you can rename a stored procedure from the Enterprise Manager or SQL Server Query Analyzer with these steps:
 * 1) From Enterprise Manager, expand the database folder and select Stored Procedures. Right-click the stored procedure and then select RENAME.
 * 2) From SQL Server Query Analyzer, select the stored procedure's database name in the database window and type in sp_rename 'old name','new name'.

A renamed Stored Procedure, View or Trigger is upgraded to SQL Server 7.0 with the old name of the object. The Upgrade Wizard moves SQL Server 6.x databases to SQL Server 7.0. An upgrade script is created based on the syscomments entries in the 6.x database. It is recommended that you drop and recreate objects that have been renamed prior to upgrading SQL Server to version 7.0. An example of the warning you receive during the SQL Server 6.5 to SQL Server 7.0 upgrade process follows:

============================================= The following syscomments entries are invalid on your 6.x SQL Server. It is recommended that you fix these problems before you continue.

=
================================ mydb Inconsistency Report For Database: mydb.

dbo.renamedmytest

Procedure

Object Renamed: Text does not match sysobjects.



CAUSE
Internally, SQL Server uses the object_id, which remains the same even if the name of the object has changed through RENAME.



WORKAROUND
Do not rename Stored Procedures, Views, or Triggers. Instead, create a script of the existing object, change the name of the object in the CREATE statement of the script, and save the script. The script should now have a DROP statement with the old object name to be dropped. The script should also have the CREATE statement with the new object name. Run the script to create the object with the new name.



MORE INFORMATION
The CREATE statement for a renamed Stored Procedure or View can be accessed by double-clicking the renamed Stored Procedure or View in Microsoft SQL Server Enterprise Manager. To generate a script of the Stored Procedure, View or Trigger, right-click the database where the renamed Stored Procedure, View or Trigger was created and select ALL TASKS\GENERATE SQL SCRIPTS. The generated SQL script will have a CREATE statement with the previous name of the Stored Procedure, View or Trigger.

For example, create a stored procedure as follows: CREATE PROCEDURE myrenametest AS select * from pubs..authors Now rename the stored procedure to "renamedmytest" by right-clicking the stored procedure and selecting RENAME or by using sp_rename 'myProc_OldName','myProc_NewName'.

Next, double-click the stored procedure "renamedmytest" and note that the old stored procedure name still appears in the CREATE PROCEDURE statement. CREATE PROCEDURE myrenametest AS select * from pubs..authors The stored procedure runs in a query window using the new name (exec renamedmytest). If you right-click the database where the renamed stored procedure was created and select ALL TASKS\GENERATE SQL SCRIPTS to generate a script of the renamed stored procedure, the CREATE PROCEDURE statement will have the old procedure name "myrenametest" in the CREATE PROCEDURE statement generated. For example: CREATE PROCEDURE myrenametest AS select * from pubs..authors

Keywords: kbprb KB243198

-

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

© Microsoft Corporation. All rights reserved.