How To Backup, Delete And Re-Create An SQL Table

Did Your Company Table Fail During A Dynamics GP Upgrade?

From time to time during upgrades, we run into time-consuming issues that really don't make much sense.  I have performed more Dynamics GP upgrades than I care to count and no two are ever alike.

The process below will guide you through backing up, deleting and re-creating a Dynamics GP company table that failed to upgrade. (This happened during a test upgrade, and we did not get this error during the production upgrade.  Go figure.)

During Dynamics GP upgrades you will find from time to time that a company will fail to upgrade. Worse yet you check the error and find you get something like this:

Then you check for the temporary table and it does not exist.

This script was used to solve the issue I was having with this table in question.

BEWARE!

If using this process on other tables, make a special note in “Step 3”. In this section pay attention to the table structure as all tables are not set up the same way so please pay attention and make those changes to fit your specific table. Before running “Step 3” verify the current table (i.e. GL00100) configuration setup is the same as the script below and if not make those changes.

The process below will have you take a backup of the corrupt table, then delete the failed upgrade from the DU000030 table, then you will drop the current GL00100 table and create a new GL00100 table.

The final step will have you copy the data from step 1 to the newly created GL00100 table.

Then start the upgrade again and you should be good to go.

Below are the steps to resolve this issue (make sure you take SQL database backups on the GP company and Dynamics database before proceeding)

Step 1 -Run the script to backup the table GL00100

Select * Into GL00100BAK from GL00100

Step 2 - Run the script to remove the failed table record from the DU000030

Delete DYNAMICS..DU000030 WHERE (Status <> 0 or errornum <> 0) and Status <>15

Step 3 - Run the script to recreate the GL00100 table

      /*Begin_GL00100*/

            if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GL00100]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)

            drop table [dbo].[GL00100]

            GO




            CREATE TABLE [dbo].[GL00100] (

             [ACTINDX] [int] NOT NULL ,

             [ACTNUMBR_1] [char] (5) NOT NULL ,

             [ACTNUMBR_2] [char] (5) NOT NULL ,

             [ACTNUMBR_3] [char] (5) NOT NULL ,

             [ACTNUMBR_4] [char] (5) NOT NULL ,

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

             [MNACSGMT] [char] (67) NOT NULL ,

             [ACCTTYPE] [smallint] NOT NULL ,

             [ACTDESCR] [char] (51) NOT NULL ,

             [PSTNGTYP] [smallint] NOT NULL ,

             [ACCATNUM] [smallint] NOT NULL ,

             [ACTIVE] [tinyint] NOT NULL ,

             [TPCLBLNC] [smallint] NOT NULL ,

             [DECPLACS] [smallint] NOT NULL ,

             [FXDORVAR] [smallint] NOT NULL ,

             [BALFRCLC] [smallint] NOT NULL ,

             [DSPLKUPS] [binary] (4) NOT NULL ,

             [CNVRMTHD] [smallint] NOT NULL ,

             [HSTRCLRT] [numeric](19, 7) NOT NULL ,

             [NOTEINDX] [numeric](19, 5) NOT NULL ,

             [CREATDDT] [datetime] NOT NULL ,

             [MODIFDT] [datetime] NOT NULL ,

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

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

             [PostSlsIn] [smallint] NOT NULL ,

             [PostIvIn] [smallint] NOT NULL ,

             [PostPurchIn] [smallint] NOT NULL ,

             [PostPRIn] [smallint] NOT NULL ,

             [ADJINFL] [tinyint] NOT NULL ,

             [INFLAREV] [int] NOT NULL ,

             [INFLAEQU] [int] NOT NULL ,

             [ACCTENTR] [tinyint] NOT NULL ,

             [USRDEFS1] [char] (31) NOT NULL ,

             [USRDEFS2] [char] (31) NOT NULL ,

             [Clear_Balance] [tinyint] NOT NULL ,

             [DEX_ROW_TS] [datetime] NOT NULL CONSTRAINT [DF__GL00100__DEX_ROW__7E6CC920] DEFAULT (getutcdate()),

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

             CONSTRAINT [PKGL00100] PRIMARY KEY  NONCLUSTERED

             (

             [ACTINDX]

             )  ON [PRIMARY] ,

             CHECK (datepart(hour,[CREATDDT])=(0) AND datepart(minute,[CREATDDT])=(0) AND datepart(second,[CREATDDT])=(0) AND datepart(millisecond,[CREATDDT])=(0)),

             CHECK (datepart(hour,[MODIFDT])=(0) AND datepart(minute,[MODIFDT])=(0) AND datepart(second,[MODIFDT])=(0) AND datepart(millisecond,[MODIFDT])=(0))

            ) ON [PRIMARY]

            GO




            setuser

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[ACCATNUM]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[ACCTENTR]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[ACCTTYPE]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[ACTALIAS]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[ACTDESCR]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[ACTINDX]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[ACTIVE]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[ACTNUMBR_1]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[ACTNUMBR_2]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[ACTNUMBR_3]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[ACTNUMBR_4]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[ADJINFL]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[BALFRCLC]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[CNVRMTHD]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[GL00100].[CREATDDT]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[Clear_Balance]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[DECPLACS]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[DSPLKUPS]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[FXDORVAR]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_MONEY]', N'[GL00100].[HSTRCLRT]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[INFLAEQU]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[INFLAREV]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[MNACSGMT]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_DATE]', N'[GL00100].[MODIFDT]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_MONEY]', N'[GL00100].[NOTEINDX]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[PSTNGTYP]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[PostIvIn]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[PostPRIn]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[PostPurchIn]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[PostSlsIn]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_INT]', N'[GL00100].[TPCLBLNC]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[USERDEF1]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[USERDEF2]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[USRDEFS1]'

            GO




            EXEC sp_bindefault N'[dbo].[GPS_CHAR]', N'[GL00100].[USRDEFS2]'

            GO




            setuser

            GO




GRANT  SELECT ,  UPDATE ,  INSERT ,  DELETE  ON [dbo].[GL00100]  TO [DYNGRP]

            GO




            /*End_GL00100*/

Step 4 - Run the insert script to put the records back into the newly recreated table.

Insert Into GL00100

(ACCATNUM,ACCTENTR,ACCTTYPE,ACTALIAS,ACTDESCR,ACTINDX,

ACTIVE,ACTNUMBR_1,ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4,ADJINFL,

BALFRCLC,CNVRMTHD,CREATDDT,DECPLACS,DEX_ROW_TS,DSPLKUPS,

FXDORVAR,HSTRCLRT,INFLAEQU,INFLAREV,MNACSGMT,MODIFDT,

NOTEINDX,PostIvIn,PostPRIn,PostPurchIn,PostSlsIn,PSTNGTYP,

TPCLBLNC,USERDEF1,USERDEF2,USRDEFS1,USRDEFS2  )

Select

ACCATNUM,ACCTENTR,ACCTTYPE,ACTALIAS,ACTDESCR,ACTINDX,

ACTIVE,ACTNUMBR_1,ACTNUMBR_2,ACTNUMBR_3,ACTNUMBR_4,ADJINFL,

BALFRCLC,CNVRMTHD,CREATDDT,DECPLACS,DEX_ROW_TS,DSPLKUPS,

FXDORVAR,HSTRCLRT,INFLAEQU,INFLAREV,MNACSGMT,MODIFDT,

NOTEINDX,PostIvIn,PostPRIn,PostPurchIn,PostSlsIn,PSTNGTYP,

TPCLBLNC,USERDEF1,USERDEF2,USRDEFS1,USRDEFS2

From GL00100BAK

Step 5 – Run the upgrade process again.