No internet connection
  1. Home
  2. Questions

CREATE DATABASE failed (system error 3)

By Ollie A @olliea2021-03-19 12:45:02.031Z

At the point of installation where the Message Store is configured, trying to create the database presents the following error:

Database create failed. Directory lookup for the file "C:\ProgramData\Parker Software\ThinkAutomation.NET\ThinkAutomationMessageStore,mdf" failed with the operating system error(The system cannot find the path specified.).
CREATE DATABASE failed. Some file names listed could not be created. Check related errors. FROM Execute.

  • 8 replies
  1. D
    In reply toolliea:
    Dave @Dave5a2021-03-19 20:43:32.538Z2021-03-19 21:04:21.219Z

    Thank you @liam , that's exactly what I needed. (In addition to CREATE DATABASE ThinkAutomationMessageStore ;) But yes, it is a remote SQL instance.

    1. LLiam @liam2021-03-23 12:32:43.193Z

      @Dave5a - the statement I provided was an old incorrect version, apologies.
      I've now updated the comment, please try it again.

      1. DDave @Dave5a2021-03-23 22:26:55.507Z

        Thank you for the update, I deleted all objects and reran the SQL. Was this also intended as a solution to the Message Store issue in my other Question? It is still unsolved after this update.

    2. L
      In reply toolliea:
      Liam @liam2021-03-19 12:48:15.366Z2021-03-23 12:32:08.562Z

      CREATEDB_MESSAGESTORE.SQL (9.88 KiB)

      Something has gone wrong with how the database is trying to be created here I think.
      Is this a remote SQL instance?
      Perhaps it is looking for that directory on the remote SQL server, creating this folder path on that server might fix the problem.
      Alternatively, it might be easier to manually create the database.

      --==================================================================================================
      --
      --  ThinkAutomation 5 Server Database Creation Script
      --
      --  Revision:               2020.0.1
      --  Author:                 Parker Software
      --  Created On:             24/01/2020
      --  Last Change Date:       30/06/2020
      --  Changed By:             SP
      --
      --==================================================================================================
      --
      
      ----------------------------------------------------------------------------------------------------
      -- TABLES
      ----------------------------------------------------------------------------------------------------
      
      ----------------------------------------------------------------
      -- Solutions
      ----------------------------------------------------------------
      CREATE TABLE Solutions 
      (
      	[Id] CHAR(24) NOT NULL,
      	[Name] VARCHAR(500) NOT NULL,
      	[ToRemove] BIT DEFAULT 0,
      	[Json] VARCHAR(MAX),
      CONSTRAINT PK_Solutions PRIMARY KEY ([Id])
      )
      GO
      
      ----------------------------------------------------------------
      -- Message Sources
      ----------------------------------------------------------------
      CREATE TABLE MessageSources
      (
      	[Id] CHAR(24) NOT NULL,
      	[SolutionId] CHAR(24) NOT NULL,
      	[Name] VARCHAR(500) NOT NULL,
      	[ToRemove] BIT DEFAULT 0,
      	[Json] VARCHAR(MAX),
      CONSTRAINT PK_MessageSources PRIMARY KEY ([Id])
      )
      GO
      
      ----------------------------------------------------------------
      -- Automations
      ----------------------------------------------------------------
      CREATE TABLE Automations
      (
      	[Id] CHAR(24) NOT NULL,
      	[SolutionId] CHAR(24) NOT NULL,
      	[Name] VARCHAR(500) NOT NULL,
      	[ToRemove] BIT DEFAULT 0,
      	[Json] VARCHAR(MAX),
      CONSTRAINT PK_Automations PRIMARY KEY ([Id])
      )
      GO
      
      ----------------------------------------------------------------
      -- Message Store
      ----------------------------------------------------------------
      CREATE TABLE MessageStore
      (
      	[Id] CHAR(24) NOT NULL ,
      	[SolutionId] CHAR(24) NOT NULL ,
      	[MessageSourceId] CHAR(24) NOT NULL ,
      	[IncomingUid] VARCHAR(250) COLLATE Latin1_General_CS_AS NOT NULL ,
      	[Subject] NVARCHAR(3000),
      	[SentFrom] VARCHAR(3000),
      	[SentTo] VARCHAR(3000),
      	[Importance] CHAR(1) DEFAULT 'N',
      	[MessageDate] DATETIME DEFAULT GETDATE(),
      	[MessageFlags] INTEGER DEFAULT 0,
      	[MessageSize] INTEGER DEFAULT 0,
      	[AutomationId] CHAR(24) ,
      	[AutomationPending] BIT DEFAULT 1,
      	[AutomationSuccess] BIT DEFAULT 0,
      	[AutomationError] VARCHAR(3000),
      	[AutomationReturnValue] NVARCHAR(3000),
      	[AutomationDate] DATETIME DEFAULT GETDATE(),
      	[AutomationExecutionTime] INTEGER DEFAULT 0,
      	[Attachments] BIT DEFAULT 0,
      	[AttachmentNames] NVARCHAR(3000),
      	[ValidatePending] SMALLINT DEFAULT 0,
      	[ValidateExpires] DATETIME,
      	[Mime] VARCHAR(MAX),
      CONSTRAINT PK_MessageStore PRIMARY KEY ([Id])
      )
      GO
      
      CREATE UNIQUE INDEX IX_MessageStore1 ON MessageStore([MessageSourceId],[IncomingUid])
      GO
      
      CREATE INDEX IX_MessageStore2 ON MessageStore([MessageSourceId])
      GO
      
      CREATE INDEX IX_MessageStore3 ON MessageStore([AutomationId])
      GO
      
      CREATE INDEX IX_MessageStore4 ON MessageStore([SolutionId],[MessageDate]) 
      GO
      
      ----------------------------------------------------------------
      -- Validation Temporary Storage
      ----------------------------------------------------------------
      CREATE TABLE ValidationTemp
      (
      	[Id] INTEGER IDENTITY NOT NULL ,
      	[MessageStoreId] CHAR(24) NOT NULL ,
      	[AutomationId] CHAR(24),
      	[Dated] DATETIME DEFAULT GETDATE(),
      	[ValidateActionId] CHAR(24),
      	[ValidateJson] VARCHAR(MAX),
      	[ValidateJsonResults] VARCHAR(MAX),
      CONSTRAINT PK_ValidationTemp PRIMARY KEY ([Id])
      )
      GO
      
      CREATE UNIQUE INDEX IX_ValidationTemp1 ON ValidationTemp([MessageStoreId])
      GO
      
      CREATE INDEX IX_ValidationTemp2 ON ValidationTemp([AutomationId])
      GO
      
      ----------------------------------------------------------------
      -- Message Source Log
      ----------------------------------------------------------------
      CREATE TABLE MessageSourceLog 
      (
      	[Id] CHAR(24) NOT NULL,
      	[MessageSourceId] CHAR(24) NOT NULL ,
      	[NodeName] VARCHAR(25),
      	[DateStamp] DATETIME DEFAULT GETDATE(),
      	[Error] BIT DEFAULT 0,
      	[Line] VARCHAR(1000),
      CONSTRAINT PK_MessageSourceLog PRIMARY KEY ([Id])	
      )
      GO
      
      ----------------------------------------------------------------
      -- Automation Log
      ----------------------------------------------------------------
      CREATE TABLE AutomationLog
      (
      	[Id] CHAR(24) NOT NULL ,
      	[MessageStoreId] CHAR(24) NOT NULL ,
      	[AutomationId] CHAR(24) NOT NULL ,
      	[NodeName] VARCHAR(25) ,
      	[DateStamp] DATETIME DEFAULT GETDATE(),
      	[Error] BIT DEFAULT 0,
      	[LineNumber] INTEGER DEFAULT 0,
      	[Line] VARCHAR(1000),
      CONSTRAINT PK_AutomationLog PRIMARY KEY ([Id])
      )
      GO
      
      CREATE INDEX IX_AutomationLog1 ON AutomationLog([MessageStoreId])
      GO
      
      ----------------------------------------------------------------
      -- Outbox
      ----------------------------------------------------------------
      CREATE TABLE Outbox 
      (
      	[Id] CHAR(24) NOT NULL,
      	[MessageStoreId] CHAR(24),
      	[SolutionId] CHAR(24),
      	[AutomationId] CHAR(24),
      	[ActionId] CHAR(24),
      	[DateAdded] DATETIME DEFAULT GETDATE(),
      	[ScheduledDate] DATETIME DEFAULT GETDATE(),
      	[Scheduled] BIT DEFAULT 0,
      	[Status] TINYINT DEFAULT 0 ,
      	[SendTo] VARCHAR(3000) NOT NULL,
      	[ReplyTo] VARCHAR(1000),
      	[SendFrom] VARCHAR(3000),
      	[Subject] NVARCHAR(3000),
      	[Sending] BIT DEFAULT 0,
      	[SendType] INTEGER DEFAULT 0,
      	[RetryDate] DATETIME DEFAULT GETDATE() ,
      	[Retries] SMALLINT DEFAULT 0 ,
      	[Message] VARCHAR(MAX) ,
      CONSTRAINT PK_Outbox PRIMARY KEY ([Id])
      )
      GO
      
      CREATE INDEX IX_Outbox1 ON Outbox([ScheduledDate])
      GO
      
      ----------------------------------------------------------------
      -- Sent Items
      ----------------------------------------------------------------
      CREATE TABLE SentItems 
      (
      	[Id] INTEGER IDENTITY NOT NULL,
      	[SolutionId] CHAR(24) NOT NULL,
      	[AutomationId] CHAR(24) NOT NULL,
      	[MessageStoreId] CHAR(24) NOT NULL,
      	[DateSent] DATETIME DEFAULT GETDATE(),
      	[SentTo] VARCHAR(3000) NOT NULL,
      	[SentFrom] VARCHAR(3000),
      	[SentType] INTEGER DEFAULT 0,
      	[Subject] NVARCHAR(3000),
      	[Scheduled] BIT DEFAULT 0,
      	[Importance] INTEGER DEFAULT 0,
      	[Attachments] BIT DEFAULT 0,
      	[Success] BIT DEFAULT 0,
      	[Result] VARCHAR(250),
      	[MessageSize] INTEGER DEFAULT 0,
      	[Message] VARCHAR(MAX) ,
      CONSTRAINT PK_SentItems PRIMARY KEY ([Id])
      )
      GO
      
      CREATE INDEX IX_SentItems1 ON SentItems([MessageStoreId])
      GO
      
      CREATE INDEX IX_SentItems2 ON SentItems([AutomationId])
      GO
      
      ----------------------------------------------------------------
      -- Day Summary
      ----------------------------------------------------------------
      CREATE TABLE DaySummary
      (
      	[Id] INTEGER IDENTITY NOT NULL,
      	[SolutionId] CHAR(24) NOT NULL,
      	[Dated] SMALLDATETIME DEFAULT GETDATE() NOT NULL,
      	[MessagesInLast] DATETIME,
          [MessagesIn] INTEGER DEFAULT 0,
          [MessagesOut] INTEGER DEFAULT 0,
          [MessagesStarted] INTEGER DEFAULT 0,
          [MessagesFailed] INTEGER DEFAULT 0,
          [MessagesFailedLast] DATETIME ,
          [MessagesCompleted] INTEGER DEFAULT 0,
          [MessagesCompletedLast] DATETIME,
          [BytesIn] BIGINT DEFAULT 0,
          [LastError] VARCHAR(1000),
      CONSTRAINT PK_DaySummary PRIMARY KEY ([Id])
      )
      GO
      
      CREATE INDEX IX_DaySummary1 ON DaySummary([SolutionId],[Dated])
      GO
      
      ----------------------------------------------------------------
      -- Web Callbacks
      ----------------------------------------------------------------
      CREATE TABLE WebCallBacks
      (
      	[Id] VARCHAR(100) NOT NULL,
      	[SolutionId] CHAR(24) NOT NULL,
      	[AutomationId] CHAR(24) NOT NULL,
      	[ActionId] CHAR(24) NOT NULL,
      	[MessageStoreId] CHAR(24),
      	[DateSubmitted] DATETIME DEFAULT GETDATE() NOT NULL,
      	[DateRequested] DATETIME DEFAULT GETDATE() NOT NULL,
      	[DateExpires] DATETIME DEFAULT GETDATE() NOT NULL,
      	[Status] INTEGER DEFAULT 0,
      	[ContentType] VARCHAR(100),
      	[RequestParameters] VARCHAR(MAX),
      	[Response] VARCHAR(MAX),
      CONSTRAINT PK_WebCallBacks PRIMARY KEY([Id])
      )
      GO
      
      ----------------------------------------------------------------
      -- Variable Store
      ----------------------------------------------------------------
      CREATE TABLE VariableStore 
      (
      	[Id] INTEGER IDENTITY NOT NULL,
      	[ParentId] VARCHAR(24) NOT NULL,
      	[NodeName] VARCHAR(25),
      	[Name] VARCHAR(250) NOT NULL,
      	[Dated] DATETIME DEFAULT GETDATE(),
      	[DataType] INTEGER DEFAULT 0,
      	[Value] NVARCHAR(MAX),
      CONSTRAINT PK_VariableStore PRIMARY KEY([Id])
      )
      GO
      
      CREATE UNIQUE INDEX IX_VariableStore1 ON VariableStore([ParentId],[NodeName],[Name])
      GO
      
      ----------------------------------------------------------------
      -- Delete Message
      ----------------------------------------------------------------
      CREATE PROCEDURE sp_DeleteMessage
        @Id CHAR(24)
      AS
      BEGIN
        DECLARE @Result int = 0
        DELETE FROM AutomationLog WHERE MessageStoreId=@Id;
        DELETE FROM SentItems WHERE MessageStoreId=@Id;
        DELETE FROM MessageStore WHERE Id = @Id;
        SELECT @Result=@@RowCount;
      END
      RETURN @Result;
      GO
      
      ----------------------------------------------------------------
      -- Delete MessageSource
      ----------------------------------------------------------------
      CREATE PROCEDURE sp_DeleteMessageSource
        @Id CHAR(24)
      AS
      BEGIN
        SET NOCOUNT ON;
        DELETE FROM MessageStore WHERE MessageSourceId=@Id;
        DELETE FROM MessageSourceLog WHERE MessageSourceId=@Id;
        DELETE FROM VariableStore WHERE ParentId=@Id;
        DELETE FROM MessageSources WHERE Id = @Id;
      END
      GO
      
      ----------------------------------------------------------------
      -- Delete Automation
      ----------------------------------------------------------------
      CREATE PROCEDURE sp_DeleteAutomation
        @Id CHAR(24)
      AS
      BEGIN
        SET NOCOUNT ON;
        DELETE FROM WebCallBacks WHERE AutomationId=@Id;
        DELETE FROM AutomationLog WHERE AutomationId=@Id;
        DELETE FROM MessageStore WHERE AutomationId=@Id;
        DELETE FROM SentItems WHERE AutomationId=@Id;
        DELETE FROM ValidationTemp WHERE AutomationId=@Id;
        DELETE FROM VariableStore WHERE ParentId=@Id;
        DELETE FROM Outbox WHERE AutomationId=@Id;
        DELETE FROM Automations WHERE Id = @Id;
      END
      GO
      
      
      
      
      
      
      
      1. Progress
        with handling this problem
      2. @olliea marked this topic as Started 2021-03-19 12:45:55.444Z.
      3. L@liam marked this topic as Done 2021-03-22 09:22:38.313Z.
      4. L@liam marked this topic as Started 2021-03-22 10:16:01.254Z.
      5. L@liam marked this topic as Done 2021-03-22 14:20:32.905Z.