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
GOCREATE SCHEMA OldSchema
GOCREATE TYPE OldSchema.MyUDT FROM VARCHAR(20);GOALTER 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