Error EXEC sp_updateextendedproperty

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Error EXEC sp_updateextendedproperty

Author

Topic

bholmstrom
Yak Posting Veteran

76 Posts

Posted - 2013-03-14 : 16:28:15

I am trying to update my feild descriptions programatically, but I am getting an error.
------------------------------
use NCOS
GO

EXEC sp_updateextendedproperty
@name = N'MS_Description', @value = 'Your description',
@level0type = N'Schema', @level0name = 'NCOS',
@level1type = N'Table', @level1name = 'NCOS_Data',
@level2type = N'Column', @level2name = 'NC_Prospect_Name';

Error:

Server: Msg 15600, Level 15, State 1, Procedure sp_updateextendedproperty, Line 42
An invalid parameter or option was specified for procedure 'sp_updateextendedproperty'.

Bryan Holmstrom

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-18 : 02:37:37

This link may help you
http://forums.databasejournal.com/showthread.php?7799-Help-Extended-Properties

--
Chandu

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2013-03-18 : 10:50:50

sp_updateextendedproperty will barf if the property doesn't exist yet. in that case use sp_addextendedproperty

you can check for existence using fn_listextendedproperty - http://msdn.microsoft.com/en-us/library/ms179853.aspx

elsasoft.org

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2013-03-18 : 12:10:10

or you can do this to make it just one statement:


if exists (select * from ::fn_listextendedproperty ('MS_Description','SCHEMA','NCOS','TABLE','NCOS_Data','COLUMN','NC_Prospect_Name'))
begin
    exec sp_updateextendedproperty 
         @name='MS_Description'
        ,@value=N'Your description'
        ,@level0type='SCHEMA'
        ,@level0name='NCOS'
        ,@level1type='TABLE'
        ,@level1name='NCOS_Data'
        ,@level2type='COLUMN'
        ,@level2name='NC_Prospect_Name' 
end
else
begin
    exec sp_addextendedproperty 
         @name='MS_Description'
        ,@value=N'Your description'
        ,@level0type='SCHEMA'
        ,@level0name='NCOS'
        ,@level1type='TABLE'
        ,@level1name='NCOS_Data'
        ,@level2type='COLUMN'
        ,@level2name='NC_Prospect_Name' 
end;

elsasoft.org

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources