June 15, 2011 at 9:04 am
I have a curious error. When I try to move a table from one schema to another I get:
alter schema guest transfer dbo.[sample]
Msg 15530, Level 16, State 1, Line 1
The object with name "sample" already exists.
The table does not exist in the schema I'm trying to move it to as verified by:
select a.name as tablename, b.name as schemaname
, b.name + '.' + a.name as [schema.tablename]
from sys.tables a
join sys.schemas b on a.schema_id = b.schema_id
where a.name = 'sample'
tablename...schemaname...schema.tablename
sample........dbo...............dbo.sample
Yet my query returns that error above.:unsure: I can move it to other schemas within the same DB, and move it to the correct schema in other similar DB's. I have restarted SQL Service in hopes that a temp object existed somewhere with the same result. Any ideas?
There is an exception to every rule, except this one...
June 15, 2011 at 9:11 am
any possibility it's not a table, but a view or proc or a poorly named constraint or something that happens to be named 'sample'?
select
schema_name(a.schema_id) as SchemaName,
a.name as ObjName,
a.type
from sys.objects a
where a.name = 'sample'
Lowell
June 15, 2011 at 9:18 am
The query you posted returned 1 row verifying "dbo, sample, U". I've checked views, procedures, everything I can think of.
There is an exception to every rule, except this one...
June 15, 2011 at 9:24 am
I've even tried to move the object that it's saying is there to a different schema and I get:
Msg 15151, Level 16, State 1, Line 1
Cannot find the object 'sample', because it does not exist or you do not have permission.
There is an exception to every rule, except this one...
June 15, 2011 at 1:26 pm
It appears to have been a FK issue...the error msg totally threw me off.
There is an exception to every rule, except this one...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply