BUG: Use of the sp_rename Stored Procedure to Change the Case of a Column Name Fails (303712)
The information in this article applies to:
- Microsoft SQL Server 2000 (all editions)
- Microsoft SQL Server 7.0
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.
| Modification Type: | Major | Last Reviewed: | 10/17/2003 |
|---|
| Keywords: | kbBug kbpending KB303712 |
|---|
|