eConnect, GL Transactions & Multicurrency

What I’m posting today is hopefully of use to someone out there who runs into this same problem.  Admittedly it’s rare but it took me a while to find the answer.

In this case, “I” didn’t find the answer, I found it buried in a community forums post from Sivakumar, one of the Microsoft Dynamics GP MVPs.

Some Canadian Payroll & Project Accounting gotchas!

Recently, a client of mine experienced a couple of weird things when we started to configure another Dynamics GP company for them with Canadian Payroll and Project Accounting in action.  One of them is definitely something to look out for if you use the Canadian Payroll import processes; the second is more of a head-scratcher type of error where you don’t know what the real problem is.

I call these things “gotchas” – I’m not sure that translates into all languages but generally means to me at least, something that “GOT” me, that I wasn’t expecting or didn’t think to look out for.

Canadian Payroll issues and fixes

Third time’s the charm… 🙂  Gotta love computers, I’ve written this twice already and lost it completely, the draft didn’t even save.  Argh!

Today I have 2 issues to write about relating specifically to Canadian Payroll in Dynamics GP; things that a couple of my clients have recently run into that are fairly common and easy fixes.

Issue #1 is regarding stuck batches and the message “The batch is currently in use – Please wait until the other user is finished”.

Issue #2 is regarding permissions during the Generate Cheques routine and the message “There is an error in the Transmission Header – Incorrect Length”.

Issue 1: Stuck Batches

This first issue is incredibly easy to resolve yet one of those things that consultants are not looking for something this simple; the solution is different than for nearly every other module which requires a SQL script to be run.

The issue is trying to do something to a batch in Canadian Payroll and receiving the following message: “The batch is currently in use – Please wait until the other user is finished”.

Pic_CanPaystuckbatch

The solution is to open the Payroll Batch Setup window, select your batch and click the Reset Button.  This resets the batch’s status from In Process to Available, freeing it up for the user to proceed with it.  When you click Reset, you will get a message to confirm that says something like “are you sure you want to perform a general cleanup of the batch file?” and the answer is Yes.

Pic_CanPaybatch

 

Issue 2: Permissions

This second error, which occurs during the Generate Cheques process, is permissions related.  Of course you wouldn’t know that by the error message though!  We really should help those developers with improving the text on error messages some day!

The error is: “There is an error in the Transmission Header – Incorrect Length”.  Meaningful isn’t it?  Well, when this first happened, it was with a client who had just upgraded from GP9 to GP2010 and this was on processing their first payroll.  They didn’t get the error during their test upgrade so it was a little surprising.

Pic_CanPaypermissionerror

The solution ended up being that the user – now that GP2010 was installed on her own workstation with her normal permissions – didn’t have “write” permissions on the application directory (C:\Program Files\Microsoft Dynamics\GP2010).  During the Generate Cheques routine, for most Canadian Payroll users, there are two files being generated – a direct deposit file which you sent to the bank, and a Canada Savings Bond file which you send to the bank or manually transmit in other ways.

Years ago, both files defaulted to the application directory without giving the user the option where to save it; nor was there an option to configure a default location like most other file-based things in other modules.  After many complaints and suggestions, Microsoft finally fixed the issue on the Direct Deposit file, which now prompts the user for a location to save the output file.  Unfortunately they didn’t do the same for the CSB file and it continues to default to the application directory.

At this client, they switched to Windows 7 with the upgrade so the workstations were using default User Account Control settings.  A simple switch to give the users permission to create a file in that directory solved the problem.

Hopefully this helps someone out there when they get one or more of these error messages!

How secure are your passwords?

It seems I am starting every post recently with “it’s been a while since I last posted”!  I have probably reached the point that it goes without saying… lol

A recent scenario at a client got me to thinking about security – or lack thereof – that exists at some clients for various reasons as well as the flipside of ridiculous security that was going too far the other way.

The Background

I’ve been documenting the configuration of a third party reporting & budgeting tool one of my clients uses, since no one, including myself, knows any of the inner workings.

  • Does it require a service account to run?
  • How does it connect to the server?
  • How are user permissions managed?
  • What are the admin passwords?

