Post reply

can't execute sp_addextendedproperty

  • 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!

  • December 15, 2010 at 3:03 am

    #1262714

    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

  • December 15, 2010 at 3:39 am

    #1262720

    Hi

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

  • December 16, 2010 at 7:24 pm

    #1263839

    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.

  • December 16, 2010 at 11:42 pm

    #1263909

    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

  • December 20, 2010 at 12:02 am

    #1264493

    nice! that solved my problem. thanks a lot!

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

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