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/
0 comments:
Post a Comment