It’s an odd situation really, no one knows much about this application and the person who was all gung ho about it to buy it and get it installed is no longer with the company.  Having spoken to this person before they left, they didn’t know either.  Swell!

I started down this road because they are in the middle of an upgrade and they are interested in scaling back on the use of ‘sa’ internally after the upgrade is complete.  The question came up: does XYZ product use ‘sa’ anywhere that we need to know about?  Great question!  It was bought and installed long before I was involved so I have no idea, and the VAR didn’t do the install so they have no idea either.  To compound it, the IT person who was working with the software vendor is no longer here, didn’t document anything and no one knows who did the original install.  It really sounds too goofy to be true but I’m quite sure this happens more often than people realize.

First lesson to share: SOMEONE write down the key ingredients to a new product install – that includes admin accounts, service accounts, etc.  Ideally your VAR is installing new products for or with you and they really should be responsible for documenting some of this for you – and yes, you have to pay for this to be done!  If you don’t want to pay for this kind of documentation, then someone needs to pay attention, ask questions and write things down.

Let the hacking begin

“Hacking” may be a bit extreme but what I had to do was go into any and all applications and configuration areas this suite of products had to determine what some of these settings were.  The documentation I had from the software vendor was sadly lacking; but of course they are not typically documenting the configuration settings of an install, at least not for an end user.  Those kinds of instructions the VARs may get or they may learn them via training on installation of the product.

In this case there were two separate products, one was a data warehouse kind of tool with a front end administration console and the second was a reporting tool.  Both have client pieces as well. Fortunately one of the users knew one of the admin passwords – which was the software company’s name (that’s original!).  The documentation from the software vendor did mention the default password for one of the admin tools was “admin” and turns out, that still worked.  Yikes… no mention of how to change this either.

There was a third admin user that no one seemed to know the password to, so a-hacking I went trying to guess.  It didn’t take long, I tried all of the common passwords people use, like password, access, no password etc..  No rocket scientist needed here.

Now that I was able to access some of the admin sides of these apps, I did find that “sa” was being used as a service account of sorts for the connection to the server and the other app used it’s own service account, and they used the ‘sa’ password for that service account.  Of course that wasn’t documented anywhere but through trial and error I tried the same password as the ‘sa’ account and sure enough that was it.  At least that was a smart idea.

My reaction

It really bugs me when people – VARs, Consultants, Software Providers, Clients – use such weak passwords for admin functions.  Sometimes it’s the clients’ request – they don’t want it too complicated.  Sometimes it’s the installers though – they just merrily install with the weak passwords without consulting the client on what to use.  In my opinion it’s our job as a consultant/VAR to recommend – if the client chooses not to follow our recommendations, that’s their option.

  • Don’t use obvious passwords on admin accounts or if you do, at least document how and where to change them.
  • Use strong passwords in particular on critical user accounts – ‘sa’ for instance.
  • Use specific user accounts as service accounts or default logins; don’t use ‘sa’ as a service account login!

I could go on and perhaps will in a future post.  Personally I am a believer that very few people should use the ‘sa’ account: nearly everything can be done with granting the necessary SQL permissions to DYNSA or a GP user account.  Installation routines are probably the one thing where ‘sa’ is still sometimes hard-coded into installation routines, even if DYNSA is granted the sysadmin SQL role, for instance.  Day to day admin that requires elevated SQL permissions?  I always recommend elevating DYNSA’s permissions and using that for the usual user setup, user access types of things and in some cases elevate specific GP user ID permissions in SQL as well where named users makes more sense.

The risk of going too far overboard is the opposite effect.  I had a client once where their audit recommendations were the use of 14-character passwords for everyone in the organization.  Yikes!  I’m ok with the recommendation I suppose, but the client does have a choice between ridiculous requirements and reasonable requirements.  In this case the client chose to follow the recommendations.  Guess what, EVERY user has their password written on a post-it note on their desk because 14 characters is too long to remember.  How secure is that?

