Friday, February 24, 2012

Getting error while executing sp_addextendedproperty in SQL 2005

I've read through existing threads on this topic but haven't found anything that matches my situation exactly.

I'm trying to update the MS_Description on every table in the database. A sample of the syntax is:

EXEC sp_addextendedproperty

@.name = 'MS_Description',

@.value = 'Persons email',

@.level0type = 'SCHEMA',

@.level0name = 'dbo',

@.level1type = 'TABLE',

@.level1name = 'email',

@.level2type = 'COLUMN',

@.level2name = 'cemail'

Fairly straightforward, right? I'm receiving that awful, vague error "Object is invalid. Extended properties are not permitted on 'dbo.email.cemail', or the object does not exist." I'm in the correct database and am logged in as 'sa'.

I need to update over 2,000 column names, so I really don't want to do this manually via SQL Server Management Studio. The only solutions I've seen so far include scripting out the tables (I have data in them already) and checking to make sure that the user has permissions to the table (again, I'm signed in as 'sa').

Does anyone have any idea how to get around this? Thanks in advance.

Please ignore! The column name was incorrect. I just started here and found out they use a naming convention of using the datatype in the column name on the list I received. So 'cemail' is really just 'email' and the 'c' represents a character data type. Sorry for the inconvenience. I found this by checking the sys.columns table.

No comments:

Post a Comment