Old UA Migration SQL - basis for an event logger DB

USE [master]
GO
/****** Object:  Database [UAMigration]    Script Date: 7/14/2020 3:36:06 PM ******/
CREATE DATABASE [UAMigration]
 CONTAINMENT = NONE
 ON  PRIMARY
( NAME = N'UAMigration_Data', FILENAME = N'c:\program files\microsoft sql server\mssql15.mssqlserver\mssql\data\UAMigration_Data.mdf' , SIZE = 10240KB , MAXSIZE = UNLIMITED, FILEGROWTH = 65536KB )
 LOG ON
( NAME = N'UAMigration_Log', FILENAME = N'c:\program files\microsoft sql server\mssql15.mssqlserver\mssql\data\UAMigration_Log.ldf' , SIZE = 8192KB , MAXSIZE = 2048GB , FILEGROWTH = 65536KB )
 WITH CATALOG_COLLATION = DATABASE_DEFAULT
GO
ALTER DATABASE [UAMigration] SET COMPATIBILITY_LEVEL = 150
GO
IF (1 = FULLTEXTSERVICEPROPERTY('IsFullTextInstalled'))
begin
EXEC [UAMigration].[dbo].[sp_fulltext_database] @action = 'enable'
end
GO
ALTER DATABASE [UAMigration] SET ANSI_NULL_DEFAULT OFF
GO
ALTER DATABASE [UAMigration] SET ANSI_NULLS OFF
GO
ALTER DATABASE [UAMigration] SET ANSI_PADDING OFF
GO
ALTER DATABASE [UAMigration] SET ANSI_WARNINGS OFF
GO
ALTER DATABASE [UAMigration] SET ARITHABORT OFF
GO
ALTER DATABASE [UAMigration] SET AUTO_CLOSE OFF
GO
ALTER DATABASE [UAMigration] SET AUTO_SHRINK OFF
GO
ALTER DATABASE [UAMigration] SET AUTO_UPDATE_STATISTICS ON
GO
ALTER DATABASE [UAMigration] SET CURSOR_CLOSE_ON_COMMIT OFF
GO
ALTER DATABASE [UAMigration] SET CURSOR_DEFAULT  GLOBAL
GO
ALTER DATABASE [UAMigration] SET CONCAT_NULL_YIELDS_NULL OFF
GO
ALTER DATABASE [UAMigration] SET NUMERIC_ROUNDABORT OFF
GO
ALTER DATABASE [UAMigration] SET QUOTED_IDENTIFIER OFF
GO
ALTER DATABASE [UAMigration] SET RECURSIVE_TRIGGERS OFF
GO
ALTER DATABASE [UAMigration] SET  ENABLE_BROKER
GO
ALTER DATABASE [UAMigration] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
GO
ALTER DATABASE [UAMigration] SET DATE_CORRELATION_OPTIMIZATION OFF
GO
ALTER DATABASE [UAMigration] SET TRUSTWORTHY OFF
GO
ALTER DATABASE [UAMigration] SET ALLOW_SNAPSHOT_ISOLATION OFF
GO
ALTER DATABASE [UAMigration] SET PARAMETERIZATION SIMPLE
GO
ALTER DATABASE [UAMigration] SET READ_COMMITTED_SNAPSHOT OFF
GO
ALTER DATABASE [UAMigration] SET HONOR_BROKER_PRIORITY OFF
GO
ALTER DATABASE [UAMigration] SET RECOVERY FULL
GO
ALTER DATABASE [UAMigration] SET  MULTI_USER
GO
ALTER DATABASE [UAMigration] SET PAGE_VERIFY CHECKSUM 
GO
ALTER DATABASE [UAMigration] SET DB_CHAINING OFF
GO
ALTER DATABASE [UAMigration] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO
ALTER DATABASE [UAMigration] SET TARGET_RECOVERY_TIME = 60 SECONDS
GO
ALTER DATABASE [UAMigration] SET DELAYED_DURABILITY = DISABLED
GO
EXEC sys.sp_db_vardecimal_storage_format N'UAMigration', N'ON'
GO
ALTER DATABASE [UAMigration] SET QUERY_STORE = OFF
GO
USE [UAMigration]
GO
/****** Object:  User [NT AUTHORITY\NETWORK SERVICE]    Script Date: 7/14/2020 3:36:07 PM ******/
CREATE USER [NT AUTHORITY\NETWORK SERVICE] FOR LOGIN [NT AUTHORITY\NETWORK SERVICE] WITH DEFAULT_SCHEMA=[dbo]
GO
/****** Object:  UserDefinedFunction [dbo].[fn_CheckMaxDayInactivity]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE FUNCTION  [dbo].[fn_CheckMaxDayInactivity]
(
@MaxDayInactivity int,
@LoginTime DateTime
)
RETURNS  bit
AS
BEGIN
DECLARE @RetValue bit


IF @MaxDayInactivity IS NULL
BEGIN
SET @RetValue = 1
END
ELSE
BEGIN
IF @LoginTime IS NULL
BEGIN
SET @RetValue = 0
END
ELSE
BEGIN
IF @MaxDayInactivity - DATEDIFF(dd,@LoginTime,GETDATE()) > 0
SET @RetValue = 1
ELSE
SET @RetValue = 0
END
END

RETURN @RetValue
END
GO
/****** Object:  Table [dbo].[Common_ErrorLog]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Common_ErrorLog](
[ErrorLogID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[ErrorTime] [datetime] NOT NULL,
[UserName] [sysname] NOT NULL,
[ErrorNumber] [int] NOT NULL,
[ErrorSeverity] [int] NULL,
[ErrorState] [int] NULL,
[ErrorProcedure] [nvarchar](126) NULL,
[ErrorLine] [int] NULL,
[ErrorMessage] [nvarchar](max) NOT NULL,
[ServerName] [varchar](50) NULL,
[ErrorAdditionalInfo] [nvarchar](max) NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Migration_Events]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Migration_Events](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](256) NULL,
[Description] [varchar](max) NULL,
[EventTypeID] [tinyint] NULL,
[CreateTimeUTC] [datetime] NULL,
[CreateTime]  AS (dateadd(hour,datediff(hour,getutcdate(),getdate()),[CreateTimeUTC]))
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Migration_EventsHistory]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Migration_EventsHistory](
[ID] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Migration_EventID] [int] NULL,
[Migration_SessionID] [int] NULL,
[Migration_EventSourceID] [tinyint] NULL,
[Migration_EventStepID] [tinyint] NULL,
[CreateTimeUTC] [datetime] NULL,
[CreateTime]  AS (dateadd(hour,datediff(hour,getutcdate(),getdate()),[CreateTimeUTC]))
) ON [PRIMARY]
GO
/****** Object:  Table [dbo].[Migration_Steps]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Migration_Steps](
[ID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[Name] [varchar](50) NULL,
[Description] [varchar](256) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Common_ErrorLog] ADD  CONSTRAINT [DF__Common_Er__Error__2A4B4B5E]  DEFAULT (getdate()) FOR [ErrorTime]
GO
ALTER TABLE [dbo].[Migration_Events] ADD  CONSTRAINT [DF__Migration__Creat__164452B1]  DEFAULT (getutcdate()) FOR [CreateTimeUTC]
GO
ALTER TABLE [dbo].[Migration_EventsHistory] ADD  CONSTRAINT [DF__Migration__Creat__108B795B]  DEFAULT (getutcdate()) FOR [CreateTimeUTC]
GO
/****** Object:  StoredProcedure [dbo].[sp_Common_LogError]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- uspLogError logs error information in the ErrorLog table about the
-- error that caused execution to jump to the CATCH block of a
-- TRY...CATCH construct. This should be executed from within the scope
-- of a CATCH block otherwise it will return without inserting error
-- information.
CREATE PROCEDURE [dbo].[sp_Common_LogError]
@Retry INT = 0 OUTPUT,
@MaxRetries int = 3 OUTPUT,
    @ErrorLogID [int] = 0 OUTPUT,-- contains the ErrorLogID of the row inserted
@ErrorAdditionalInfo nvarchar(MAX) = NULL
AS
-- by uspLogError in the ErrorLog table
BEGIN
    SET NOCOUNT ON;

DECLARE @ErrorMessage NVARCHAR(MAX);
    DECLARE @ErrorSeverity INT;
    DECLARE @ErrorState INT;

    -- Output parameter value of 0 indicates that error
    -- information was not logged
    SET @ErrorLogID = 0;

SET @Retry = @Retry + 1

SET @MaxRetries = 10

/*
    -- Return if there is no error information to log
    IF ERROR_NUMBER() IS NULL
RETURN;
*/

    BEGIN TRY

        -- Return if inside an uncommittable transaction.
        -- Data insertion/modification is not allowed when
        -- a transaction is in an uncommittable state.
