USE [EDWAdmin]; GO GO ALTER DATABASE [EDWAdmin] SET SINGLE_USER WITH ROLLBACK IMMEDIATE GO -- If we need to modify this proc, do it in here as an ALTER IF EXISTS PRINT('Create stored procedure [Admin].[PrintErrorAndThrowException]'); IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[Admin].[PrintErrorAndThrowException]') AND type IN ( N'P', N'PC' )) BEGIN EXEC(' CREATE PROCEDURE [Admin].PrintErrorAndThrowException AS DECLARE @ExceptionMessage NVARCHAR(1000); -- THE VALID MAX LENGTH FOR EXCEPTION MESSAGE IS 1000 DECLARE @ErrorMessage NVARCHAR(1000) DECLARE @ErrorNumber VARCHAR(16); DECLARE @ErrorLineNumber VARCHAR(6); SET @ErrorNumber = CAST(ERROR_NUMBER() AS VARCHAR(16)); SET @ErrorLineNumber = CAST(ERROR_LINE() AS VARCHAR(6)); SET @ErrorMessage = ERROR_MESSAGE(); SET @ExceptionMessage = ''Error upgrading Metadata Database - Error#:'' + COALESCE(@ErrorNumber,'''') + '' Line#:'' + COALESCE(@ErrorLineNumber,'''') + '' Message:'' + COALESCE(@ErrorMessage,''''); PRINT(@ExceptionMessage); THROW 52416, @ExceptionMessage, 1;'); END -- If we need to modify this proc, do it in here as an ALTER IF EXISTS PRINT('Create function [Admin].[IsLessThanOrEqualToCurrentVersion]'); DECLARE @ProcedureScriptTypeLess VARCHAR(10) = 'ALTER '; DECLARE @ProcedureBodyTextLess VARCHAR(4000) = ''; IF NOT EXISTS(SELECT 1 FROM sys.objects WHERE object_id = OBJECT_ID(N'[Admin].[IsLessThanOrEqualToCurrentVersion]') AND type IN ( N'FN', N'IF', N'TF', N'FS', N'FT' )) BEGIN SET @ProcedureScriptTypeLess = 'CREATE '; END; BEGIN TRY BEGIN TRANSACTION -- SCRIPT SET @ProcedureBodyTextLess = @ProcedureScriptTypeLess +'FUNCTION [Admin].IsLessThanOrEqualToCurrentVersion ( @ApplicationNM VARCHAR(255) ,@VersionTXT VARCHAR(40) ) RETURNS BIT BEGIN DECLARE @Result BIT = 1 -- Contents removed for brevity RETURN @Result; END;'; EXEC(@ProcedureBodyTextLess); COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE() <> 0) ROLLBACK TRANSACTION; EXECUTE [Admin].PrintErrorAndThrowException; END CATCH; -- END 3.3.60.20 SCRIPT -- /**************************************************************************************************/ -- 3.3.60.21 SCRIPT PRINT('Drop primary key constraint on ETLTableHistoryBASE'); /**************************************************************************************************/ IF ([Admin].IsLessThanOrEqualToCurrentVersion('Metadata', '3.3.60.21') = 0) BEGIN BEGIN TRY BEGIN TRANSACTION -- SCRIPT ALTER TABLE [Admin].ETLTableHistoryBASE DROP CONSTRAINT pkETLTableHistoryBASE -- UPDATE VERSION EXECUTE [Admin].SetVersion 'Metadata', 3, 3, 60, 21; COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE() <> 0) ROLLBACK TRANSACTION; EXECUTE [Admin].PrintErrorAndThrowException; END CATCH; END GO -- END 3.3.60.21 SCRIPT -- /**************************************************************************************************/ -- 3.3.60.22 SCRIPT PRINT('Add primary key column, constraint and index to ETLTableHistoryBASE'); /**************************************************************************************************/ IF ([Admin].IsLessThanOrEqualToCurrentVersion('Metadata', '3.3.60.22') = 0) BEGIN BEGIN TRY BEGIN TRANSACTION -- SCRIPT ALTER TABLE [Admin].ETLTableHistoryBASE ADD TableHistoryID INT IDENTITY(1,1) NOT NULL ALTER TABLE [Admin].ETLTableHistoryBASE ADD CONSTRAINT pkETLTableHistoryBASE PRIMARY KEY CLUSTERED(TableHistoryID ASC) CREATE NONCLUSTERED INDEX [ixEtlTableHistoryBASEComposite1] ON [Admin].[ETLTableHistoryBASE] (BatchID, TableID) ON [HCEDWAdminIndex1]; -- UPDATE VERSION EXECUTE [Admin].SetVersion 'Metadata', 3, 3, 60, 22; COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE() <> 0) ROLLBACK TRANSACTION; EXECUTE [Admin].PrintErrorAndThrowException; END CATCH; END GO -- END 3.3.60.22 SCRIPT -- /**************************************************************************************************/ -- 3.3.60.26 SCRIPT PRINT('Add new column TableHistoryID in ETLLogBASE'); /**************************************************************************************************/ IF ([Admin].IsLessThanOrEqualToCurrentVersion('Metadata', '3.3.60.26') = 0) BEGIN BEGIN TRY BEGIN TRANSACTION -- SCRIPT ALTER TABLE [Admin].ETLLogBASE ADD TableHistoryID INT NULL -- UPDATE VERSION EXECUTE [Admin].SetVersion 'Metadata', 3, 3, 60, 26; COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE() <> 0) ROLLBACK TRANSACTION; EXECUTE [Admin].PrintErrorAndThrowException; END CATCH; END GO -- END 3.3.60.26 SCRIPT -- /**************************************************************************************************/ -- 3.3.61.1 SCRIPT PRINT('Populate TableHistoryID in [Admin].ETLLogBASE'); /**************************************************************************************************/ IF ([Admin].IsLessThanOrEqualToCurrentVersion('Metadata', '3.3.61.1') = 0) BEGIN BEGIN TRY BEGIN TRANSACTION -- SCRIPT UPDATE [Admin].ETLLogBASE SET TableHistoryID = histories.TableHistoryID FROM [Admin].ETLLogBASE logs JOIN [Admin].ETLTableHistoryBASE histories ON logs.BatchID = histories.BatchID AND logs.TableID = histories.TableID; -- UPDATE VERSION EXECUTE [Admin].SetVersion 'Metadata', 3, 3, 61, 1; COMMIT TRANSACTION; END TRY BEGIN CATCH IF (XACT_STATE() <> 0) ROLLBACK TRANSACTION; EXECUTE [Admin].PrintErrorAndThrowException; END CATCH; END GO -- END 3.3.61.1 SCRIPT -- GO ALTER DATABASE [EDWAdmin] SET MULTI_USER WITH ROLLBACK IMMEDIATE; GO