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
Go to Top of Page

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
Go to Top of Page

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
Go to Top of Page
   

- Advertisement -