2005 breaks sp_rename for constraints?

BooksOnline indicates that sp_rename can still be used to rename constraints such as primary keys, foreign keys, defaults, etc. in SQL Server 2005. However, when I try to do so I get this:

"Either the parameter @.objname is ambiguous or the claimed @.objtype (object) is wrong."

After looking at the code for sp_rename, it looks like this problem might be related to another bit of apparent misbehavior on 2005's part: when I execute "select object_name()" with the Id number of a constraint, it returns the name, but when I execute "select object_id()" with the name of that same constraint, it returns null. Why is that?

Thanks,

Ron

Did you specify the schema ? make sure that Object_id is only valid for the current context, the database you are currently connected with.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de|||

It worked for me on RTM Express Edition. Can you post the code you used/reproduce it? Here is my code:

create table test
(
testId int constraint pktest primary key
)
go
select object_id('pktest')
select object_name(object_id('pktest'))
go
/*
--
613577224

pktest
*/
go
sp_rename 'pktest','newPkTest'
go
select object_id('pktest')
select object_name(object_id('pktest'))

select object_id('newPkTest')
select object_name(object_id('newPktest'))
go
/*

--
NULL

--
NULL

--
613577224

--
newPkTest
*/

|||

Sorry, I thought I had deleted this one. Yes, I had not specified the schema of the constraint. Doing so helped a lot!

Thanks,

Ron