Contents x
    Data Exchange Settings
    • 26 Aug 2024
    • 3 Minutes to read
    • Dark
      Light
    Contents

    Data Exchange Settings

    • Updated on 26 Aug 2024
    • 3 Minutes to read
    • Dark
      Light
    Article summary

    General

    The data exchange functionality can be used to improve monitoring. When enabled, Mail & Deploy will write all execution information and logs into a database.

    Managing Data Exchange Settings

    To configure data exchange settings you have to connect to the Management Service of Mail & Deploy, click the Settings item in the main menu, and then click the Data Exchange tile.

    Properties

    You can set the following properties:

    Name

    		Description
    Datasource TypeThe type of database to write exchange data to.

    (None). If you select this option the data exchange feature is deactivated.

    Local SQLite Database. When you select this option, Mail & Deploy writes all execution logs into an  SQLite Database located in the Data\Exchange\Database.s3db file (see below for the definition of tables in this database). You can find ODBC drivers required to create ODBC connections to SQLite database in the Data\Exchange directory. The file sqliteodbc.exe installs 32bit ODBC drivers, the file sqliteodbc_w64.exe installs 64bit ODBC drivers.

    Microsoft SQL Server. If you select this option you can specify details of a Microsoft SQL Server database to which exchange data will be written (see below for the required tables in that database).

    MySQL. If you select this option you can specify details of a MySQL database to which exchange data will be written (see below for the required tables in that database).
    LanguageThe language in which log files will be written to the database.

    SQLite Database Structure

    Executions

    CREATE TABLE Executions (
    ExecutionId NVARCHAR (36) PRIMARY KEY,
    StartDateTimeUtc DATETIME,
    EndDateTimeUtc DATETIME,
    Result NVARCHAR (50),
    ExecutionType NVARCHAR (255),
    ExecutionTrigger NVARCHAR (255),
    WorkspaceName NVARCHAR (255),
    TaskName NVARCHAR (255),
    ReportName NVARCHAR (255),
    ExportDatasource NVARCHAR (255),
    ExportDatasourceObject NVARCHAR (255),
    SubscriptionType NVARCHAR (255),
    SubscriptionName NVARCHAR (255),
    SubscriptionUser NVARCHAR (255),
    AlertName NVARCHAR (255),
    AlertUser NVARCHAR (255),
    RequestedByUser NVARCHAR (255),
    NumberOfCreatedReportDocuments INTEGER,
    JobNumber INTEGER
    )

    ExecutionLogEntries

    CREATE TABLE ExecutionLogEntries (
    ExecutionLogEntryId NVARCHAR (36) PRIMARY KEY,
    ExecutionId NVARCHAR (36), "Order" INTEGER,
    DateTimeOfOccurrenceUtc DATETIME,
    Type NVARCHAR (50),
    Message TEXT,
    AdditionalInformation TEXT, StackTrace TEXT)

    EMailedReports

    CREATE TABLE EMailedReports (
    DateTimeUtc DATETIME,
    EMailAddress TEXT,
    Report TEXT,
    Task TEXT,
    Workspace TEXT,
    Subject TEXT
    )

    Microsoft SQL Server Database Structure

    If you select a Datasource Type of Microsoft SQL Server you need to make sure that the database specified contains the following three tables:

    Executions

    CREATE TABLE [dbo].[Executions](
    [ExecutionId] [nvarchar] (36) NOT NULL,
    [StartDateTimeUtc] [datetime] NULL,
    [EndDateTimeUtc] [datetime] NULL,
    [Result] [nvarchar] (50) NULL,
    [ExecutionType] [nvarchar] (255) NULL,
    [ExecutionTrigger] [nvarchar] (255) NULL,
    [WorkspaceName] [nvarchar] (255) NULL,
    [TaskName] [nvarchar] (255) NULL,
    [ReportName] [nvarchar] (255) NULL,
    [ExportDatasource] [nvarchar] (255) NULL,
    [ExportDatasourceObject] [nvarchar] (255) NULL,
    [SubscriptionType] [nvarchar] (255) NULL,
    [SubscriptionName] [nvarchar] (255) NULL,
    [SubscriptionUser] [nvarchar] (255) NULL,
    [AlertName] [nvarchar] (255) NULL,
    [AlertUser] [nvarchar] (255) NULL,
    [RequestedByUser] [nvarchar] (255) NULL,
    [NumberOfCreatedReportDocuments] [int] NULL,
    [JobNumber] [int] NULL)

    ExecutionLogEntries

    CREATE TABLE [dbo].[ExecutionLogEntries](
    [ExecutionLogEntryId] [nvarchar] (36) NOT NULL,
    [ExecutionId] [nvarchar] (36) NULL,
    [Order] [int] NULL,
    [DateTimeOfOccurrenceUtc] [datetime] NULL,
    [Type] [nvarchar] (50) NULL,
    [Message] [text] NULL,
    [AdditionalInformation] [text] NULL,
    [StackTrace] [text] NULL)

    EMailedReports

    CREATE TABLE [dbo].[EMailedReports] (
    [DateTimeUtc] [datetime] NULL,
    [EMailAddress] [nvarchar] (255) NULL,
    [Report] [nvarchar] (255) NULL,
    [Task] [nvarchar] (255) NULL,
    [Workspace] [nvarchar] (255) NULL,
    [Subject] [nvarchar] (255) NULL
    )

    MySQL Database Structure

    If you select a Datasource Type of MySQL you need to make sure that the database specified contains the following two tables:

    Executions

    CREATE TABLE `Executions` (
    `ExecutionId` VARCHAR(36) NOT NULL COLLATE 'utf8mb3_general_ci',
    `StartDateTimeUtc` DATETIME NULL DEFAULT NULL,
    `EndDateTimeUtc` DATETIME NULL DEFAULT NULL,
    `Result` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `ExecutionType` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `ExecutionTrigger` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `WorkspaceName` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `TaskName` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `ReportName` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `ExportDatasource` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `ExportDatasourceObject` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `SubscriptionType` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `SubscriptionName` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `SubscriptionUser` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `AlertName` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `AlertUser` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `RequestedByUser` VARCHAR(255) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `NumberOfCreatedReportDocuments` INT(11) NULL DEFAULT NULL,
    `JobNumber` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`ExecutionId`) USING BTREE)

    ExecutionLogEntries

    CREATE TABLE `ExecutionLogEntries` (
    `ExecutionLogEntryId` VARCHAR(36) NOT NULL COLLATE 'utf8mb3_general_ci',
    `ExecutionId` VARCHAR(36) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `Order` INT(11) NULL DEFAULT NULL,
    `DateTimeOfOccurrenceUtc` DATETIME NULL DEFAULT NULL,
    `Type` VARCHAR(50) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `Message` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `AdditionalInformation` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `StackTrace` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    PRIMARY KEY (`ExecutionLogEntryId`) USING BTREE)

    EMailedReports

    CREATE TABLE `EMailedReports` (
    `DateTimeUtc` DATETIME NULL DEFAULT NULL,
    `EMailAddress` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `Report` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `Task` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `Workspace` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci',
    `Subject` TEXT NULL DEFAULT NULL COLLATE 'utf8mb4_general_ci'
    )

    Was this article helpful?
    What's Next