--        IF XACT_STATE() = -1
        IF XACT_STATE() <> 0
        BEGIN
ROLLBACK TRANSACTION

/*
            PRINT 'Cannot log error since the current transaction is in an uncommittable state. '
                + 'Rollback the transaction before executing uspLogError in order to successfully log error information.';
            RETURN;
*/
        END

-- Return if there is no error information to log
IF ERROR_NUMBER() IS NULL
RETURN;

SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE()


        INSERT [dbo].[Common_ErrorLog]
(
            [UserName],
            [ErrorNumber],
            [ErrorSeverity],
            [ErrorState],
            [ErrorProcedure],
            [ErrorLine],
            [ErrorMessage],
[ErrorAdditionalInfo],
[ServerName]
)
        VALUES
(
            CONVERT(sysname, CURRENT_USER),
            ERROR_NUMBER(),
            @ErrorSeverity,
            @ErrorState,
            ERROR_PROCEDURE(),
            ERROR_LINE(),
            @ErrorMessage,
@ErrorAdditionalInfo,
HOST_NAME ()
);

        -- Pass back the ErrorLogID of the row inserted
        SET @ErrorLogID = SCOPE_IDENTITY()

    END TRY

    BEGIN CATCH
        PRINT 'An error occurred in stored procedure uspLogError: ';
        EXECUTE [dbo].[sp_Common_PrintError];
  --      RETURN -1;
    END CATCH

