Invalid column name 'Search_dates_1' upgrading to Microsoft Dynamics GP 2010 R2

For many of us tech heads in the trenches, upgrades constitute a large part of the activities we carry out throughout the year for our clients. Microsoft Dynamics GP 2010 R2 has certainly added to the list of tasks that we must complete throughout these days.

This week marks the beginning of another upgrade that I must carry out for a client. The client will be upgrading from Microsoft Dynamics GP 9.0  (9.0.0388) to Microsoft Dynamics GP 2010 R2 (11.00.1752). It has been brought to the Community's attention, an issue upgrading from Microsoft Dynamics GP 9.0 installations to Microsoft Dynamics GP 2010 SP1 (and possibly involving 2010 R2). Some people have reported getting the following error during the upgrade:

The following SQL statement produced an error: 
if not exists (select * from sysobjects where id = object_id(N'[SLB10400]') and ....

Followed by,

Invalid column name 'Search_dates_1'

It is also possible that other tables and columns may error out:

DDB10300 - Invalid column name 'Seach_dates_1'
ERB11100 - Invalid column name  'Blank_Field_CB'
NLB11100 - Invalid column name  'Blank_Field_CB'

Apparently, the issue is contained to upgrades from version 9.0 to version 2010 R2, since apparently, most people already on 2010 SP1 have applied the SP2 without any issues, adding the columns to the tables above just fine.

To workaround any of these 'Invalid column name' table issues, you will need to have a running Microsoft Dynamics GP 2010 R2 environment from which you can script the tables, indexes, and table auto procedures. The following is an example of the scripts needed to bypass the error on the SLB10400 table:

USE [DYNAMICS]
GO

/****** Object: Table [dbo].[SLB10400]  Script Date: 06/26/2011 11:56:16 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[SLB10400]') AND type in (N'U'))
DROP TABLE [dbo].[SLB10400]
GO

USE [DYNAMICS]
GO

/****** Object: Table [dbo].[SLB10400]  Script Date: 06/26/2011 11:56:16 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING OFF
GO

CREATE TABLE [dbo].[SLB10400](
 [SmartList_ID] [char](15) NOT NULL,
 [GoTo_Number] [smallint] NOT NULL,
 [GoTo_Description] [char](81) NOT NULL,
 [GoTo_Type] [smallint] NOT NULL,
 [PRODID] [smallint] NOT NULL,
 [FORMNAME] [char](79) NOT NULL,
 [fileName] [char](255) NOT NULL,
 [Table_Number] [smallint] NOT NULL,
 [Field_Number] [smallint] NOT NULL,
 [PRCDNAME] [char](255) NOT NULL,
 [Search_Date_Method_1] [smallint] NOT NULL,
 [Search_Date_Method_2] [smallint] NOT NULL,
 [Search_Date_Method_3] [smallint] NOT NULL,
 [Search_Date_Method_4] [smallint] NOT NULL,
 [Search_Dates_1] [datetime] NOT NULL,
 [Search_Dates_2] [datetime] NOT NULL,
 [Search_Dates_3] [datetime] NOT NULL,
 [Search_Dates_4] [datetime] NOT NULL,
 [Search_Field_Numbers_1] [smallint] NOT NULL,
 [Search_Field_Numbers_2] [smallint] NOT NULL,
 [Search_Field_Numbers_3] [smallint] NOT NULL,
 [Search_Field_Numbers_4] [smallint] NOT NULL,
 [Search_Strings_1] [char](255) NOT NULL,
 [Search_Strings_2] [char](255) NOT NULL,
 [Search_Strings_3] [char](255) NOT NULL,
 [Search_Strings_4] [char](255) NOT NULL,
 [Search_Times_1] [datetime] NOT NULL,
 [Search_Times_2] [datetime] NOT NULL,
 [Search_Times_3] [datetime] NOT NULL,
 [Search_Times_4] [datetime] NOT NULL,
 [Comparison_Types_1] [smallint] NOT NULL,
 [Comparison_Types_2] [smallint] NOT NULL,
 [Comparison_Types_3] [smallint] NOT NULL,
 [Comparison_Types_4] [smallint] NOT NULL,
 [Field_Comparison_1] [tinyint] NOT NULL,
 [Field_Comparison_2] [tinyint] NOT NULL,
 [Field_Comparison_3] [tinyint] NOT NULL,
 [Field_Comparison_4] [tinyint] NOT NULL,
 [SmartList_Field_1] [tinyint] NOT NULL,
 [SmartList_Field_2] [tinyint] NOT NULL,
 [SmartList_Field_3] [tinyint] NOT NULL,
 [SmartList_Field_4] [tinyint] NOT NULL,
 [Maximum_SmartList_Record] [int] NOT NULL,
 [Search_Type] [smallint] NOT NULL,
 [Search_Account_Indexes_1] [int] NOT NULL,
 [Search_Account_Indexes_2] [int] NOT NULL,
 [Search_Account_Indexes_3] [int] NOT NULL,
 [Search_Account_Indexes_4] [int] NOT NULL,
 [SmartList_Number] [smallint] NOT NULL,
 [Search_Numbers_1] [smallint] NOT NULL,
 [Search_Numbers_2] [smallint] NOT NULL,
 [Search_Numbers_3] [smallint] NOT NULL,
 [Search_Numbers_4] [smallint] NOT NULL,
 [To_Search_Account_Indexe_1] [int] NOT NULL,
 [To_Search_Account_Indexe_2] [int] NOT NULL,
 [To_Search_Account_Indexe_3] [int] NOT NULL,
 [To_Search_Account_Indexe_4] [int] NOT NULL,
 [To_Search_Date_Method_1] [smallint] NOT NULL,
 [To_Search_Date_Method_2] [smallint] NOT NULL,
 [To_Search_Date_Method_3] [smallint] NOT NULL,
 [To_Search_Date_Method_4] [smallint] NOT NULL,
 [To_Search_Dates_1] [datetime] NOT NULL,
 [To_Search_Dates_2] [datetime] NOT NULL,
 [To_Search_Dates_3] [datetime] NOT NULL,
 [To_Search_Dates_4] [datetime] NOT NULL,
 [To_Search_Numbers_1] [smallint] NOT NULL,
 [To_Search_Numbers_2] [smallint] NOT NULL,
 [To_Search_Numbers_3] [smallint] NOT NULL,
 [To_Search_Numbers_4] [smallint] NOT NULL,
 [To_Search_Strings_1] [char](255) NOT NULL,
 [To_Search_Strings_2] [char](255) NOT NULL,
 [To_Search_Strings_3] [char](255) NOT NULL,
 [To_Search_Strings_4] [char](255) NOT NULL,
 [To_Search_Times_1] [datetime] NOT NULL,
 [To_Search_Times_2] [datetime] NOT NULL,
 [To_Search_Times_3] [datetime] NOT NULL,
 [To_Search_Times_4] [datetime] NOT NULL,
 [Extender_Type] [smallint] NOT NULL,
 [Extender_ID] [char](15) NOT NULL,
 [Drill_Down_ID] [char](15) NOT NULL,
 [DEX_ROW_ID] [int] IDENTITY(1,1) NOT NULL,
 [TXTFIELD] [text] NOT NULL,
 CONSTRAINT [PKSLB10400] PRIMARY KEY NONCLUSTERED 
(
 [SmartList_ID] ASC,
 [GoTo_Number] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

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

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

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

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

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[Search_Times_1])=(1) AND datepart(month,[Search_Times_1])=(1) AND datepart(year,[Search_Times_1])=(1900)))
GO

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[Search_Times_2])=(1) AND datepart(month,[Search_Times_2])=(1) AND datepart(year,[Search_Times_2])=(1900)))
GO

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[Search_Times_3])=(1) AND datepart(month,[Search_Times_3])=(1) AND datepart(year,[Search_Times_3])=(1900)))
GO

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[Search_Times_4])=(1) AND datepart(month,[Search_Times_4])=(1) AND datepart(year,[Search_Times_4])=(1900)))
GO

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

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[To_Search_Times_1])=(1) AND datepart(month,[To_Search_Times_1])=(1) AND datepart(year,[To_Search_Times_1])=(1900)))
GO

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[To_Search_Times_2])=(1) AND datepart(month,[To_Search_Times_2])=(1) AND datepart(year,[To_Search_Times_2])=(1900)))
GO

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[To_Search_Times_3])=(1) AND datepart(month,[To_Search_Times_3])=(1) AND datepart(year,[To_Search_Times_3])=(1900)))
GO

ALTER TABLE [dbo].[SLB10400] WITH CHECK ADD CHECK ((datepart(day,[To_Search_Times_4])=(1) AND datepart(month,[To_Search_Times_4])=(1) AND datepart(year,[To_Search_Times_4])=(1900)))
GO

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

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

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

GRANT SELECT, INSERT, UPDATE, DELETE ON [dbo].[SLB10400] TO DYNGRP;
GO

USE [DYNAMICS]
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400F_1]  Script Date: 06/26/2011 11:55:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zDP_SLB10400F_1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[zDP_SLB10400F_1]
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400L_1]  Script Date: 06/26/2011 11:55:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zDP_SLB10400L_1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[zDP_SLB10400L_1]
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400N_1]  Script Date: 06/26/2011 11:55:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zDP_SLB10400N_1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[zDP_SLB10400N_1]
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400SD]  Script Date: 06/26/2011 11:55:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zDP_SLB10400SD]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[zDP_SLB10400SD]
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400SS_1]  Script Date: 06/26/2011 11:55:05 ******/
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[zDP_SLB10400SS_1]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[zDP_SLB10400SS_1]
GO

USE [DYNAMICS]
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400F_1]  Script Date: 06/26/2011 11:55:05 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[zDP_SLB10400F_1] (@SmartList_ID_RS char(15), @GoTo_Number_RS smallint, @SmartList_ID_RE char(15), @GoTo_Number_RE smallint) AS set nocount on IF @SmartList_ID_RS IS NULL BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 ORDER BY SmartList_ID ASC, GoTo_Number ASC END ELSE IF @SmartList_ID_RS = @SmartList_ID_RE BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID = @SmartList_ID_RS AND GoTo_Number BETWEEN @GoTo_Number_RS AND @GoTo_Number_RE ORDER BY SmartList_ID ASC, GoTo_Number ASC END ELSE BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID BETWEEN @SmartList_ID_RS AND @SmartList_ID_RE AND GoTo_Number BETWEEN @GoTo_Number_RS AND @GoTo_Number_RE ORDER BY SmartList_ID ASC, GoTo_Number ASC END set nocount off  
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400L_1]  Script Date: 06/26/2011 11:55:05 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[zDP_SLB10400L_1] (@SmartList_ID_RS char(15), @GoTo_Number_RS smallint, @SmartList_ID_RE char(15), @GoTo_Number_RE smallint) AS set nocount on IF @SmartList_ID_RS IS NULL BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 ORDER BY SmartList_ID DESC, GoTo_Number DESC END ELSE IF @SmartList_ID_RS = @SmartList_ID_RE BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID = @SmartList_ID_RS AND GoTo_Number BETWEEN @GoTo_Number_RS AND @GoTo_Number_RE ORDER BY SmartList_ID DESC, GoTo_Number DESC END ELSE BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID BETWEEN @SmartList_ID_RS AND @SmartList_ID_RE AND GoTo_Number BETWEEN @GoTo_Number_RS AND @GoTo_Number_RE ORDER BY SmartList_ID DESC, GoTo_Number DESC END set nocount off  
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400N_1]  Script Date: 06/26/2011 11:55:05 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[zDP_SLB10400N_1] (@BS int, @SmartList_ID char(15), @GoTo_Number smallint, @SmartList_ID_RS char(15), @GoTo_Number_RS smallint, @SmartList_ID_RE char(15), @GoTo_Number_RE smallint) AS set nocount on IF @SmartList_ID_RS IS NULL BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE ( SmartList_ID = @SmartList_ID AND GoTo_Number > @GoTo_Number OR SmartList_ID > @SmartList_ID ) ORDER BY SmartList_ID ASC, GoTo_Number ASC END ELSE IF @SmartList_ID_RS = @SmartList_ID_RE BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID = @SmartList_ID_RS AND GoTo_Number BETWEEN @GoTo_Number_RS AND @GoTo_Number_RE AND ( SmartList_ID = @SmartList_ID AND GoTo_Number > @GoTo_Number OR SmartList_ID > @SmartList_ID ) ORDER BY SmartList_ID ASC, GoTo_Number ASC END ELSE BEGIN SELECT TOP 25 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID BETWEEN @SmartList_ID_RS AND @SmartList_ID_RE AND GoTo_Number BETWEEN @GoTo_Number_RS AND @GoTo_Number_RE AND ( SmartList_ID = @SmartList_ID AND GoTo_Number > @GoTo_Number OR SmartList_ID > @SmartList_ID ) ORDER BY SmartList_ID ASC, GoTo_Number ASC END set nocount off  
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400SD]  Script Date: 06/26/2011 11:55:05 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[zDP_SLB10400SD] (@SmartList_ID char(15), @GoTo_Number smallint, @RowsAffected int OUT, @RID int = 0, @TN char(99) = 'TN') AS set nocount on BEGIN IF @RID > 0 DELETE FROM .SLB10400 WHERE SmartList_ID = @SmartList_ID AND GoTo_Number = @GoTo_Number AND ( NOT EXISTS ( SELECT 1 FROM tempdb.dbo.DEX_LOCK WHERE row_id = @RID AND table_path_name = @TN ) ) ELSE DELETE FROM .SLB10400 WHERE SmartList_ID = @SmartList_ID AND GoTo_Number = @GoTo_Number SELECT @RowsAffected = @@rowcount END set nocount off  
GO

