A coutrecords operation on table ‘[Not Found]’ cannot find the table–Dynamics GP

Symptom: User experiencing this error dialog when using navigation lists, but can happen in other areas of Dynamics GP

A countrecords operation on table '[Not found]' cannot find the table

Cause and solutions:

This error is caused by the client machine that is running Dynamics GP loosing network connection at some point during the Dynamics GP session. Dynamics GP holds some long lived connections, especially where temp tables are in use (for example in navigation lists). Indeed it is necessary for GP to keep the connection alive or the temp table will be reclaimed by SQL server as soon as the last connection to it drops. It may also be that you see an associated error similar to the following, with a random looking numeric table name for the global temp table. This is the result of the connection loss.

SQL Server, Invalid object name ‘##293343’

There can be many root causes for a SQL connection to be lost in action. Network infrastructure failures, due to cabling faults in RJ45 connectors, network cards, when the kicks the cables under desk intermittent errors, or someone re-patching live network cabling, network cards starting to fail due to wear and tear etc. These hardware related issues used to cause us issues many years ago, but with a better infrastructure, in recent years it has been software issues in the shape of “sleep settings”. Perhaps there is a poor wifi bridge or similar weak network infrastructure that needs attention, as every time someone starts the microwave in the canteen the wifi drops… – you get the idea, so much to investigate.

The server being rebooted, or SQL server tier being torn down while the user is using GP can also result in the same error. 

The advanced settings of the network card can present various settings, including allowing the operating system to put the network card to sleep, supposed power efficiency? The machine OS sleep settings may be putting the machine into sleep or hibernate after a certain period of time, there may be BIOS settings too.

Users who take long lunches, or leave machines on overnight with GP logged in will more than likely get caught out by sleep settings. When the machines are left inactive they go to sleep loosing the SQL connection and resulting in the above error. I’ve seen more of this since we moved the estate to Windows 10, I don’t think I can draw any real conclusions from that though. I have also experienced users putting their machines to sleep when going for lunch or overnight, manually from the start menu. For this reason Active Directory Group Policy has been created and rolled out to lock down such settings for the GP users.

Although not personally experienced, I have read that this may also be that the ODBC connection settings not being set up correctly. For Dynamics GP, uncheck “Use ANSI quoted identifiers” and “Use ANSI nulls, paddings and warnings”, Uncheck “Perform translation for character data” in the ODBC settings.

See: How To Create An ODBC For Microsoft Dynamics GP 2010 which is applicable for all versions of GP.


Hopefully this has been useful to you – let me know if it has with a comment – motivates me to write more!

TF400409 you do not have licensing right to access this feature

Setting up a new user for team foundation server, although user has been added to all the licencing groups and project groups, they cannot expand the root node of the team collection. They can also not access the portal security pages as it gives the above error.

Turned out to be that they needed the access level for the user setting.

This is outlined within the document here: https://docs.microsoft.com/en-gb/vsts/security/change-access-levels

 

You may follow this route to the access level admin (in the version we use)

Select Security from the Team Explorer Settings menu from Visual Studio

2018-01-26_10-42-17

Navigate to the root of the control panel

2018-01-26_10-44-00

Navigate to the Access Levels tab

2018-01-26_10-46-10

 

Put the user under the appropriate access level of Basic or Advanced. This should then allow them to access the web portal without error and expand the source control collection node to see the individual projects (assuming you’ve set them up correctly with those too).

SQL formatting and Power Query

Today I got caught out by using SSMSBoost to SQL format a query being used as the source for a Microsoft Power Query.

On updating the stored procedure the query stopped working in Power Query, with the error that the column “Temp” was missing.

Looking at the query output in SSMS, I could see that the column had changed to TEMP in upper case from Temp in the original query, easily fixed. Then I realised what might have happened to cause it and proved it. Formatting the SQL had caused the temp column to be upper cased, my suspicion is that Temp was wrongly identified as a keyword by the formatting template and thus with the setting in SSMSBoost being to uppercase keywords, it got uppercased. Power Query is case sensitive so got upset.

SSMSBoot Settings Menu showing the UPPERCASE keywords = True

Just a warning for those of you that use SQL formatting tooling, one to keep an eye out for, luckily having a column named Temp is not too common.

ClickOnce with report viewer control

Installing SSRS Report Viewer component for Visual Studio 2017 (VS2017) using NUGET

For Visual Studio 2017, the SQL server reporting services, report viewer component, for web forms or windows forms has been put into a NuGet package. This means it can be installed into projects where it is required, with the added benefit of no longer needing a client report viewer runtime msi installer for the client, that previously was used to install the runtime report viewer components into the GAC.

To upgrade a solution from using the old report viewer component, open all the references nodes in the project tree, remove all the old .dll references to the original report viewer .dll files. Then install the NUGET package for the projects in the solutions within the solution that require it.

To install the NuGet package, Right click the project, select Manage NuGet Packages,

Select the Browse option and then search for “ReportViewerControl” as shown.

2018-01-05_12-35-12

Take care to install the Windows Forms or Web Forms version as required and that it is the newer version, it is easy to click the wrong one as there are a few similar products in there.

This will pull down the required .dlls in to the project.

If you need the control in the visual control toolbox, then right click the toolbox area, select “choose items”, then in the .NET Framework Components Tab use the Browse button to browse to the control’s dll. This will be located in the “\packages\Microsoft.ReportingServices.ReportViewerControl.Winforms.140.1000.523\lib\net40” folder for the example where we installed the version shown above (note the version number). The packages folder is usually found with the solution or project files somewhere.  Select the file “Microsoft.ReportViewer.WinForms.dll” within that folder.

This will add the control to the toolbox.

2018-01-05_12-41-26

 

Problems:

I experienced some real problems after this when trying to deploy the application with the ReportViewer control via ClickOnce deployment.

On install of a non-developer machine, various errors occurred.

These were due to the “Microsoft.ReportViewer.Design.dll” library being referenced by the project. This meant the project then had visual studio dependencies introduced. Removing this file caused the application deployment to work again as expected.

"assembly Microsoft.VisualStudio.Text.Logic version 14.0.0.0 be installed in the GAC"

The above was caused by the report viewer design dll being referenced by the project, causing a dependency that was picked up by the ClickOnce manifest generator.

Microsoft.VisualStudio.Text.Logic installed in teh Global Assembly Cache

Assembly Microsoft.VisualStudio.Diagnostics.Assert needs to be installed in the Global Assembly Cache

image

Unable to install or run the application. The application requires that assembly Microsoft.VisualStudio.Diagnostics.Assert Version 14.0.0.0 be installed in the Global Assembly Cache (GAC) first.