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

Create ASP.NET virtual directories in IIS Express using windows credentials to another server (website project)

Moving from developing ASP.NET sites against the full IIS to IIS Express led me to migrating the many virtual directories that were defined for the site. There was an added dimension to the problem, in that the the virtual directories map to central development resources server to keep the many GB of images and tens of thousands of pdf files off the developer SSD drives, for space reasons (and sanity).

To my relief it turns out to be totally possible for website projects (as oppose to website application projects), but I didn’t on performing a quick internet search, find any reference to credentials.

The virtual directories can be set up as part of the site definition in the .vs folder within the applicationhost.config of the website solution. By editing that file, under the following node:

<site
  <application path= …
      <virtualDirectory path= …

Luckily rather than in notepad, I opened up this file in Visual Studio, and found intellisense gave me the answer I had been looking for, and it was a good one!

virtualdirectory

You can see there are userName and password attributes available.

So add a virtualDirectory node for each virtual directory, following this pattern, one after another, after the application node.

<virtualDirectory path="/imageitems" physicalPath="\\resServer\Resources\ProdRes"  
userName="devaccess@mydomain.net" password="ourpassword123" />

We use a read only, low rights user for this, so I’m not too bothered about the password getting into the config file.

Convert ASP.NET “Website Project” from IIS to IIS Express

I had this problem to solve and thought I’d document my solution here, as I didn’t find anything appropriate documented when I tried searching the search engines.

Website Project vs Website Application Project

There are two broad ways ASP.NET websites can be setup as applications.

  • Web site projects
  • Web site application projects

The quickest way to tell the difference is that an application project uses a visual studio project file to define the files that are in the project, whereas for a web site project, any file under the folder is classed as part of the project.

For more information look at: Web Application Projects versus Web Site Projects in Visual Studio (msdn)

Web site projects are opened by going to File->Open Website, rather than File->Open Project, obviously there are many ways to open projects.

I labour this point as on the forums it is almost the norm for questions about website projects get people answering as if it were a website application project, understandable as that is the form of project most developers will experience.

Finally I decided to tackle the problem of being required to have local administrator rights to develop with IIS hosted sites. Running with local admin rights in this day and age is becoming more and more risky and something that is getting less and less necessary so long as you have an alternative elevated user to use when the need arises to perform operations that require it (like installing software). 

These days developing sites against IIS Express is the norm, back in the day we outgrew Cassini, a small developer webserver that used to host sites, as we had url rewriting, host headers, custom url rewrite modules, virtual folders mapped with custom credentials etc. It turns out that now in 2017 all this is possible using IIS Express. How do you move the site though?

These are the steps I performed that worked for me:

Remove the IIS website

In IIS manager I removed the website, so IIS is no longer managing that folder.

Edit the solution file

I edited the solution file to remove the website project definition node from it.

Add the website back in

I then opened the solution and used the add existing website option to add the website folder from source control back into the solution.

Define virtual directories

I then edited the applicationhost.config file that resides in the solution .vs folder, defining the virtual directories that were previously defined in IIS. I did this under the <virtualDirectory path= section for the site.

Added https binding

I added a binding to the <bindings> section for https,
<binding protocol="https" bindingInformation="*:44340:localhost" />

Custom rewriting 

The custom URL rewriter and rewrite rules seemed to come over by themselves so no action was needed.

Custom Host name

I followed the instructions on USING A CUSTOM HOSTNAME WITH IIS EXPRESS AND VISUAL STUDIO 2015 (VS2015)

to get the custom hostname so the site responds to custom host headers correctly.

<binding protocol=”http” bindingInformation=”*:80:mywebapp.127.0.0.1.xip.io” />

Success

I was then amazed to see the site come up first time on rebuilding it and launching it.

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.