Microsoft KB Archive/303712

= BUG: Use of the sp_rename Stored Procedure to Change the Case of a Column Name Fails =

Article ID: 303712

Article Last Modified on 10/17/2003

-

APPLIES TO


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

-



This article was previously published under Q303712



BUG #: 354713 (SHILOH) BUG #: 101851 (SQLBUG_70)



SYMPTOMS
On a case-insensitive server, you can use the sp_rename stored procedure to change the case of the name of any object except for a column. An attempt to rename a column in which you only change the case of the text will fail. For example: create table Test (Col1 int not null) go exec sp_rename 'Test.Col1','COL1','Column' The error message that occurs is:

Server: Msg 2705, Level 16, State 1, Procedure sp_rename, Line 441 Column names in each table must be unique. Column name '%.*ls' in table '%.*ls' is specified more than once. Caution: Changing any part of an object name could break scripts and stored procedures.



WORKAROUND
To work around this problem, rename the column to a different name, and then rename the column back to the name you want with the proper case. For example: create table Test (Col1 int not null) go exec sp_rename 'Test.Col1','Col1_Extra_Step','Column' exec sp_rename 'Test.Col1_Extra_Step','COL1','Column'



STATUS
Microsoft has confirmed this to be a problem in SQL Server 2000.



MORE INFORMATION
When the rename is successful the following 15477 message appears:

Caution: Changing any part of an object name could break scripts and stored procedures.

Additional query words: rename change case upper lower mixed

Keywords: kbbug kbpending KB303712

-

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

© Microsoft Corporation. All rights reserved.