can't execute sp_addextendedproperty

Question

can't execute sp_addextendedproperty

quasar_phoenicis

SSCommitted

Points: 1625
More actions
December 14, 2010 at 9:01 pm

#242405

hi,
I have an SQL script that executes sp_addextendedproperty but i get this error:
Object is invalid. Extended properties are not permitted on 'TableName', or the object does not exist.
Where can I change the permission that will allow me to execute this sp?
thanks!

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Mike John SSCertifiable Points: 7186 More actions · Answer 1

Does the error actiually say the word Tablename in the message or are you using that as a placeholder?

It sounds like the table you are naming does not exist. Can you post the complete call to sp_addesxtended_property you ar emaking.

Mike

VSSGeorge SSCrazy Eights Points: 8146 More actions · Answer 2

Hi

Prima facie, it seems that ur database object, ie. table does not exist in the DB.

quasar_phoenicis SSCommitted Points: 1625 More actions · Answer 3

Hi,

Sorry - here's the script:

declare @CurrentUser sysname

select @CurrentUser = user_name()

execute sys.sp_addextendedproperty 'MS_Description',

'Test Description',

'user', @CurrentUser, 'table', '[DWStage].[Sales]'

and here's the error message:

Object is invalid. Extended properties are not permitted on 'DWUser.[DWStage].[Sales]', or the object does not exist.

I have verified already that the [DWStage].[Sales] table is existing in the same database I am running the query above. I have set the user DWUser to DB Owner already but still it raises an error.

Mike John SSCertifiable Points: 7186 More actions · Answer 4

I think the problem lies with the stringing together of the username (it is not needed at all) and the schema name and table names.

The schema name and table names go in separate parameters something like:

create table t1 (col1 integer) -- Just for an example of names

EXEC sys.sp_addextendedproperty @name=N'MS_Description',

@value=N'Some text',

@level0type=N'SCHEMA',

@level0name=N'dbo',

@level1type=N'TABLE',

@level1name=N't1'

Mike

quasar_phoenicis SSCommitted Points: 1625 More actions · Answer 5

quasar_phoenicis

SSCommitted

Points: 1625

December 20, 2010 at 12:02 am

#1264493

nice! that solved my problem. thanks a lot!