FP: Couldn’t close table! Dynamics GP Error Solution

I talked before in a previous blog post about the “syContentPageXMLCache cannot find table” error. This and its cousin the “FP: Couldn’t close table!” error are caused by something severing long lived SQL connections between the client and server.

FP: Couldn't close table! 

This might be due to networking issues like bad routing, physical faults with NIC cards, faulty Ethernet cables or connectors, servers going to sleep, connectivity problems (WIFI) or many other potential causes.

The problem

I found myself involved in this today. A new employee started, since they have been working for us, every time the Dynamics GP application is closed at the end of the day (or sooner) they get the FP Couldn’t close table error. They have also been getting other SQL related errors.

IT support had already tried:

  • New GP user ID for the user
  • Rebuilding the PC from the standard image
  • Swapping the newer PC hardware the new user got, for the same hardware the rest of the users are using, involving another new image rebuild.
  • Deleting the user’s AD profile and rebuilding it.
  • Swapping the Ethernet cables
  • Swapping to another wall port of a user that is known to work, also on another network switch
  • Checking power saving sleep options on the NIC

None of the above has stopped the issue occurring. It came to a head when a quotation was entered, that ended up pulling the wrong currency for pricing (as I suspect the SQL connection broke under the hood). I was stumped as to what the issue could possibly be bearing in mind what had already been tried.

Resolving the issue

The user was instructed to email me the screenshots of the errors, the second they happened. Not long after, I came back from my lunch to see an email come in. Attached was the stereotypical errors caused by connection loss.

I connected to the event log on the offending machine, looked at recent history and found the problem.

Event viewer shows Kernel Power event shortly before problem

Although the power settings in windows 10 and it looked ok from the top level screen. When drilling into “Change advanced power settings” and checking through all the options, the “Allow hybrid sleep” setting was set to ON. Looking at the other machines in the area, they all were set to OFF.

Advanced power settings

So my current working assumption is that the user had come back from lunch, during which time the machine had snoozed, causing the SQL connection to drop, with the following errors on resuming using GP:

An unknown SQL error occurred.

A SQL network connection error occurred and your connection was cleared.

David Musgrave has in the past posted some more information on these kinds of issues that are worth a read:

TCP Chimney Setting and SQL Server Error: TCP Provider: An existing connection was forcibly closed by the remote host

More on SQL Server Connection issues with Microsoft Dynamics GP

What do you call that window GP?

Steve Endow mentioned on twitter the other day about the naming of objects in GP when programming. The inconstancies are astounding, I have to agree, you can spend half an hour trying to determine if the object named something almost like what you want is actually the object you want or not, then two mins to actually write the mod! The fact you end up using a tool to work out what the object name of the window is in the code, well that just tells the story.

api naming dynamics gp steve endow

What Steve might not realise is that it is even worse if you speak English rather than American. See that window he’s looking at in the tweet, look at it on my screen…

puchaseinv

So we have Enquiry rather than Inquiry, no big deal? Well mostly no we learn the translations, but it confusing to the new developers who have to get used to this translations issue. Sometimes the translated terms are not as obvious as this. However even this would be an issue if you were looking at the object explorer ordered alphabetically or doing a search for the object.

There  is fun though with Debtors and Customers. I still don’t really know why we can’t have customers too, for our users it would make more sense than the accounting term debtors!

Steve kindly furnished me with this screen shot of an American GP customer window.

GP2015CustMaint

Compare that to what we see…with a English Debtor Window. These are the same windows, replacing customer everywhere, now imagine searching for this in visual studio, or a field called debtor id…

dmuk

In visual studio when developing addins we have RMCustomerMaintenance, good job I didn’t go searching for debtor when looking for the object, eh?

customerobject

So I conclude it is all fun and games developing visual studio addins for GP!

 

Thanks for Steve inspiring this post with his tweet and helping with the screen shot.

Installing Dynamics GP Intrastat Module

If you are looking for the Intrastat module, it is part of the Main GP install, I think fromGP2013 when more of the product extras were bundled in - but certainly from GP 2015. When running the GP installer the various features that can be installed are offered in the installer, find Enhanced Intrastat, click on the icon to change if it is installed or not by the installer. This can also be performed retrospectively to add the feature to an existing install.

intrastat installer Dynamics GP

You must switch the module on in company options and use the intrastat setup window to get it going. Look for the user guide on line or on the installation media for full details.

SQL to extract contiguous ranges for maintenance tasks in Dynamics GP

From time to time I find some SQL that really makes me smile and sit back and stare at it with awe. Today is one of those days, let me show you…

