IF OBJECT_ID('auditlog.ErrorLog') IS NOT NULL
  DROP TABLE [auditlog].[ErrorLog];
GO

CREATE TABLE [auditlog].[ErrorLog](
	[ErrorLogID] [int] IDENTITY(1,1) 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](4000) NOT NULL,
  CONSTRAINT [PK_ErrorLog_ErrorLogID] PRIMARY KEY CLUSTERED ([ErrorLogID] ASC)
)
GO

ALTER TABLE [auditlog].[ErrorLog] 
  ADD CONSTRAINT [DF_ErrorLog_ErrorTime] 
  DEFAULT (getdate()) FOR [ErrorTime]
GO

IF OBJECT_ID('auditlog.uspLogError') IS NOT NULL
  DROP PROCEDURE [auditlog].[uspLogError];
GO

CREATE PROCEDURE [auditlog].[uspLogError] 
  @ErrorLogID int = 0 OUTPUT -- contains the ErrorLogID of the row inserted
AS                               -- by uspLogError in the ErrorLog table
BEGIN
  SET NOCOUNT ON;

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

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

    -- Return if inside an uncommittable transaction.
    -- Data insertion/modification is not allowed when 
    -- a transaction is in an uncommittable state.
    IF XACT_STATE() = -1 BEGIN
      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

    INSERT [auditlog].[ErrorLog] (
      [UserName], 
      [ErrorNumber], 
      [ErrorSeverity], 
      [ErrorState], 
      [ErrorProcedure], 
      [ErrorLine], 
      [ErrorMessage]
    ) VALUES (
      CONVERT(sysname, CURRENT_USER), 
      ERROR_NUMBER(),
      ERROR_SEVERITY(),
      ERROR_STATE(),
      ERROR_PROCEDURE(),
      ERROR_LINE(),
      ERROR_MESSAGE()
    );

      -- Pass back the ErrorLogID of the row inserted
    SET @ErrorLogID = @@IDENTITY;
  END TRY
  BEGIN CATCH
    PRINT 'An error occurred in stored procedure uspLogError: ';
    RAISERROR('Error during logging an error by uspLogError', 16, 1) WITH LOG;
    RETURN -1;
  END CATCH
END;
GO

IF OBJECT_ID('auditlog.AuditServiceFailedItems') IS NOT NULL
    DROP TABLE [auditlog].[AuditServiceFailedItems];
GO

CREATE TABLE [auditlog].[AuditServiceFailedItems] (
  [conversation_handle] uniqueidentifier,
	[message_body] nvarchar(max),
	[EventDate] datetime DEFAULT CURRENT_TIMESTAMP
);
GO