Tuesday, August 14, 2007

Stored Procedure to return a Policy to its editable state

When we publish or deploy a Policy during development, if we want to modificate the Policy we need to create a new version. With this stored procedure, we can unpublish/undeploy our Policy and modificate it:

CREATE PROCEDURE [dbo].[re_setpolicyeditable] (@policyName nvarchar(256),@nMajor bigint, @nMinor bigint)

AS

DECLARE @nRuleSetID AS BIGINT

SET @nRuleSetID = (SELECT nRuleSetID FROM dbo.re_ruleset WHERE strName = @policyName AND nMajor = @nMajor AND nMinor = @nMinor)

IF @nRuleSetID IS NULL BEGIN PRINT 'The Policy [' + @policyName + ' ' + CAST(@nMajor AS nvarchar(10)) + '.' + CAST(@nMinor AS nvarchar(10)) + '] not exists.' RETURN

END

UPDATE dbo.re_ruleset SET nStatus = 0 WHERE nRuleSetID = @nRuleSetID

DELETE FROM dbo.re_deployment_config WHERE nRuleSetID = @nRuleSetID

DELETE FROM dbo.re_tracking_id WHERE nRuleSetID = @nRuleSetID

INSERT INTO dbo.re_deployment_history VALUES (@policyName , 1, 0, 0, getdate())

GO

No comments: