Troubleshooting: Unable to open Consolidated Financial Report – Invalid DDL Statement . . . Cannot find the object “dbo.ConsolidateProfile” because it does not exist or you do not have permissions

Question: When I try to open AutoCount Consolidate Financial Report, system prompted error “Invalid DDL Statement . . . Cannot find the object “dbo.ConsolidateProfile”. What should I do?

Version: 1.8 / 1.9 / 2.0

When click on OK

Click OK again another error prompted.

Possible Reason:

There is a missing table in your Consolidate database.

Solution:

1. Search and open SQL Management Studio from Windows search.

2. Connect SQL Server Management Studio.

3. Right click on AUTOCOUNT_CONSOLIDATE (for 1.9), AUTOCOUNT_CONSOLIDATE_V2 (for 2.0) and select New Query.

4. Paste the copy of below sql query.

EXEC sp_addtype N'd_AccType', N'nvarchar (2)' GO EXEC sp_addtype N'd_Boolean', N'char (1)' GO EXEC sp_addtype N'd_AccNo', N'nvarchar (12)' GO EXEC sp_addtype N'd_AccDescription', N'nvarchar (80)' GO EXEC sp_addtype N'd_SpecialAccType', N'varchar (3)' GO EXEC sp_addtype N'd_CurrencyCode', N'nvarchar (5)' GO EXEC sp_addtype N'd_Project', N'nvarchar (20)' GO EXEC sp_addtype N'd_Dept', N'nvarchar (20)' GO EXEC sp_addtype N'd_BudgetName', N'nvarchar (20)' GO EXEC sp_addtype N'd_PeriodNo', N'int' GO EXEC sp_addtype N'd_Money', N'decimal(19,2)' GO EXEC sp_addtype N'd_JournalType', N'nvarchar (10)' GO EXEC sp_addtype N'd_SourceType', N'varchar (2)' GO EXEC sp_addtype N'd_GLDescription', N'nvarchar (100)' GO EXEC sp_addtype N'd_CurrencyRate', N'decimal(19, 12)' GO CREATE TABLE [dbo].[ConsolidateProfile](

     [AccountBookKey] [bigint] NOT NULL,
     [CompanyName] [nvarchar](80) NULL,
     [Remark] [nvarchar](12) NULL,
     [DisplayName] [nvarchar](40) NULL,
     [UseLiveDB] [d_Boolean] NOT NULL,
     [LiveDBServerName] [nvarchar] (100) NULL,
     [LiveDBUserName] [nvarchar] (20) NULL,
     [LiveDBPassword] [nvarchar] (100) NULL,
     [LiveDBDatabaseName] [nvarchar] (20) NULL,
     [FiscalYearStartPeriod] [int] NULL,
     [ActualDataStartPeriod] [int] NULL,
     [CurrencyDecimal] [int] NOT NULL,
     [UseLiveStockBalance] [d_Boolean] NOT NULL,
     [UseBankerRounding] [d_Boolean] NOT NULL,
     [OpeningBalanceInYTD] [d_Boolean] NOT NULL,
     [BalanceStockAccountNo] [d_AccNo] NULL,
     [LastUpdatedData] [datetime] NULL,
     CONSTRAINT [PK_ConsolidateProfile] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[AccGroup] (

     [AccountBookKey] [bigint] NOT NULL,
     [AccType] [d_AccType] NOT NULL,
     [Seq] [int] NOT NULL,
     [NormalBalance] char(1) NOT NULL,
     [Group] char(2) NOT NULL,
     [Level] [int] NOT NULL,
     CONSTRAINT [PK_AccGroup] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [AccType] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[AccType] (

     [AccountBookKey] [bigint] NOT NULL,
     [AccType] [d_AccType] NOT NULL,
     [Description] [nvarchar] (40) NULL ,
     [Desc2] [nvarchar] (40) NULL ,
     [IsBSType] [d_Boolean] NOT NULL ,
     [IsSystemType] [d_Boolean] NOT NULL ,
     CONSTRAINT [PK_AccType] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [AccType] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[GLMast](

     [AccountBookKey] [bigint] NOT NULL,
     [AccNo] [dbo].[d_AccNo] NOT NULL,
     [ParentAccNo] [dbo].[d_AccNo] NULL,
     [Description] [dbo].[d_AccDescription] NULL,
     [Desc2] [dbo].[d_AccDescription] NULL,
     [AccType] [dbo].[d_AccType] NOT NULL,
     [SpecialAccType] [dbo].[d_SpecialAccType] NULL,
     [CurrencyCode] [dbo].[d_CurrencyCode] NOT NULL,
     [CashFlowCategory] [char](1) NULL,
     CONSTRAINT [PK_GLMast] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [AccNo] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[AssetLink](

     [AccountBookKey] [bigint] NOT NULL,
     [AssetAccNo] [dbo].[d_AccNo] NOT NULL,
     [AssetDeprnAccNo] [dbo].[d_AccNo] NOT NULL,
     CONSTRAINT [PK_AssetLink] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [AssetAccNo] ASC,
           [AssetDeprnAccNo] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[Project](

     [AccountBookKey] [bigint] NOT NULL,
     [ProjNo] [dbo].[d_Project] NOT NULL,
     [ParentProjNo] [dbo].[d_Project] NULL,
     [Description] [nvarchar](40) NULL,
     [Desc2] [nvarchar](40) NULL,
     [IsActive] [dbo].[d_Boolean] NOT NULL,
     [LastUpdate] [int] NOT NULL,
     CONSTRAINT [PK_Project] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [ProjNo] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[Dept](

     [AccountBookKey] [bigint] NOT NULL,
     [DeptNo] [dbo].[d_Dept] NOT NULL,
     [ParentDeptNo] [dbo].[d_Dept] NULL,
     [Description] [nvarchar](40) NULL,
     [Desc2] [nvarchar](40) NULL,
     [IsActive] [dbo].[d_Boolean] NOT NULL,
     [LastUpdate] [int] NOT NULL,
     CONSTRAINT [PK_Dept] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [DeptNo] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[Budget](

     [AccountBookKey] [bigint] NOT NULL,
     [BudgetKey] [int] NOT NULL,
     [BudgetName] [dbo].[d_BudgetName] NOT NULL,
     CONSTRAINT [PK_Budget] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [BudgetKey] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[BudgetPBalance](

     [AccountBookKey] [bigint] NOT NULL,
     [BudgetKey] [int] NOT NULL,
     [PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
     [AccNO] [dbo].[d_AccNo] NOT NULL,
     [ProjNo] [dbo].[d_Project] NULL,
     [DeptNo] [dbo].[d_Dept] NULL,
     [Amount] [dbo].[d_Money] NULL

) ON [PRIMARY] GO

CREATE NONCLUSTERED INDEX [IX_BudgetPBalance] ON [dbo].[BudgetPBalance] (

     [AccountBookKey] ASC,
     [BudgetKey] ASC,
     [PeriodNo] ASC,
     [AccNO] ASC,
     [ProjNo] ASC,
     [DeptNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

CREATE TABLE [dbo].[StockSet](

     [AccountBookKey] [bigint] NOT NULL,
     [StockSetKey] [int] NOT NULL,
     [OpenStock] [dbo].[d_AccNo] NOT NULL,
     [CloseStock] [dbo].[d_AccNo] NOT NULL,
     [BalanceStock] [dbo].[d_AccNo] NOT NULL,
     CONSTRAINT [PK_StockSet] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [StockSetKey] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[StockPBalance](

     [AccountBookKey] [bigint] NOT NULL,
     [StockSetKey] [int] NOT NULL,
     [PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
     [ProjNo] [dbo].[d_Project] NULL,
     [DeptNo] [dbo].[d_Dept] NULL,
     [Balance] [dbo].[d_Money] NULL

) ON [PRIMARY] GO

CREATE NONCLUSTERED INDEX [IX_StockPBalance] ON [dbo].[StockPBalance] (

     [AccountBookKey] ASC,
     [StockSetKey] ASC,
     [PeriodNo] ASC,
     [ProjNo] ASC,
     [DeptNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

CREATE TABLE [dbo].[PBalance](

     [AccountBookKey] [bigint] NOT NULL,
     [PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
     [AccNo] [dbo].[d_AccNo] NOT NULL,
     [ProjNo] [dbo].[d_Project] NULL,
     [DeptNo] [dbo].[d_Dept] NULL,
     [DR] [dbo].[d_Money] NULL,
     [CR] [dbo].[d_Money] NULL,
     [HomeDR] [dbo].[d_Money] NULL,
     [HomeCR] [dbo].[d_Money] NULL

) ON [PRIMARY] GO

CREATE NONCLUSTERED INDEX [IX_PBalance] ON [dbo].[PBalance] (

     [AccountBookKey] ASC,
     [PeriodNo] ASC,
     [AccNo] ASC,
     [ProjNo] ASC,
     [DeptNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

CREATE TABLE [dbo].[OBalance](

     [AccountBookKey] [bigint] NOT NULL,
     [PeriodNo] [dbo].[d_PeriodNo] NOT NULL,
     [AccNo] [dbo].[d_AccNo] NOT NULL,
     [ProjNo] [dbo].[d_Project] NULL,
     [DeptNo] [dbo].[d_Dept] NULL,
     [DR] [dbo].[d_Money] NULL,
     [CR] [dbo].[d_Money] NULL,
     [HomeDR] [dbo].[d_Money] NULL,
     [HomeCR] [dbo].[d_Money] NULL

) ON [PRIMARY] GO

CREATE NONCLUSTERED INDEX [IX_OBalance] ON [dbo].[OBalance] (

     [AccountBookKey] ASC,
     [PeriodNo] ASC,
     [AccNo] ASC,
     [ProjNo] ASC,
     [DeptNo] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO

CREATE TABLE [dbo].[GLDTL](

     [AccountBookKey] [bigint] NOT NULL,
     [GLDtlKey] [bigint] NOT NULL,
     [AccNo] [dbo].[d_AccNo] NOT NULL,
     [DEAccNo] [dbo].[d_AccNo] NULL,
     [ProjNo] [dbo].[d_Project] NULL,
     [DeptNo] [dbo].[d_Dept] NULL,
     [CurrencyCode] [dbo].[d_CurrencyCode] NOT NULL,
     [CurrencyRate] [dbo].[d_CurrencyRate] NOT NULL,
     [ToHomeRate] [dbo].[d_CurrencyRate] NOT NULL,
     [OrgDR] [dbo].[d_Money] NULL,
     [OrgCR] [dbo].[d_Money] NULL,
     [DR] [dbo].[d_Money] NULL,
     [CR] [dbo].[d_Money] NULL,
     [HomeDR] [dbo].[d_Money] NULL,
     [HomeCR] [dbo].[d_Money] NULL,
     [TransDate] [datetime] NOT NULL,
     [Description] [dbo].[d_GLDescription] NULL,
     [RefNo1] [nvarchar](20) NULL,
     [RefNo2] [nvarchar](20) NULL,
     [DEKey] [bigint] NULL,
     CONSTRAINT [PK_GLDTL] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [GLDtlKey] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

CREATE TABLE [dbo].[FiscalYear](

     [AccountBookKey] [bigint] NOT NULL,
     [FiscalYearName] [nvarchar](20) NOT NULL,
     [FromDate] [datetime] NOT NULL,
     [ToDate] [datetime] NOT NULL,
     [IsActive] [dbo].[d_Boolean] NOT NULL,
     CONSTRAINT [PK_FiscalYear] PRIMARY KEY NONCLUSTERED 
     (
           [AccountBookKey] ASC,
           [FiscalYearName] ASC
     )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]

) ON [PRIMARY] GO

5. Execute the sql query.