Welcome MVP Jivtesh Singh!

I'll start this post by saying... it was just about darn time!

My good friend Jivtesh Singh has been working tirelessly for the Microsoft Dynamics GP community worldwide and I don't think he often gets too much credit for it. I came in touch with Jivtesh probably around April of 2,009 when he approached me on the subject of becoming an MVP. Back then I remember telling him there was no quick way or magic formula to become an MVP, but rather to begin working with and for the Microsoft Dynamics GP community of users and partners to provide sound solutions to their problems. And so he set off to do.

In August of 2,010 and after a lot of back and forth between Jivtesh, David, and I, GPWindow was born - see Jivtesh Singh dissects the Microsoft Dynamics GP Community for more information. GPWindow has become the de facto destination for everything Microsoft Dynamics GP: articles, blogs, development tips, customizations, sample code, and the likes of thousands of other little Microsoft Dynamics GP secrets provided by experts and bloggers, all categorized like a big online library at your fingertips.

Later on, Jivtesh released the first online search tool for Microsoft Dynamics GP windows and forms. Simply put, you begin to type some window name (complete or partial) and GP window will narrow down the navigation path to that window - once again, online, from your browser!

Jivtesh also has his own blog, About Dynamics, Development, and Life which he also keep up with. For all these accomplishments, Jivtesh deserves the MVP award. Keep up the good work!

Until next post!

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

Read More

4th consecutive year as a Microsoft Dynamics GP MVP!

It's that time of the year again... every July 1st I find out whether I continue to keep my MVP stripes or... not! The good folks at Microsoft have felt once more that my contributions to the Microsoft Dynamics GP community in general have made me worthy of such honor, so it's with great joy that I announce I get to keep my award for one more year starting today, making it the 4th consecutive year of such privilege.

NOTE: The record for consecutive years in our track is currently held by MVP Victoria Yudin, with 7 consecutive years.

Also, I must congratulate MVP Frank Hamelly for being reawarded. Frank's contributions have also earned him the right of passage.

So you ask, "what does a Microsoft MVP do?" Here is just a few things I did since last July:

• Published 186 blog articles on this site
• Continue to prop up The Dynamics GP Blogster on Facebook, now with 230 active followers (join here)
• Answered over 2500 questions on the various user newsgroups and forums
• Answered over 1450 questions on the Online Partner Technical Community
• Started a monthly specialized column on Microsoft Dynamics Community, IMHO with The Dynamics GP Blogster - 6 articles published so far
• Serve as Advisor on MSDynamicsWorld's Editorial Advisory Board
• Presented at the Microsoft Dynamics GP Technical Conference 2011 (more than 80 hours invested on prep time)
• Presented at GPUG DayONE event
• Presented at the Microsoft Convergence Atlanta 2011 (more than 100 hours invested in prep time)
• Championed the Microsoft Dynamics GP MVP Product Interaction Group monthly discussions with the Microsoft Dynamics GP Product Management Team.
• Presented at the MSDynamicsWorld Decisions 2011 Spring virtual conference
• Continue to provide features' input and beta testing support for the Support Debugging Tool.

The year is still young and you can expect to see me around at a few upcoming events and training sessions, including the GPUG Summit in Las Vegas, Nevada this coming November.

For more information on the Microsoft MVP Award Program, visit the MVP Award Program Blog site.

Until next post!

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

Read More

Support Debugging Tool Build 15 now released

These are exciting times... the Support Debugging Tool Build 15 has been released! If you attended our session, CSGP014-R2 Administering Microsoft Dynamics GP Like a Pro with the Support Debugging Tool during Convergence Atlanta 2011, you may recall that the features David Musgrave (author of the Support Debugging Tool) and I presented were all part of the Build 15 beta release at the time. Since then, the Support Debugging Tool has been propped up with a number of enhancements and bug fixes that make the wait while worth.

Support Debugging Tool Build 15 - UI Facelift

Take a look at David Musgrave's release article, Support Debugging Tool Build 15 released, for a complete list of fixes, enhancements, performance improvements, usability and  features included in the new build.

Build 15 is only available for Microsoft Dynamics GP 10.0, Microsoft Dynamics GP 2010, and Microsoft Dynamics GP 2010 R2. You can download Build 15 at:

Support Debugging Tool for Microsoft Dynamics GP 10.0
Support Debugging Tool for Microsoft Dynamics GP 2010 (v11.0)

Also, I will be writing in the coming days on some of the features I personally suggested and that are now a part of the Support Debugging Tool.

Until next post!

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

Read More

IM - Cannot Open Database error when running eConnect adapter integration

Here comes one of those puzzling errors that can have you spinning your wheels for a bit until you resolve it. Just recently, I was helping a client building a Fixed Assets integration. The requirement called for importing a number of asset records and have a method that could be reused as new assets are added to the organization's 15 different locations... that's the easy part!

We were getting ready to run our first integration when the dreadful error came up:

ERROR: System.Data.SqlClient.SqlError: Cannot open database "XYZAB" requested by the login. The login failed"

Of course, here comes the troubleshooting aspect of the process. With the client running eConnect 10, there are a few places to look for issues that may trigger this error:

Component Services:

The most likely cause of this error message is a problem with the eConnect COM+ configuration. Open the Component Services by clicking Start > Run and type in dcomcnfg.

Expand Component Services > Computers > My Computer > COM+ Applications. Right click on "eConnect 10 for Microsoft Dynamics GP" and choose Start. If you receive an error, that means the COM+ component is not configured properly.