Well the good news is now that we found the configuration areas we were able to secure things better and have a nice starting point for the IT department on the inner workings of this third party tool.

Excel Report Builder - bug in SQL views created

I'm not sure why, but I always seem to find the really obscure bugs in GP, the ones that really don't affect many others.  I guess it's called a gift. 🙂

Today, I found another one that affects GP10 (although apparently has been resolved – I can't verify that yet) and a slight variation of the same for GP2010.

The Issue

When creating an Excel Report Builder report and publishing it for multiple companies at once, the SQL views are not pointing at the correct database (or in some cases are only partially correct).

To reproduce this issue

In the enviroment I am testing, the client has GP10 but hasen't put in SP5 or anything newer so admittedly this is an out of date environment at the moment.  Microsoft confirmed it's a known issue that is resolved in SP5 (10.00.1701 or later).  I just don't have that handy to test.  For fun, I also tested this in GP2010, with SP2 (11.00.1752).

  1. Create an Excel Report Builder report – use anything (I used Year To Date Transaction Open).
  2. Set some fields for display.
  3. On the Account Index field, use the field options to set “Show Account Number” instead of account index.
  4. Use the Summary button (GP10) or Options button (GP2010) to select multiple companies.  This doesn't matter if you consolidate into one workbook or not (I didn't).
  5. Publish.

Now, take a look at the SQL view it creates, which will be prefixed, if you didn't change it, with “erb”.  I use the right click Script View As context menu to display it in a query editor window in SQL Mgmt Studio.  Look for the “FROM” clause(s).

The results

On GP10: the company I am logged into, it's SQL views were correct; however the SQL views on the other databases I published to are selecting “from” the same logged-into company database.  Everything else appears as normal but of course when the user runs the Excel Report, it will be pulling data from a different company than they want.

On GP2010: the only part that is wrong is the select script that is to obtain the account number for a given account index.  Look for 2 (or more depending on what you have created) “select” scripts.  One of them is “select top 1 ACTNUMST from [XXXX]..GL00105 where ACTINDEX = T1.[ACTINDX]”.  The XXXX in this case is pointing at the wrong company database.  So, once again, the users would be pulling a report, and it would be displaying Account Numbers from another company's database (or erroring I suppose if the account index does not exist in the other company db).

Quick Fix

The quick fix is to alter the SQL view directly by changing the referenced database to the correct one.  I haven't tested any further so beware if you make the change, and then republish the report for some reason – I don't know if it “re-creates” the SQL view at that time or not. 

What's next

I've submitted both of these to Microsoft.  The GP10 one they can't reproduce on SP5 so that is likely resolved.  The GP2010 variation they haven't responded to me yet, they are still testing it.  I'll try to post updates as I get them on this!

Client Updates & Archiving old service packs

What usually is a typical service pack install client visit started off on the wrong foot.  I am installing SP2 for GP2010 plus related hotfixes for a client, and the last time I was here was in January for the year end tax updates.  While I was doing my usual preparation, before I even started the process of installing anything, users started reporting warning messages keeping them out of GP.

The Message

Users started getting a message when logging into any company in Dynamics GP:

“An available update may be required for your computer, but the update process couldn’t verify information. Contact your system administrator for additional information”

At the time all I was doing was downloading the service packs I needed from Partnersource and third party websites.

The Cause

I had moved previous service pack files into an “archives” folder, to keep the downloads clean and easy to identify, a process I have always followed without issue.  What I forgot was in January I had attempted to use the Client Updates feature in GP – and at the time it didn’t work, and we didn’t waste time troubleshooting it.  However, we didn’t remove the entry in the client updates window – which was pointing to the service packs I had just moved to another location.

The Takeaway

If you use the Client Updates feature, and typically manage your download or install folders by archiving old files you don’t need any longer, do this:

  1. Remove the reference in the Client Updates to this file.  (In my case, since I don’t want it used anymore, I deleted it)
  2. Then, archive any files you wish to move to another location.

The same thing would apply if you want to rename your folder structure – the client updates will not find the previous path.

