Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_syspolicy_execute_policy

  No additional text.


Syntax
CREATE PROCEDURE sys.sp_syspolicy_execute_policy
	@policy_name sysname,
	@event_data xml,
	@synchronous bit = 0
AS
BEGIN

	IF(DB_NAME() != 'msdb')
	BEGIN
		RAISERROR(14416,-1,-1)
		RETURN 14416
	END

	-- make sure that the caller is dbo or PolicyAdministratorRole
	IF ( IS_MEMBER('PolicyAdministratorRole') != 1 AND UPPER(USER_NAME())!= 'DBO')
	BEGIN
		RAISERROR(15003, -1, -1, 'PolicyAdministratorRole')
		RETURN 15003
	END

	DECLARE @retval int	
	DECLARE @history_id bigint
	SET @history_id = 0

	EXEC @retval = sys.sp_execute_policy @policy_name, @event_data, @history_id OUTPUT

	IF( (@synchronous = 1) AND (@retval=1))
	BEGIN

		-- the policy failed so we are going to abort the transaction

		-- make sure we get the log information so that we can recreate
		-- the log entry
		DECLARE @policy_id int
		DECLARE @start_date datetime
		DECLARE @end_date datetime
		DECLARE @result bit
		
		SELECT @policy_id = policy_id,
				@start_date = start_date,
				@end_date = end_date,
				@result = result
		FROM msdb.dbo.syspolicy_policy_execution_history_internal
		WHERE history_id = @history_id
		
		DECLARE @target_query_expression nvarchar(max)
		DECLARE @execution_date datetime
		DECLARE @target_result bit
		DECLARE @result_detail nvarchar(max)

		-- there is only one target
		SELECT TOP 1 @target_query_expression = target_query_expression,
			   @execution_date = execution_date,
			   @target_result = result,
			   @result_detail = result_detail
		FROM msdb.dbo.syspolicy_policy_execution_history_details_internal
		WHERE history_id = @history_id

		ROLLBACK TRANSACTION

		IF( @policy_id IS NOT NULL)
		BEGIN
			SET IDENTITY_INSERT msdb.dbo.syspolicy_policy_execution_history_internal ON
			INSERT INTO msdb.dbo.syspolicy_policy_execution_history_internal(history_id, policy_id, start_date, end_date, result)
				VALUES (@history_id, @policy_id, @start_date, @end_date, @result)
			SET IDENTITY_INSERT msdb.dbo.syspolicy_policy_execution_history_internal OFF
			-- insert empty string because the expression is no longer valid
			-- once the transaction has been rolled back
			INSERT INTO msdb.dbo.syspolicy_policy_execution_history_details_internal (history_id, target_query_expression, target_query_expression_with_id, execution_date, result, result_detail)
				VALUES (@history_id, @target_query_expression, N'', @execution_date, @target_result, @result_detail)
		END
	END

	RETURN @retval
END

 
Last revision 2008RTM
News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash