Could not find stored procedure 'DYNAMICS.dbo.zDP_PA003502F_1'

Symptoms

When installing CDA, you received the following message:

[Microsoft][SQL Server Native Client 11.0][SQL Server]Could not find stored procedure 'DYNAMICS.dbo.zDP_PA003502F_1'.

[Microsoft][SQL Server Native Client 11.0][SQL Server]Invalid object name 'DYNAMICS.dbo.PA003502'.

Reason(s)

The following are the reasons why this error populated:

1. The Create/Upgrade tables process has not been completed for the Company Data Archive module.
2. The Registration table was not successfully created/upgraded.

Resolution

Method 1:

Ensure that installation steps for Company Data Archive are completed:

1. Navigate to the Archive Company Install window (Dynamics GP – Tools – Utilities – System – Company Data Archive Install)
2. Select both ‘Create/Upgrade Tables’ and ‘Install Stored Procedures’
3. Click OK

This will need to be completed in both the production and archive database.

Method 2:

If the issue persists after completing method 1, this generally is encountered when there is an issue creating the registration table required to complete the installation. Please run the following script:

USE [DYNAMICS]

GO

/****** Object:  Table [dbo].[PA003502]    Script Date: 12/31/2015 09:00:54 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[PA003502](

       [UNIQKEY] [char](1) NOT NULL,

       [paRegkey] [char](21) NOT NULL,

       [Expiry_Date] [datetime] NOT NULL,

       [PA_Years] [smallint] NOT NULL,

       [PA_Months] [smallint] NOT NULL,

       [PA_RemoveOnlyAllowed] [tinyint] NOT NULL,

       [PA_BulkArchive] [tinyint] NOT NULL,

       [USERID] [char](15) NOT NULL,

       [DATEDONE] [datetime] NOT NULL,

       [TRXSize] [int] NOT NULL,

       [NUMOFTRX] [int] NOT NULL,

       [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,

CONSTRAINT [PKPA003502] PRIMARY KEY CLUSTERED

(

       [UNIQKEY] 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

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[PA003502]  WITH CHECK ADD CHECK  ((datepart(hour,[DATEDONE])=(0) AND datepart(minute,[DATEDONE])=(0) AND datepart(second,[DATEDONE])=(0) AND datepart(millisecond,[DATEDONE])=(0)))

GO

ALTER TABLE [dbo].[PA003502]  WITH CHECK ADD CHECK  ((datepart(hour,[Expiry_Date])=(0) AND datepart(minute,[Expiry_Date])=(0) AND datepart(second,[Expiry_Date])=(0) AND datepart(millisecond,[Expiry_Date])=(0)))

GO

Once complete, run both the ‘Create/Upgrade Tables’ and ‘Install Stored Procedures’ process within the CDA installation window. To confirm, the install steps need to be completed in all company databases (production and archive).