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 = 0
-- 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