To configure, right-click and choose Properties. Click on the Identity tab and make sure that the domain user account configured here is setup in SQL server and is at least a member of the DYNGRP role for the company and dynamics databases.

If you have System Account selected, select This User instead and enter your Domain\User account and password.

SQL Sercurity

Open Microsoft SQL Server Management Studio (SSMS) and in Security verify that you have a SQL user that is the same Domain\User and the user is part of the DYNGRP role.

That should do!

Until next post!

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

Read More

Decisions Spring 2011 now available on-demand

Missed out on any of the MSDynamicsWorld Decisions Spring 2011 presentations? I know I did! Now you can review each and every single one from the comfort of your lazy boy, by clicking on the following link:

Decisions Spring 2011 On-demand


You will need a user ID and password to access the site, but it's never too late to register. I had the pleasure to have Errol Schoenfish, Director of Product Management, Microsoft Dynamics GP taking questions at the end of the presentation with me. Please go back and enjoy the session.

For presentation materials, see my previous post MSDynamicsWorld's Decisions Spring 2011 virtual conference Day 2.

Until next post!

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

Read More

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/

Read More

Passing arrays as parameters to functions or procedures in Dexterity

First, some theory...

The individual pieces that make up an array are called elements. The elements are numbered from 1 to the size of the array. Arrays can have up to 32,767 elements. The number used to indicate a specific element of the array is called the array index. A specific element of the array is referenced in sanScript using its corresponding array index.

Typically, an array can be used in place of several fields that have the same data type and will be storing similar information. For example, if an application keeps track of monthly sales for a year, one monthly sales array field with an array size of 12 could be used to store this information instead of 12 separate fields.

Dexterity applications can use array fields [in tables] and array local variables [in memory]. Array fields are created in the Field Definition window by setting the Array Size field to the size of the array. Array local variables are created by including the size of the array in brackets – [ ] – following the local variable name. When creating local arrays, the array size must be a constant expression. Array sizes can’t be set at runtime.

To access the elements of an array from within a script, simply use the name of the array and add the index number of the element you want to reference. Be sure the index number is included in square brackets after the name of the array and before the qualifier. The following example sets the third element of the Quarterly Sales array field (corresponding to the third quarter) to the sum of the monthly sales for July, August and September.

'Quarterly Sales'[3] = 'Jul Sales' + 'Aug Sales' + 'Sep Sales';

The following example uses a local array field with five elements to act as status flags for the script. The for loop initializes the flags to false.

local boolean status[5];
local integer i;

for i = 1 to 5 do
  status[i] = false;
end for;

Now to the problem...

A couple days aback a developer reported issues when passing an array as a parameter to a Dex global procedure - won't have been any different for a function either. Her code was as follows - I say was as I assumed it has been fixed since.

local currency l_plan_total, l_month_totals[13];

call TAS_Calculate_Plan_Totals,
  'Customer Number' of table TAS_Plan_Header,
  Year of table TAS_Plan_Header,
  TAS_Plan_Status of table TAS_Plan_Header,
  'Revision Number' of table TAS_Plan_Header,
  l_plan_total,
  l_month_totals[13];

The procedure signature is as follows:

{PROC: TAS_Calculate_Plan_Totals}

in string i_customer;
in integer i_year, i_status;
in long i_revision;
out currency o_total;
out currency o_month[13];

local currency l_month[13];

Things to note... in the calling script, the developer declares an array of monthly totals of 13 elements, then calls the TAS_Calculate_Plan_Totals passing the array as a parameter. The procedure is pretty straight forward and uses an out parameter to pass-back whatever results the developer will calculate and put into each element of the array.

However, when the code is compiled, the developer receives the error type incompatibility on parameter #6. That happens to be the array parameter in the call to the global proc.

Taking a closer look, the developer is passing l_month_totals[13] to in the procedure call. Since, the parameter in the procedure has been declared as an array, l_month_totals[13] is being interpreted by the Dexterity compiler as the currency value represented by the element with an index of 13. In other words, just the plain currency value and not the array as a whole. To correct the code, the developer needed to remove the brackets from the call, just passing l_month_totals, as follows:

local currency l_plan_total, l_month_totals[13];

call TAS_Calculate_Plan_Totals,
  'Customer Number' of table TAS_Plan_Header,
  Year of table TAS_Plan_Header,
  TAS_Plan_Status of table TAS_Plan_Header,
  'Revision Number' of table TAS_Plan_Header,
  l_plan_total,
  l_month_totals;

{PROC: TAS_Calculate_Plan_Totals}

in string i_customer;
in integer i_year, i_status;
in long i_revision;
out currency o_total;
out currency o_month[13];
local currency l_month[13];

I must say that the developer in question is an experienced developer, but at the same time, I can see how this can become confusing even for the experts. I personally believe that a better approach for this call - talking to you Dex compiler gurus in Fargo - would have been as follows:

call TAS_Calculate_Plan_Totals,
  'Customer Number' of table TAS_Plan_Header,
  Year of table TAS_Plan_Header,
  TAS_Plan_Status of table TAS_Plan_Header,
  'Revision Number' of table TAS_Plan_Header,
  l_plan_total,
  l_month_totals[];

Note the use of the brackets as a way to differentiate the array variable from, say, the other currency variable just above it. On that same note, I have a long list of suggested compiler changes for Dexterity, but I am sure these are not a priority at this point.

Until next post!

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

Read More