/****** Object: StoredProcedure [dbo].[zDP_SLB10400SS_1]  Script Date: 06/26/2011 11:55:05 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO

CREATE PROC [dbo].[zDP_SLB10400SS_1] (@SmartList_ID char(15), @GoTo_Number smallint) AS set nocount on SELECT TOP 1 SmartList_ID, GoTo_Number, GoTo_Description, GoTo_Type, PRODID, FORMNAME, fileName, Table_Number, Field_Number, PRCDNAME, Search_Date_Method_1, Search_Date_Method_2, Search_Date_Method_3, Search_Date_Method_4, Search_Dates_1, Search_Dates_2, Search_Dates_3, Search_Dates_4, Search_Field_Numbers_1, Search_Field_Numbers_2, Search_Field_Numbers_3, Search_Field_Numbers_4, Search_Strings_1, Search_Strings_2, Search_Strings_3, Search_Strings_4, Search_Times_1, Search_Times_2, Search_Times_3, Search_Times_4, Comparison_Types_1, Comparison_Types_2, Comparison_Types_3, Comparison_Types_4, Field_Comparison_1, Field_Comparison_2, Field_Comparison_3, Field_Comparison_4, SmartList_Field_1, SmartList_Field_2, SmartList_Field_3, SmartList_Field_4, Maximum_SmartList_Record, Search_Type, Search_Account_Indexes_1, Search_Account_Indexes_2, Search_Account_Indexes_3, Search_Account_Indexes_4, SmartList_Number, Search_Numbers_1, Search_Numbers_2, Search_Numbers_3, Search_Numbers_4, To_Search_Account_Indexe_1, To_Search_Account_Indexe_2, To_Search_Account_Indexe_3, To_Search_Account_Indexe_4, To_Search_Date_Method_1, To_Search_Date_Method_2, To_Search_Date_Method_3, To_Search_Date_Method_4, To_Search_Dates_1, To_Search_Dates_2, To_Search_Dates_3, To_Search_Dates_4, To_Search_Numbers_1, To_Search_Numbers_2, To_Search_Numbers_3, To_Search_Numbers_4, To_Search_Strings_1, To_Search_Strings_2, To_Search_Strings_3, To_Search_Strings_4, To_Search_Times_1, To_Search_Times_2, To_Search_Times_3, To_Search_Times_4, Extender_Type, Extender_ID, Drill_Down_ID, DEX_ROW_ID, TXTFIELD FROM .SLB10400 WHERE SmartList_ID = @SmartList_ID AND GoTo_Number = @GoTo_Number ORDER BY SmartList_ID ASC, GoTo_Number ASC set nocount off  
GO

GRANT EXECUTE ON [dbo].[zDP_SLB10400F_1] TO DYNGRP;
GO

GRANT EXECUTE ON [dbo].[zDP_SLB10400L_1] TO DYNGRP;
GO

GRANT EXECUTE ON [dbo].[zDP_SLB10400N_1] TO DYNGRP;
GO

GRANT EXECUTE ON [dbo].[zDP_SLB10400SD] TO DYNGRP;
GO

GRANT EXECUTE ON [dbo].[zDP_SLB10400SS_1] TO DYNGRP;

Knowing these issues will better prepare you for an upgrade and will allow you to establish a plan with corrective actions just in case you run into something similar. Don't forget to report any problems you run into to Microsoft as this will allow the Dev team to take action on their side as well.

Until next post!

MG.-
Mariano Gomez, MVP
IntellPartners, LLC
http://www.IntellPartners.com/

Comments

RS said…
We are upgrading from 9.0.0388 to 2010 version 11.0.1799. Is this still an issue within 1799 or has been addressed since?
November 10, 2011 at 8:39 PM
Post a Comment

Popular posts from this blog

DBMS: 12 Microsoft Dynamics GP: 0 error when updating to Microsoft Dynamics GP 2013 R2

Image
Hello all! It's been quite a while since I've actually written anything of remote interest to anyone who follows my blog, but at the same time, you will be pleased to know that I've been quite busy in the consulting front, with upgrades, server migrations, complex multi-instance postings involving 10's of thousands of transactions, wrapping up some Field Service Automation projects, and the list goes on and on. This time around I want to bring to the forefront, an issue I encountered updating  from Microsoft Dynamics GP 2013 SP2 to Microsoft Dynamics GP 2013 R2 plus the latest service pack. Background My client requested a server migration to a new environment where they wanted to deploy Dynamics GP 2013 R2 web client (plus the latest service pack) and upgrade their relational database management system to Microsoft SQL Server 2014. This is something I'm absolutely comfortable with (for the most part) given also that my client was sitting at Microsoft Dynamics
Read more

Cannot insert the value NULL into column 'CONTACT' error when clicking on Items List in Navigation Pane

Image
Moving on from my previous article on a similar subject - see Cannot insert the value NULL into column 'BASEUOFM' error when clicking on Items List in Navigation Pane , I recently came across this error,  Cannot insert the value NULL into column 'CONTACT' when clicking on the All Purchasing Transactions list under the Purchasing Navigation Pane option, after performing an upgrade from Microsoft Dynamics GP 9.0 to Microsoft Dynamics GP 2010 R2. All Purchasing Transactions list error - Purchasing Navigation List The name of the global temp table - in this case, tempdb.dbo.##2093338- varies in almost all cases, but the end result of the error is the same. The issue has been identified running Microsoft Dynamics GP 2010 RTM, SP1 or SP2. Upon further review, the issue is due to bad data in the Vendor ID (VENDORID) column in the Purchasing Receipt History table (POP30300). In summary, if you have a purchasing receipt with a blank vendor ID or a vendor ID that doe
Read more

How to uninstall Microsoft Dynamics GP 10.0 if you cannot uninstall it by using the "Add or Remove Programs" feature

INTRODUCTION This article describes how to uninstall an existing Microsoft Dynamics GP v10 system if you cannot uninstall it by using the Add or Remove Programs feature (or Programs and Features in Windows Vista) in Control Panel. It is recommended that you verify that you cannot uninstall by using Add or Remove Programs first. MORE INFORMATION First, verify that you cannot uninstall Microsoft Dynamics GP by using Add or Remove Programs (or Programs and Features) To verify that you cannot uninstall Microsoft Dynamics GP by using Add or Remove Programs (or Programs and Features in Windows Vista), follow these steps. For Windows XP or Windows Server 2003: 1. Click Start, click Run, type control appwiz.cpl in the Open box, and then press ENTER. 2. Click to select Microsoft Dynamics GP 10.0 product from the application list, and then click Remove. For Windows Vista: 1. Click Start, type Programs and Features in the Search box, and then press ENTER. 2. Click to select the product to be un
Read more