Additional Information

At least one other blogger has written about this in the past and has some additional information and options if you don’t want to undo your archiving just to remove a reference in Client Updates.  See Mohammad Daoud’s post here.

Fun with changing a server name!

I write this with a little embarrassment at the solution to a problem I had.  Earlier this week I renamed a server I’m using for GP2010 to prepare for a new network and improve my network naming conventions.  I’ve changed server names before at clients and the steps and places you need to make updates are pretty straight forward, at least for vanilla GP.  There are other complications when you get into web services and other things where the configuration is not strictly within the application.

The Issue

I changed the server name and updated the obvious things but was still unable to log in to GP.  I could log into SQL Server Management Studio just fine but couldn’t log into GP, even as ‘sa’.

The Solution

For reference, here are what I call the obvious things to check and rename if necessary.  I’m not including updating FRx or web products, just plain ol’ GP.  If you use IP addresses to reference pathnames then often you are ok unless the IP address has changed.  In my case I use UNC pathnames and had several places to update.

  • Dynamics.set launch file – update the pathnames to shared reports or forms if applicable
  • Dex.ini configuration file – update the pathnames to other shared items if applicable, like OLE notes, Letters etc.
  • SQL server @@servername update – you need to run the relevant “add” and “drop” command to change the server name
  • ODBC datasource – update the SQL server name, if using UNC pathnames

I changed all of the above, and continually banged my head against the wall trying to sort out why I still was unable to log in.

The “DOH” Moment!

I’m running a 64-bit operating system (Windows 7 in this particular machine’s case).  I completely forgot that the ODBC shortcut you get to via Administrative Tools is 64-bit only.  What confused me was there was a “Dynamics GP 2010” DSN already listed so naturally, I updated it, but still couldn’t log in.

GP requires a 32-bit ODBC DSN, and I had to open the 32-bit version of the ODBC setup window.  If you’re reading this far and looking for this as well, on Windows 7 you can find it here:

C:WindowsSysWOW64 and it’s called odbcad32.exe.

Thank me later!  🙂

I’m back with a bug!

At long last I’m back, and aim to get back to regular weekly (if not more frequent) blogging!

First topic, last week I installed GP2010 SP2 (aka R2) at a client site and all was fine.  That is, until I was doing some unrelated work in Report Writer, and was getting an error message when coming back into Dynamics GP.

I had some trouble finding this specific error but did find a post on the partner technical forums with the same problem, from a couple of months ago, that had gone dormant.  I updated the post with my information, adding to the original poster’s info, and it turns out it is indeed a bug.

The scenarios

The errors are specific to the following situation, in GP2010 R2 (11.00.1752)

  • You have Extender installed
  • You go into Report Writer or Modifer during your session of being logged into GP
  • You go back from RW or Modifier into Dynamics GP and receive one if not two errors
  • You get a similar error when exiting Dynamics GP – but ONLY if you had been in RW or Modifier during your GP session.  (No error if you never go into RW or Modifier)

The errors

The first error is pictured below (top is the error, bottom is scrolling down to the rest of the text).  It is an unhandled script exception error:

“Illegal Address for field ‘[Not Found]’ in script ‘Unregister_Triggers’.”

Scrolling down further is:

“EXCEPTION_CLASS_SCRIPT_ADDRESSING”

and

“SCRIPT_CMD_LOAD_ATSI7”.

Pic_extenderGP2010error1

The second error occurs immediately after the first one AND when you exit Dynamics GP if you had been in RW or Modifier during your session.  Another unhandled script exception.

“Object has no reference.”

Scrolling down further

“EXCEPTION_CLASS_SCRIPT_BAD_PARAM”

and

“SCRIPT_CMD_DEREF_TABLE”.

Pic_extenderGP2010error2

The impact

There appears to be no impact on the functioning of Extender or Dynamics GP.  I didn’t do a TON of testing but what I did test, nothing seemed impacted by the errors.  The fact that there are no errors unless you go in and out of Report Writer or Modifier tell me they are not errors in functioning of the modules themselves but something else.

