June 8, 2010 at 10:19 am
hi, I have a error - property cant be updated or deleted.
property 'Domain' does not exist for 'object specified'.
this the new code :
CREATE PROCEDURE yyeess51 @domain nvarchar(100),@db_name nvarchar(100)
AS
declare @STR nvarchar(100)
declare @str1 nvarchar(100)
set @STR = 'USE '+@db_name+';'
EXEC sp_executesql @STR
if 'Domain' in (select name from sys.extended_properties)
set @str1 = 'USE '+@db_name+';EXEC sp_updateextendedproperty @name=''Domain'',@value='''+@domain+''';'
else set @str1 = 'USE '+@db_name+';EXEC sp_addextendedproperty @name=''Domain'',@value='''+@domain+''';'
EXEC sp_executesql @str1
thank's, you help me a lot.
June 8, 2010 at 10:33 am
I think the line beginning with "if" should read:
IF EXISTS (SELECT * FROM sys.extended_properties WHERE [name] = 'Domain')
By the way, please try to post in the correct forum next time - this isn't SQL Server 7 or 2000.
John
Edit: actually, looking at it again, I think it's because you're trying to update an extended property that doesn't exist. What are you trying to add the extended property to? Try putting a [font="Courier New"]PRINT @str1 [/font]command in your code so that you can see precisely what is being executed.
June 8, 2010 at 10:59 am
hi, thank's i try also your code.
finally, i sucess to fix iu with this code :
create PROCEDURE ywork @domain nvarchar(100),@db_name nvarchar(100)
AS
declare @STR nvarchar(1000)
set @STR = 'USE '+@db_name+';'
+' if ''Domain'' in (select name from sys.extended_properties)'
+' EXEC sp_updateextendedproperty @name=''Domain'',@value='''+@domain+''';'
+' else EXEC sp_addextendedproperty @name=''Domain'',@value='''+@domain+''';'
EXEC sp_executesql @STR
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply