How can I solve "Either the parameter @objname is ambiguous or the claimed @objtype (COLUMN) is wrong."?
SqlSql ServerSql Problem Overview
If I try to execute below query:
EXEC sp_rename 'ENG_TEst.[ENG_Test_A/C_TYPE]', 'ENG_Test_AC_TYPE', 'COLUMN'
I get an error:
> Either the parameter@objname is > ambiguous or the claimed @objtype > (COLUMN) is wrong.
How can I solve it?
Sql Solutions
Solution 1 - Sql
Nuts. I hit this same error weeks ago, and after a lot of wasted time figured out how to make it work--but I've since forgotten it. (Not much help, other than to say yes, it can be done.)
Have you tried different combinations of brackets, or of with and without brackest? e.g.
EXEC sp_rename 'ENG_TEst.ENG_Test_A/C_TYPE', 'ENG_Test_AC_TYPE', 'COLUMN';
EXEC sp_rename '[ENG_TEst].[ENG_Test_A/C_TYPE]', 'ENG_Test_AC_TYPE', 'COLUMN';
EXEC sp_rename '[ENG_TEst].[ENG_Test_A/C_TYPE]', '[ENG_Test_AC_TYPE]', 'COLUMN';
EXEC sp_rename '[ENG_TEst].ENG_Test_A/C_TYPE', 'ENG_Test_AC_TYPE', 'COLUMN';
If all else fails, there's always
- Create new table (as "xENG_TEst") with proper names
- Copy data over from old table
- Drop old table
- Rename new table to final name
Solution 2 - Sql
This works
EXEC sp_rename
@objname = 'ENG_TEst."[ENG_Test_A/C_TYPE]"',
@newname = 'ENG_Test_A/C_TYPE',
@objtype = 'COLUMN'
Solution 3 - Sql
Are you running the query in the correct database? i.e.,
Use MyDatabase;
GO
EXEC sp_rename 'ENG_TEst.[ENG_Test_A/C_TYPE]', 'ENG_Test_AC_TYPE', 'COLUMN';
GO
Solution 4 - Sql
I ran into this today and got it to work with:
EXECUTE sp_rename N'dbo.table_name.original_field_name', N'new_field_name', 'COLUMN'
To get this syntax, I followed Martin Smith's advice above - open up the table in design view, rename the column and then click table designer | generate change script. This produced the script below which does the renaming in two steps:
/* To prevent any potential data loss issues, you should review this script in
detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
EXECUTE sp_rename N'dbo.table_name.original_field_name', N'Tmp_new_field_name_1', COLUMN'
GO
EXECUTE sp_rename N'dbo.table_name.Tmp_new_field_name_1', N'new_field_name', 'COLUMN'
GO
ALTER TABLE dbo.table_name SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
Solution 5 - Sql
i also had this issue- very annoying and haven't found a satisfactory sql answer myself yet (aside from long-winded ones involving creating temp tables etc.) and i didn't have time to explore it to the conclusion i'd have liked.
In the end just used SQL Server Management Studio to do it by selecting the table, right-clicking on the column and hitting rename. simples!
obviously i'd rather know how to do it without a gui but sometimes you've just gotta get sh** done!
Solution 6 - Sql
Both of the following work (as discussed here).
exec sp_rename 'ENG_TEst.[[ENG_Test_A/C_TYPE]]]' ,
'ENG_Test_A/C_TYPE', 'COLUMN'
exec sp_rename 'ENG_TEst."[ENG_Test_A/C_TYPE]"' ,
'ENG_Test_A/C_TYPE', 'COLUMN'
Solution 7 - Sql
I got this error when Updating code first MVC5 database. Dropping (right click, delete) all the tables from my database and removing the migrations from the Migration folder worked for me.
Solution 8 - Sql
I tried every possible solution on this web site and nothing worked for me. I ended up doing in the design mode. Right click on the table name and then click design. Then I changed the name and saved here. Worked simply.
Solution 9 - Sql
just try to remove the STATISTICS INDEXES linked to this COLUMN.
Best Regards.