The resolution

Microsoft has written this up as a bug and hopefully it would get resolved in SP3 assuming they can determine the cause.

UPDATE: December 6, 2011 – this is resolved in KB2599505 (aka US Payroll 2011 Year End Update service pack)

The Support Debugging Tool is my new best friend

I have known about the Support Debugging Tool for a while but have never had an occasion to install and use it at a client location.  

I took advantage of a recent client upgrade from GP9 to GP2010 to install it on all of the workstations while we rolled out GP2010.  It has already paid dividends!

The Issue

One user was getting an error on posting “Not privileged to run this report” – when she posted Receivings Transaction Entry batches.  That's a standard error, and typically I haven't found it tough to resolve.  Since it was received on posting, I did what I normally do: I went into a test company and posted a batch with a POWERUSER user to see what posting reports were turned on.  The result however was I received the same 3 posting journals that this user did, except I did not get an error message.  Hmmm…. 

SDT to the rescue

I turned to the Support Debugging Tool's Security Profiler feature and it was a very easy and quick way to find the problem.  The steps were as follows:

  • Enable the Security Profiler on the user's workstation
  • The user posted a test batch to get to the point of the permission error
  • She exported the information to the shared drive for debugging information
  • I logged on as 'sa' and imported the information
  • The SDT showed me it was the report “POP Backordered Items Received” that she did not have access to.
  • Opened security via the shortcut, made the necessary change, problem solved!

Feedback

There are a TON of things in this tool I don't know how to use effectively yet and will be looking at other features I can take advantage of.

One very cool thing is the colour customization of the GP windows which this client is using for test companies.  They wanted to ensure the users don't accidentally forget they are in a test company and post something they want to be “live” data.

The second very cool thing is the option to rename the window titles when GP is minimized.  In this case, the power users tend to have multiple instances of GP launched at once and with GP2010 having the user interface display all windows individually, their taskbar gets busy in a hurry.  They loved this feature to quickly see which minimized windows are which user / company logged in.

If you haven't tried this, check out David Musgrave's site for the Support Debugging Tool Portal for more information and lots of articles and information.

If you are already using this, Build 14 was just released – check it out and update to the latest build!

Issues with GP2010 and uninstalling

I ran into a difficult situation at a client where we were unable to edit (add new features) to GP2010, unable to uninstall or repair GP2010 and unable to launch GP2010 because of dictionary version mismatches.  If you ever have issues finding the references in the registry to troubleshoot installations I hope this may help, that is where I ended up in my quest.

The issues were various but the short story is:

  • It started with being unable to launch Dynamics GP Utilities (“Problem ascertaining version” error)
  • Product mismatch was Canpay.dic file – after installing SP1, dictionary still said it was code 11.0.1247 (RTM).  Re-installing SP1 made no difference.  All other dictionary files were at proper SP1 version #.  Odd.
  • Next tried to remove Canadian Payroll from the dynamics.set file and then re-run setup to “add” that product back.  Problem: the Change program feature under Add/Remove programs is blank – usually it’s a product list for additional products.
  • Next step – let’s uninstall and reinstall the client entirely.  Should be simple, right?  Nope! Uninstall gives me “Object reference not set to an instance of an object”.
  • Okay, now I’m getting annoyed and frustrated, nothing seems to work, I’m going in circles!  Hold on, time to re-group, we’ve been in difficult situations before, let’s dig in and roll up our sleeves.  Time to search for more solutions!  (I’m not sure that I outwardly appeared that positive in the middle of this but I do enjoy a challenge … hehehe).

Uninstalling GP2010

First I found Mariano Gomez’ post How to Uninstall GP10 for instances where add/remove programs does not work.  Even though I was using GP2010, not GP10, the references were basically the same.

This gave me a lot of the information I have here and helped a ton.

A second completely non-Dynamics-related article here helped me find the other references in the registry including a tip in a user’s comment about 32bit apps on a 64bit machine.

