How to delete a specific Agreement Rule (script provided)

Version 3

    Environment:

     

    Service Desk - All Supported Versions

    SQL Database.

     

     

    Question:

     

    Is there a script which will delete a specific Agreement Rule and all its rules?

     

     

    Answer:

     

    You can delete your rules manually in the Administration Component in Service Desk. Once you have deleted all your rules in an Agreement, you will be able to delete the Agreement itself.

    If you have a lot of rules in an agreement then the suggested way aroundt his is to export your rules to an Excel spread sheet. Delete all rules but one in the spread sheet and then import the one rule back in.

    When you import rules in to an agreement in Service Desk you will automatically delete all rules before importing the new ones. So when you are finished with your import you will only have one rule in here. Delete this and tyou will then be able to delete your agreement rule.

     

    If you do not have Exel on your machine you can instead run a script to do this.

    Please note to always test this in a test database first and to always have a full back up of your database before you run any script in your live environment.

     

    Script:

    Please note to update the <<TITLE>> with the title of the Agreement.

     

     

    declare @Agreement nvarchar (64)
    declare @Agreement_Guid uniqueidentifier 
    
    set @Agreement = '<<TITLE>>'
    set @Agreement_Guid = (select lc_guid from lc_agreement where lc_title = @Agreement)
    
    
    delete from lc_agreement_group where lc_agreement_guid = @Agreement_Guid
    delete from lc_agreement_rule_value where lc_agreement_rule_guid in (select lc_guid from lc_agreement_rule where lc_agreement_guid = @Agreement_Guid)
    delete from lc_agreement_rule where lc_agreement_guid = @Agreement_Guid
    delete from lc_agreement where lc_guid = @Agreement_Guid