help with update database

  • 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.

  • 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.

  • 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