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: Difference between revisions

Content added Content deleted

Inline

Latest revision as of 09:01, 28 February 2023

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.