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:
GRANT SELECT , INSERT , DELETE , UPDATE ON [dbo].[AAG00200FL] TO [DYNGRP]

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:

select @MXNUMSEG = MXNUMSEG from DYNAMICS..SY003001
while @Counter <> @MXNUMSEG
begin


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!