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 2012 Forums
 Transact-SQL (2012)
 Extended Properties Code
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bholmstrom
Yak Posting Veteran

USA
71 Posts

Posted - 03/21/2013 :  10:55:39  Show Profile  Reply with Quote
Good morning, I have this little slice of code that I am trying to get to work. If the extended property already exists this code throws an error, I thought it was going to just update the property?

USE NCOS
GO

if exists (select * from ::fn_listextendedproperty ('MS_Description','SCHEMA','NCOS','TABLE','NCOS_Data','COLUMN','NC_LeadDate'))
begin
EXEC sp_updateextendedproperty
'MS_Description',
'Fred',
'SCHEMA',
'dbo',
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end
else
begin
EXEC sp_addextendedproperty
'MS_Description',
'Lead Date',
'SCHEMA',
'dbo',
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end

Msg 15233, Level 16, State 1, Procedure sp_addextendedproperty, Line 37
Property cannot be added. Property 'MS_Description' already exists for 'dbo.NCOS_Data.NC_LeadDate'.

Thanks in advance as always.

Bryan Holmstrom

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/21/2013 :  16:55:57  Show Profile  Reply with Quote
-- Try this... no idea if it will make any difference though
if exists (select * from fn_listextendedproperty (default, default, default, default, default, default, default) WHERE Name = 'MS_Description')

OR

Should 'NCOS' not be 'dbo'

Edited by - UnemployedInOz on 03/21/2013 17:05:45
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 03/22/2013 :  00:58:23  Show Profile  Reply with Quote
the schemas are different in checking and updation part. the if check part looks for NCOS_Data table in NCOS schema but you're trying to update extendedproperty of NCOS_Data table in dbo schema

try this


if exists (select * from ::fn_listextendedproperty ('MS_Description','SCHEMA','dbo','TABLE','NCOS_Data','COLUMN','NC_LeadDate'))
begin
EXEC sp_updateextendedproperty 
'MS_Description', 
'Fred', 
'SCHEMA',
'dbo', 
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end
else
begin
EXEC sp_addextendedproperty 
'MS_Description', 
'Lead Date', 
'SCHEMA',
'dbo', 
'table',
'NCOS_Data',
'COLUMN',
'NC_LeadDate'
end



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

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.2 seconds. Powered By: Snitz Forums 2000