USE master GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[CommandExecute] @DatabaseContext nvarchar(max), @Command nvarchar(max), @CommandType nvarchar(max), @Mode int, @Comment nvarchar(max) = NULL, @DatabaseName nvarchar(max) = NULL, @SchemaName nvarchar(max) = NULL, @ObjectName nvarchar(max) = NULL, @ObjectType nvarchar(max) = NULL, @IndexName nvarchar(max) = NULL, @IndexType int = NULL, @StatisticsName nvarchar(max) = NULL, @PartitionNumber int = NULL, @ExtendedInfo xml = NULL, @LockMessageSeverity int = 16, @LogToTable nvarchar(max), @Execute nvarchar(max) AS BEGIN ---------------------------------------------------------------------------------------------------- --// Source: https://ola.hallengren.com //-- --// License: https://ola.hallengren.com/license.html //-- --// GitHub: https://github.com/olahallengren/sql-server-maintenance-solution //-- --// Version: 2020-01-26 14:06:53 //-- ---------------------------------------------------------------------------------------------------- SET NOCOUNT ON DECLARE @StartMessage nvarchar(max) DECLARE @EndMessage nvarchar(max) DECLARE @ErrorMessage nvarchar(max) DECLARE @ErrorMessageOriginal nvarchar(max) DECLARE @Severity int DECLARE @Errors TABLE (ID int IDENTITY PRIMARY KEY, [Message] nvarchar(max) NOT NULL, Severity int NOT NULL, [State] int) DECLARE @CurrentMessage nvarchar(max) DECLARE @CurrentSeverity int DECLARE @CurrentState int DECLARE @sp_executesql nvarchar(max) = QUOTENAME(@DatabaseContext) + '.sys.sp_executesql' DECLARE @StartTime datetime2 DECLARE @EndTime datetime2 DECLARE @ID int DECLARE @Error int = 0 DECLARE @ReturnCode int = 0 DECLARE @EmptyLine nvarchar(max) = CHAR(9) ---------------------------------------------------------------------------------------------------- --// Check core requirements //-- ---------------------------------------------------------------------------------------------------- IF NOT (SELECT [compatibility_level] FROM sys.databases WHERE database_id = DB_ID()) >= 90 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The database ' + QUOTENAME(DB_NAME(DB_ID())) + ' has to be in compatibility level 90 or higher.', 16, 1 END IF NOT (SELECT uses_ansi_nulls FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'ANSI_NULLS has to be set to ON for the stored procedure.', 16, 1 END IF NOT (SELECT uses_quoted_identifier FROM sys.sql_modules WHERE [object_id] = @@PROCID) = 1 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'QUOTED_IDENTIFIER has to be set to ON for the stored procedure.', 16, 1 END IF @LogToTable = 'Y' AND NOT EXISTS (SELECT * FROM sys.objects objects INNER JOIN sys.schemas schemas ON objects.[schema_id] = schemas.[schema_id] WHERE objects.[type] = 'U' AND schemas.[name] = 'dbo' AND objects.[name] = 'CommandLog') BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The table CommandLog is missing. Download https://ola.hallengren.com/scripts/CommandLog.sql.', 16, 1 END ---------------------------------------------------------------------------------------------------- --// Check input parameters //-- ---------------------------------------------------------------------------------------------------- IF @DatabaseContext IS NULL OR NOT EXISTS (SELECT * FROM sys.databases WHERE name = @DatabaseContext) BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @DatabaseContext is not supported.', 16, 1 END IF @Command IS NULL OR @Command = '' BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Command is not supported.', 16, 1 END IF @CommandType IS NULL OR @CommandType = '' OR LEN(@CommandType) > 60 BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @CommandType is not supported.', 16, 1 END IF @Mode NOT IN(1,2) OR @Mode IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Mode is not supported.', 16, 1 END IF @LockMessageSeverity NOT IN(10,16) OR @LockMessageSeverity IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @LockMessageSeverity is not supported.', 16, 1 END IF @LogToTable NOT IN('Y','N') OR @LogToTable IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @LogToTable is not supported.', 16, 1 END IF @Execute NOT IN('Y','N') OR @Execute IS NULL BEGIN INSERT INTO @Errors ([Message], Severity, [State]) SELECT 'The value for the parameter @Execute is not supported.', 16, 1 END ---------------------------------------------------------------------------------------------------- --// Raise errors //-- ---------------------------------------------------------------------------------------------------- DECLARE ErrorCursor CURSOR FAST_FORWARD FOR SELECT [Message], Severity, [State] FROM @Errors ORDER BY [ID] ASC OPEN ErrorCursor FETCH ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState WHILE @@FETCH_STATUS = 0 BEGIN RAISERROR('%s', @CurrentSeverity, @CurrentState, @CurrentMessage) WITH NOWAIT RAISERROR(@EmptyLine, 10, 1) WITH NOWAIT FETCH NEXT FROM ErrorCursor INTO @CurrentMessage, @CurrentSeverity, @CurrentState END CLOSE ErrorCursor DEALLOCATE ErrorCursor IF EXISTS (SELECT * FROM @Errors WHERE Severity >= 16) BEGIN SET @ReturnCode = 50000 GOTO ReturnCode END ---------------------------------------------------------------------------------------------------- --// Log initial information //-- ---------------------------------------------------------------------------------------------------- SET @StartTime = SYSDATETIME() SET @StartMessage = 'Date and time: ' + CONVERT(nvarchar,@StartTime,120) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Database context: ' + QUOTENAME(@DatabaseContext) RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT SET @StartMessage = 'Command: ' + @Command RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT IF @Comment IS NOT NULL BEGIN SET @StartMessage = 'Comment: ' + @Comment RAISERROR('%s',10,1,@StartMessage) WITH NOWAIT END IF @LogToTable = 'Y' BEGIN INSERT INTO dbo.CommandLog (DatabaseName, SchemaName, ObjectName, ObjectType, IndexName, IndexType, StatisticsName, PartitionNumber, ExtendedInfo, CommandType, Command, StartTime) VALUES (@DatabaseName, @SchemaName, @ObjectName, @ObjectType, @IndexName, @IndexType, @StatisticsName, @PartitionNumber, @ExtendedInfo, @CommandType, @Command, @StartTime) END SET @ID = SCOPE_IDENTITY() ---------------------------------------------------------------------------------------------------- --// Execute command //-- ---------------------------------------------------------------------------------------------------- IF @Mode = 1 AND @Execute = 'Y' BEGIN EXECUTE @sp_executesql @stmt = @Command SET @Error = @@ERROR SET @ReturnCode = @Error END IF @Mode = 2 AND @Execute = 'Y' BEGIN BEGIN TRY EXECUTE @sp_executesql @stmt = @Command END TRY BEGIN CATCH SET @Error = ERROR_NUMBER() SET @ErrorMessageOriginal = ERROR_MESSAGE() SET @ErrorMessage = 'Msg ' + CAST(ERROR_NUMBER() AS nvarchar) + ', ' + ISNULL(ERROR_MESSAGE(),'') SET @Severity = CASE WHEN ERROR_NUMBER() IN(1205,1222) THEN @LockMessageSeverity ELSE 16 END RAISERROR('%s',@Severity,1,@ErrorMessage) WITH NOWAIT IF NOT (ERROR_NUMBER() IN(1205,1222) AND @LockMessageSeverity = 10) BEGIN SET @ReturnCode = ERROR_NUMBER() END END CATCH END ---------------------------------------------------------------------------------------------------- --// Log completing information //-- ---------------------------------------------------------------------------------------------------- SET @EndTime = SYSDATETIME() SET @EndMessage = 'Outcome: ' + CASE WHEN @Execute = 'N' THEN 'Not Executed' WHEN @Error = 0 THEN 'Succeeded' ELSE 'Failed' END RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT SET @EndMessage = 'Duration: ' + CASE WHEN (DATEDIFF(SECOND,@StartTime,@EndTime) / (24 * 3600)) > 0 THEN CAST((DATEDIFF(SECOND,@StartTime,@EndTime) / (24 * 3600)) AS nvarchar) + '.' ELSE '' END + CONVERT(nvarchar,DATEADD(SECOND,DATEDIFF(SECOND,@StartTime,@EndTime),'1900-01-01'),108) RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT SET @EndMessage = 'Date and time: ' + CONVERT(nvarchar,@EndTime,120) RAISERROR('%s',10,1,@EndMessage) WITH NOWAIT RAISERROR(@EmptyLine,10,1) WITH NOWAIT IF @LogToTable = 'Y' BEGIN UPDATE dbo.CommandLog SET EndTime = @EndTime, ErrorNumber = CASE WHEN @Execute = 'N' THEN NULL ELSE @Error END, ErrorMessage = @ErrorMessageOriginal WHERE ID = @ID END ReturnCode: IF @ReturnCode <> 0 BEGIN RETURN @ReturnCode END ---------------------------------------------------------------------------------------------------- END GO