Friday, November 6, 2009

No Longer (Still) a Mystery: Invalid column name 'ACTNUMBR_6' when creating TWO / Fabrikam

(Update: Reader Jessica experienced the same issue, and provided feedback indicating that using Run As Administrator for GP Utilities should resolve this issue.)

So while installing GP 10 SP4 for the millionth time on a new development server, I ran into these four fantastically fun error message while trying to create the TWO company:

The following SQL statement produced an error:
declare @MXNUMSEG int, @Counter int, @AcctSQLCmd varchar(500), @StrLinked char(20), @StrNotLinked char(20) select @Counter = 0 set @AcctSQLCmd = space(1) set @StrLinked = '''Linked''' set @StrNotLinked = '''Not Linked''' select @MXNUMSEG = MXNUMSEG from DYNAMICS..SY003001 while @Counter <> @MXNUMSEG begin select @AcctSQLCmd = @AcctSQLCmd +',' +'GL00100.ACTNUMBR_' +ltrim(str(@Counter+1)) set @Counter = @Counter + 1 end exec('create view AAG00200FL as SELECT GL00100.ACTINDX'+@AcctSQLCmd + ', GL00100.ACTDESCR, GL00100.ACCTTYPE, GL00100.ACTIVE, AAG00200.aaAcctClassID, AAG00201.aaAccountClass

ERROR [Microsoft][SQL Native Client][SQL Server]Invalid column name 'ACTNUMBR_6'.

The following SQL statement produced an error:

ERROR [Microsoft][SQL Native Client][SQL Server]Cannot find the object 'AAG00200FL', because it does not exist or you do not have permission.

Whoa there! That's quite a mouthful.

Since I've had many, many, many random errors, crashes, and problems occur over the years when creating the TWO / Fabrikam database, when I see an error, I just roll my eyes, then re-launch GP Utilities, and have it resume the database setup, or recreate TWO. Well, I tried that approach, but after the 3rd time, it was clear that this wasn't your average TWO setup failure.

I tried resuming, I tried re-creating, and I tried deleting both the Dynamics and TWO databases and starting from scratch, to no avail. But despite what I tried, I noticed that at least the error was consistent.

As an additional test, I tried to create a new empty company to see if it was something about TWO, or if it was a more fundamental issue. But sure enough, even creating a new, blank company produced the same errors.

So I finally resorted to SQL Profiler so that I could capture the entire SQL statement that was causing the initial error. The relevant portion is:

while @Counter <> @MXNUMSEG

So the SQL first queries the number of GL segments from the SY003001 table, and then performs a loop based on the number of segments.

So why was the statement later failing to find the column ACTNUMBR_6? When I launched GP Utilities, I specified an account framework of 66 characters and 10 segments, so there should be nothing special about segment 6.

But when I queried GL00100, to my surprise, there were only 5 account segments in the TWO database, which explains why the statements were failing.

Since I've installed GP tons of times and never had this problem, I didn't know where to start. Several more times I tried deleting Dynamics and TWO then launching GP Utilities to set them back up. During those subsequent tries, I figured that there are only two things that could possibly be affecting the segment field setup and causing the issue.

First, I was entering the account framework, specifying max length of 66 characters and 10 segments. The second item was that I was electing to not sort by account segment. Neither of these settings should cause the install to fail, but I couldn't see what else it could be.

So I then tried 60 and 10, with no segment sorting. No luck. I then tried several permutations of 60 and 10 with and without sorting, and 66 and 10 with and without sorting.

At some point, after over a dozen attempts and having completely lost track of what I had already tried, for some reason, it suddenly worked. TWO setup completed successfully.

Puzzled, I tried deleting both Dynamics and TWO, and ran GP Utilities again with my original options: 66 and 10, with no segment sorting.

But, once again, the TWO setup was successful. No errors.

From this, I can only assume that the account framework and segment sorting may not have been the cause, or may have only been one of several factors that was contributing to the error.

I wish I knew what the cause was, but if I never see that error again, I won't complain either.

During my testing, I noticed something that gave me at least a tiny bit of insight into the process of how TWO is created, and what else may have contributed to the problem.

When the GL00100 table is first created in TWO, it contains only 5 segments. But a few steps later in the setup process, apparently a separate script alters the table to insert the additional 5 segment fields. Based on this sequence, one theory is that the alter script was failing, being skipped, or wasn't completing successfully for some reason--but no error message occurs. If that alter script is not run successfully, when GP Utilities tries to create some analytical accounting views, the create view scripts will fail.

And with that nail biter of a story, have a good weekend, and I'll hopefully see some of you at the Dynamics GP Technical Conference in Fargo next week!


Jessica said...

I think I might have solved the mystery. I had the same problem yesterday, and opened a case with Microsoft.

The DEX.INI file should change to a synchronization status of "FALSE" after the Dynamics database is built. However, in my case, it was not - although I was an administrator on the machine. This was a Windows Server 2008 environment.

I right-clicked on Utilities and "Ran as Administrator", and then the permissions were correct for the dex.ini to be updated, and we verified that it did update. It accepted the settings (10 segments) and created the database correctly.

Since I'm not a programmer, I can only guess that if the dex.ini doesn't get updated, it tries to create the databases with the default configuration (instead of the 66-10 that was specified.)

Hope that helps someone in the future...

Steve Endow said...

Hi Jessica,

Thank you for that information!

Very interesting. I was also the server admin on my 2008 machine, and I even installed GP in the C:\GP10 directory to avoid any issues with User Account Control (UAC) with the Program Files directory.

I'll keep this in mind on the next install in Server 2008.

Steve Endow said...

I just looked at the GP 10 installation documentation, and see that they do have a reference to using the Run as Administrator on Vista, which would seem to apply to Server 2008 as well (and possibly Windows 7):

To start Microsoft Dynamics GP Utilities, you must have appropriate user privileges.
Typically, this means being part of the Administrators group or the Power Users group on Windows XP. On Windows Vista, it means starting Microsoft Dynamics GP
Utilities with administrative privileges. For example, to start Microsoft Dynamics GP Utilities from the shortcut on the Start menu, select and right-click GP Utilities, and then select Run as Administrator. Refer to your operating system’s documentation for more information.