In my specific issue I was getting errors trying to either Repair or Remove using the add/remove programs (or the appwiz.cpl shortcut).  The error was “Object reference not set to an instance of an object”.  I was unable ultimately to even run through Mariano’s steps as I received the same error when uninstalling the msi in the Installer folder(s).

To the registry!

I’m not a big fan of hacking into a registry – it’s risky, there are lots of ways for this to go wrong, and in this case, I was also working remotely so I needed to be ultra careful, backing up anything and everything, documenting every step so I could go back if I needed to.

Remember: always export the keys before deleting anything…

My environment

The server is Windows Server 2008 R2 Standard, 64bit.  SQL version SQL2005.  GP2010 SP1.

What worked for me

Take this for what it’s worth – I tried a lot of things (removing entries in the registry) and to be perfectly honest, once I got to the “search” stage on the registry, I didn’t know exactly which specific registry key was “the” one that freed me from my little black hole as I kept the search going to find as much as I could before trying to install again.

Also, in all of this, my DATA was fine – there were no issues from any other workstation, the data was fine, all products were at their proper upgraded version and the system was not down.  It was the only client install on the server for me that was not working.

  1. Using Mariano Gomez’s post steps, I first re-tried to uninstall by using Start – Run – appwiz.cpl and received the same “Object Reference not set to an instance of an object” error.
  2. Looked at the Installer folder.  I had during my troubleshooting steps installed GP2010 twice and therefore had two instances to deal with removing.  Right clicking on the installer (using the subject tip Mariano had) returned the same error message as in #1.
  3. Started looking in the registry to manually remove the references and application directories etc.  After thinking I got all of the references, I used CCleaner (free program available online) to clean the registry items that it found left behind.  Rebooted.  At this point the install (fresh install) failed with another error (System.NullReferenceException: Object Reference is not set to an instance of an object at Microsoft.Dynamics.GP.Deployment.WindowsInstaller.DynamicsGP.Installation…. long text string of similar entries.)
  4. Back to the registry, I searched for keywords and remove other references not so obviously placed.
  5. After this, I was finally able to re-install without issue and without any references to a prior install of GP2010.  This time around I used the full installation package of GP2010 with SP1 included.

Registry references

The following were the areas I looked at in the registry (all tips from the two posts above):

  • HKLM / Software / Microsoft / BusinessSolutions – this contained references to how many instances were installed, what they were called, where they were located and which products were installed on each instance.  This one helped me in an unexpected way: I ran into a problem where one product kept re-appearing in the dynamics.set file after installing a service pack (in this case, Electronic Reconcile).  Initially that product was installed, then removed from the dynamics.set file.  However, the registry has it as an installed product on that instance so each time the service pack was ran, it was updated and reinserted in the dynamics.set file.
  • HKLM / Software / Microsoft / Windows / CurrentVersion / Uninstall – this contained (in my environment) 64bit apps only, and GP2010 was not in there.  Dexterity Components were though.
  • HKLM / Software / WOW6432Node / Microsoft / Windows / CurrentVersion / Uninstall – this contained 32bit apps including GP2010.  This was the magic registry for me since I would not have guessed to look here for anything initially.
  • At this point I had stopped, thinking I had captured all the references, backed them up and removed them.  When I realized I was missing references, I searched the following terms: Dynamics, Business Solutions and Great Plains.  I only searched HKLM downwards.  When the search started to get bogged down into UserData S-1-5-18 Components, I skipped down to the last subkey of this section and then continued the search, skipping that area.

Application Folders & References

Here was where I found references in windows explorer to GP2010, dexterity etc. or install files and removed what was no longer necessary.

  • Installer folders (Start – Run – Installer)
  • Common Program Files (Start – Run – %commonprogramfiles%)
  • Microsoft Shared (same folder as above, subfolder called Microsoft Shared)
  • Application directory (Program Files – Microsoft Dynamics – GP)
  • Downloaded installations folder (Windows – Downloaded Installations)

 

While I was in the middle of my little black hole, I was searching for a lot of the error messages I was coming across but not having much luck.  Hopefully this will help someone narrow down their problem and get them out of their black hole!