none
Object is invalid. Extended properties are not permitted on ...

    Question

  • when attempting to execute the following query from within 2005 Mgmt Studio:

    sp_dropextendedproperty

    @name = 'test_me'

    , @level0type = 'schema'

    , @level0name = 'dbHSPS.dbo'

    , @level1type = 'table'

    , @level1name = 'Conflict'

    /*[ , [ @level2type = 'column'] { 'level2_object_type' }

    , [ @level2name = ] { 'level2_object_name' }

    ] */

    I receive:

    "Object is invalid. Extended properties are not permitted on 'dbHSPS.dbo.Conflict', or the object does not exist."

     

    However I can SELECT * FROM dbHSPS.dbo.Conflict and it returns results, and I know the test_me extended property exists for table "Conflict" in dbHSPS.dbo ...

    Thursday, March 02, 2006 3:11 PM

Answers

  • The error is given because the @level0name parameter is incorrect - it uses a two part name for the database and schema instead of just containing the schema name.

    If you want to execute the sp_dropextendedproperty in another database than the current one, then you should prefix it like this: dbHSPS.dbo.sp_dropextendedproperty.

    Thanks
    Laurentiu

    Friday, March 03, 2006 1:06 AM

All replies

  • same for update ...

    sp_updateextendedproperty

    @name = 'test_me'

    , @value = 'test me 2'

    , @level0type = 'schema'

    , @level0name = 'dbHSPS.dbo'

    , @level1type = 'table'

    , @level1name = 'Conflict'

    Thursday, March 02, 2006 3:13 PM
  • The error is given because the @level0name parameter is incorrect - it uses a two part name for the database and schema instead of just containing the schema name.

    If you want to execute the sp_dropextendedproperty in another database than the current one, then you should prefix it like this: dbHSPS.dbo.sp_dropextendedproperty.

    Thanks
    Laurentiu

    Friday, March 03, 2006 1:06 AM
  • I ended up going the SMO route instead. It's a little bit slower, but the job gets done.
    Friday, March 03, 2006 2:24 PM