Business Portal Query Error – Cannot find table 0

One of my users ran into this message this past week while using the Accounts page in the Financial Center of Business Portal 4.0.  When selecting a certain account in the Account List web part, the Account Balance pane showed no results, with the message “Query error” at the bottom of the window.  Click on the “Query error” and then Details gives the following message:

 The error only occurs on one account, only appears in the Account Balance web part, however you have to refresh the entire page before the various web part will function again.  Since it was only happening for the one account, I decided it had to be data-related, and began poking around in the various GL tables in this company’s database.  Since the only web part that returned an error was the Account Balance web part, I started with the Account Current Summary Master table, GL10110, and quickly found what I was looking for when I looked at all records for the problem account.   There was one record where the Year1 field was 0, and the PeriodID was also 0, and the amounts were zero, so the record was clearly invalid.  I found a similar record for another account and tested that account in the portal and got the same error.  I backed up the GL10100 table and removed the records in question, and the error went away. 

You cannot open Microsoft Dynamics GP now because another user is entering Multicurrency access information.

Other than having to define a functional currency to use econnect, I've never had the need to work with multiple currencies in GP until yesterday.  A colleague and I had defined the Singapore dollar and were in the process of granting the company access to the currency and to the exchange tables, when GP appeared to hang, giving the infamous "Not Responding" status.  We closed GP and attempted to get back in, however we were greeted with the message below:

 

I'd never bumped into this message before and my Google searches didn't seem to be bringing up anything very promising.  We removed our login from the ACTIVITY table, but still got the message.  We checked the usual suspects - SY00500, SY00800, SY00801, but still no luck.  We had 25 other users in the system and getting them all out wasn't an option, so we hadn't cleared the ACTIVITY, SY00800, SY00801 tables.  After sweating a bit, as users were unable to login to any company in GP, I found the line I was looking for in the ACTIVITY table:

We deleted the record where the USERID field was MC Access Setup, and users were able to login to GP again.  

New Season

After 11 years, I've decided that it's time for me to move on from my position at my current employer and try something else. I'll be taking a position with a GP end-user, meaning instead of 100 different customers, I'll have 1 big customer, something I've often thought would be great to get a chance to do.  To really get to know the details of the entire implementation, optimize it, and make it my own has great appeal to me at this stage in my career.  I'll definitely miss the relationships I've built with the many customers I've had the opportunity to work with, but as everyone told me, "You'll know when it's time", and it's definitely time for me to go.  

I'm extremely grateful to have become a member of this awesome Dynamics GP community, and glad that I have had a chance to contribute.  Once things have settled down I hope to get back making regular posts on SQL and Dynamics GP, though things may take a turn toward more personal content, tech and gadget-related content, at least for a while.  Thanks to all for the support and encouragement.

Excellent SQL Server Backup and Maintenance Solution

One of the first things I have to do after I’ve installed Dynamics GP and created the DYNAMICS, TWO and company databases, is setup jobs to backup and optimize those databases.  The best method I’ve found to do that is a free solution developed by Ola Hallengren that can be found here.  This solution received the 2010 Editor’s Choice Bronze Award for best Free SQL Tool, while the SQL Server Community gave it the Gold.  After a previous write-up of this solution Ola contacted me directly asking for my feedback, and he has been in touch several times since – he clearly cares about his solution and makes regular improvements as SQL Server features are added or changed. 

The solution can be installed by downloading and running a single sql script – direct link here.  Upon opening the script in SQL Management Studio, I typically make two changes.  First I change the value of the @BackupDirectory parameter, highlighted below, to point to the location I want the backups stored.  The backup job will create a folder with the server name in this folder, with folders for each database below that, followed by folders for each of the backup types – full, differential, and log.

The second change I make is that I like to keep 3 days worth of backups on disk if possible, and the scripts defaults to cleaning up backups older than 24 hours, so I like to change that to 72 hours.  This can certainly be done afterwards by editing the job step but I like to do it up front.  I usually do a quick find and replace operation on “24, ” replacing with 72. 

After the replace is completed, I run the script and the highlighted jobs below are created.  From there I open each job, set the desired schedule for each one, and I’m done.

I use this script because I want consistently configured backup and database maintenance jobs on all of the SQL Servers I manage for my customers.  It takes 10-15 minutes to configure and I love that it’s smart enough to not throw an error on the whole transaction log job if someone creates a new company and the recovery is still set to Simple – it skips that database and moves on to the next one.  It is also much smarter then the SQL Server Maintenance Plans about whether it rebuilds or reorganizes the databases indexes.  Visit Ola’s site, http://ola.hallengren.com to read more about the solution or view the documentation.   

GP 2010 SP1 Installation Package doesn’t apply SP1

Here’s an update to my previous post, as the GP 2010 installation media has been updated to include SP1 for GP 2010.  Installing GP from the media does include SP1, however when you create an installation package from this new release, SP1 does not get applied to the client install as it should.  I’ve confirmed this as a known issue with Microsoft support and it is being worked on.  In the meantime, just manually apply SP1 from the Updates folder within your client install package.