http://msdn.microsoft.com/en-us/library/ms173423.aspx
Just out of interest, is it me or is this syntax for changing an objects schema a bit strange? Are you altering the Schema or the Object? Anyhow, I digress...
In our example, we have several User Defined Types which require transfer. Here is some sample code illustrating the problem:
CREATE SCHEMA NewSchema
GO
CREATE SCHEMA OldSchema
GO
CREATE TYPE OldSchema.MyUDT FROM VARCHAR(20);
GO
ALTER SCHEMA NewSchema TRANSFER OldSchema.MyUDT
GO
This gives the rather unhelpful error:
Cannot find the object 'MyUDT', because it does not exist or you do not have permission.
I checked the documentation and there isn't anything that suggests types should be treated any differently so I was stumped. Fortunately, I managed to unearth this post which provides a workaround to the problem.
ALTER SCHEMA NewSchema TRANSFER type::OldSchema.MyUDT
GO
I'm not sure why this is not documented in Books Online (or at least nowhere I can see). One to keep an eye out for.
No comments:
Post a Comment