SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Error EXEC sp_updateextendedproperty
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 03/14/2013 :  16:28:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2208 Posts

Posted - 03/18/2013 :  02:37:37  Show Profile  Reply with Quote
This link may help you
http://forums.databasejournal.com/showthread.php?7799-Help-Extended-Properties

--
Chandu
Go to Top of Page

jezemine
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 03/18/2013 :  10:50:50  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2886 Posts

Posted - 03/18/2013 :  12:10:10  Show Profile  Visit jezemine's Homepage  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000