-- SELECT 'Retry = ' + CONVERT(varchar,@Retry) + ', MaxNumberRetries = ' + CONVERT(varchar,@MaxRetries)

/* Error Number:
1222 - Lock timeout
1205 - Deadlock
3960 - Update Conflict
*/

IF ERROR_NUMBER() IN (1222,1205,3960)
BEGIN

IF @Retry <= @MaxRetries
-- We will try it again few times
WAITFOR DELAY '00:00:02'
ELSE
RAISERROR (@ErrorMessage, @ErrorSeverity, @ErrorState);
END
ELSE
BEGIN
-- We will try it again few times, but less then in case of locks
WAITFOR DELAY '00:00:05'
SET @MaxRetries = 3
END

END;
GO
/****** Object:  StoredProcedure [dbo].[sp_Common_PrintError]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

-- uspPrintError prints error information about the error that caused
-- execution to jump to the CATCH block of a TRY...CATCH construct.
-- Should be executed from within the scope of a CATCH block otherwise
-- it will return without printing any error information.
CREATE PROCEDURE [dbo].[sp_Common_PrintError]
AS
BEGIN
    SET NOCOUNT ON;

    -- Print error information.
    PRINT 'Error ' + CONVERT(varchar(50), ERROR_NUMBER()) +
          ', Severity ' + CONVERT(varchar(5), ERROR_SEVERITY()) +
          ', State ' + CONVERT(varchar(5), ERROR_STATE()) +
          ', Procedure ' + ISNULL(ERROR_PROCEDURE(), '-') +
          ', Line ' + CONVERT(varchar(5), ERROR_LINE());
    PRINT ERROR_MESSAGE();
END;



GO
/****** Object:  StoredProcedure [dbo].[sp_Migration_InsertEvent]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Migration_InsertEvent]
(
@Event varchar(256),
@EventTypeID tinyint = 1, -- Information
@ID int = NULL OUTPUT
)
AS
BEGIN

IF @Event IS NULL
RETURN

SET NOCOUNT ON
SET LOCK_TIMEOUT 1800


DECLARE @Retry int
DECLARE @MaxRetries int
DECLARE @ErrorAdditionalInfo nvarchar(max)


SET @ErrorAdditionalInfo = 'Event = ' + @Event

SELECT @Retry = 0, @MaxRetries = 0

WHILE  @Retry <= @MaxRetries
BEGIN

SET @ID = NULL

BEGIN TRY


BEGIN TRANSACTION

SELECT @ID = ID
FROM Migration_Events
WHERE [Name] = @Event


IF @ID IS NULL
BEGIN
INSERT INTO Migration_Events([Name],[EventTypeID])
VALUES (@Event,@EventTypeID)

SET @ID = SCOPE_IDENTITY()
END

COMMIT
BREAK

END TRY
BEGIN CATCH

SET @ID = NULL

EXECUTE [dbo].[sp_Common_LogError]
@Retry = @Retry OUTPUT,
@MaxRetries = @MaxRetries OUTPUT,
@ErrorAdditionalInfo = @ErrorAdditionalInfo

END CATCH;
END

END

GO
/****** Object:  StoredProcedure [dbo].[sp_Migration_InsertEventHistory]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[sp_Migration_InsertEventHistory]
(
@SessionID int = NULL,
@Event varchar(256),
@EventTypeID tinyint = 1, -- Information
@EventSourceID tinyint = 1, -- Client
-- @EventStepID tinyint = NULL,
@Step varchar(50),
@ID bigint = NULL OUTPUT,
@IsTraceable bit = 0,
@NoOutput bit = 1
)
AS
BEGIN
SET NOCOUNT ON
SET LOCK_TIMEOUT 1800

DECLARE @Retry int
DECLARE @MaxRetries int
DECLARE @ErrorAdditionalInfo nvarchar(max)

DECLARE @EventStepID int
DECLARE @EventID int

IF @IsTraceable = 1
BEGIN

EXEC sp_Migration_InsertStep
@Name = @Step,
@ID = @EventStepID OUTPUT

EXEC sp_Migration_InsertEvent
@Event = @Event,
@EventTypeID = @EventTypeID,
@ID = @EventID OUTPUT


SET @ErrorAdditionalInfo = 'Event = ' + @Event + ',SessionID = ' +
CONVERT(varchar(max),ISNULL(@SessionID,0))


SELECT @Retry = 0, @MaxRetries = 0

WHILE  @Retry <= @MaxRetries AND @SessionID IS NOT NULL
BEGIN

SET @ID = NULL

BEGIN TRY

INSERT INTO Migration_EventsHistory
(
[Migration_EventID],
[Migration_SessionID],
[Migration_EventSourceID],
[Migration_EventStepID]
)
VALUES
(
@EventID,
@SessionID,
@EventSourceID,
@EventStepID
)

SET @ID = SCOPE_IDENTITY()
BREAK
END TRY
BEGIN CATCH

SET @ID = NULL

EXECUTE [dbo].[sp_Common_LogError]
@Retry = @Retry OUTPUT,
@MaxRetries = @MaxRetries OUTPUT,
@ErrorAdditionalInfo = @ErrorAdditionalInfo

END CATCH;
END
END

IF @NoOutput = 0
BEGIN
SELECT
@ID AS EventHistoryID,
@SessionID AS SessionID,
@EventStepID AS EventStepID

END

END
GO
/****** Object:  StoredProcedure [dbo].[sp_Migration_InsertStep]    Script Date: 7/14/2020 3:36:07 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[sp_Migration_InsertStep]
(
@Name varchar(256),
@ID int = NULL OUTPUT
)
AS
BEGIN
SET NOCOUNT ON
SET LOCK_TIMEOUT 1800


DECLARE @Retry int
DECLARE @MaxRetries int
DECLARE @ErrorAdditionalInfo nvarchar(max)


SET @ErrorAdditionalInfo = 'Name = ' + @Name

SELECT @Retry = 0, @MaxRetries = 0

WHILE  @Retry <= @MaxRetries
BEGIN

SET @ID = NULL

BEGIN TRY


BEGIN TRANSACTION

SELECT @ID = ID
FROM Migration_Steps
WHERE [Name] = @Name


IF @ID IS NULL
BEGIN
INSERT INTO Migration_Steps([Name])
VALUES (@Name)

SET @ID = SCOPE_IDENTITY()
END

COMMIT
BREAK

END TRY
BEGIN CATCH

SET @ID = NULL

EXECUTE [dbo].[sp_Common_LogError]
@Retry = @Retry OUTPUT,
@MaxRetries = @MaxRetries OUTPUT,
@ErrorAdditionalInfo = @ErrorAdditionalInfo

END CATCH;
END

END

GO
USE [master]
GO
ALTER DATABASE [UAMigration] SET  READ_WRITE
GO