Many of the maintenance windows in Dynamics GP ask for ranges of numbers to be entered. They consist of a start number and then an end number, start and end points for which you would like to execute a operation over.

It is a common thing in GP is to use a GP macro mail merge to automate the user interface where a repetitive operations are required.  If a macro mail merge is being used for one of these windows, then it requires the start and end numbers over which we would like to perform the operation.

Let move to something more tangible and the real world example I’m working on. However don’t get tied down by my example, there are many other range windows in GP that this principle would work on too.

Remove Sales History Windows Dynamics GP

I want to surgically remove 901,872 historical sales documents from GP, using a macro. I am using a macro so that we are safe, the GP biz logic gets applied by the UI. I could use SQL but always best to try macro first so to benefit from the safety the UI gives us. This could take some time, supplying the document numbers and one by one deleting them.

Master numbers are a number id that ties together a string of sales documents that are related, it is held against all sales documents. For efficiency we notice that we can remove documents by master number, so all related documents, quote->order->invoice->return will be deleted at the same time. This would mean looping through the remove operation of window much less, as one master number removes many documents.

This brings us to 446,741 unique master numbers that need removing from 901,872 sales documents (yes I’ve checked and only three master numbers in my range link to related documents outside my range, so I’ve taken those out of this list). I could macro the window to set the "from" and "to" to be the same master number, thus removing one set of related documents at a time, then moving to the next number and then the next. It would work, but testing showed I’d be here all week doing it. Besides I can see somehow that I can be more clever than that!

GP is more efficient on these types of operation if you can supply ranges of numbers to work on, so really I want to look at the list of 446,741 numbers and find all the contiguous ranges within the sequence. Then identifying the start and end numbers to put in the UI start and end fields. Although it is worth remembering sometimes there will just be on number in a particular range, when there are gaps on both sides of the master number.

Hitting Google I found the following post, where they use recursive CTEs to find the ranges. Restricting my sequence to only 10,000 records, it took about three mins to run. Letting it run on the full sequence, I ran out of patience (and probably server resources) to let it get to end. I did try to tune it with some physical tables indexed to try and improve performance, but ultimately recursive CTE’s are terrible for large data sets like this.

How to find contiguous ranges with SQL

I went looking again and found another solution in this post.

How to find the boundaries of groups of contiguous sequential numbers?

This is genius insight in its script design. I do use ROW_NUMBER() a lot myself for all sorts of weird problem solving, but had never seen it applied like this before.

I adapted the snippet of the SQL to my problem and came up with this:

DECLARE @MNTable TABLE (MSTRNUMB INT PRIMARY KEY (MSTRNUMB));

INSERT INTO @MNTable
SELECT DISTINCT MSTRNUMB AS MSTRNUMB
FROM [dbo].[SOP30200] --WHERE Blah blah...
ORDER BY MSTRNUMB;

WITH CTE
AS (
SELECT ROW_NUMBER() OVER (
ORDER BY MSTRNUMB
) - MSTRNUMB AS Groups
,MSTRNUMB
FROM @MNTable
)
SELECT MIN(MSTRNUMB) AS [From]
,MAX(MSTRNUMB) AS [To]
FROM CTE
GROUP BY Groups
ORDER BY MIN(MSTRNUMB)
 
So from a query that never finished processing we go to this:
 

query times

Really, it is now down to a few seconds, to get all the results!

sqlresults

We have now only 3,760 row ranges, from 446,741 master numbers, so I now only need to get the macro to iterate the remove window 3,760 times, that is a much more acceptable!

So the mail merge macro now takes each row of this result set and uses the from and to values from the query to populate the boxes for the master number and then sets the report options and finally then processes. Job done.

Back to the SQL for a moment…

The SQL is fascinating for this though, looking at the CTE data we see how it works.

sql result cte

The row number increments but the subtracting of the ROW_NUMBER from the master number sequence causes a banding or grouping number to be created. So long as the sequence number is only one greater than the previous number, then the banding number is maintained the same in the group. This is because the relative difference between the ROW_NUMBER and the master number has not changed. If the master number jumps up because of a gap it will have gone up by more than the row number, so the grouping will number will be changed to a new grouping number.

This grouping number is then subsequently used to gather the max and min values for each section. I love the efficiency and simplicity of this solution. It also shows how many ways there can be to solve a problem.

 

Further improvements

I can see further improvements are possible. If we in-fill the sequence of master numbers before processing them with dummy master numbers, where documents don’t exist in the database, then this will further reduce the number of times we have to loop. In that case it would allow cases where the max of one range to the min of the next contain no documents to be combined into one range.

I’ve ran out of time and leave it to the reader to investigate that optimisation.