Dynamics GP Stored procedure [smFormatStringsForExecs]

You’ve seen it often enough in GP stored procedures, but what is it doing?

EXEC @iStatus = smFormatStringsForExecs 
@I_vInputString = @I_charEndCustomer,
@O_cOutputString = @cEndCustomer OUTPUT,
@O_iErrorState = @O_iErrorState OUTPUT

The above code snippet is the common code pattern it appears in. I had always assumed, until today that this procedure was cleaning the input into the procedures for anti-SQL injection attack purposes, alas it seems not.

The procedure actually turns the passed string into a quoted string for use as parameters when building up SQL by concatenation within other GP stored procedures.

Example:

IF we pass the string 6252''5 002     (with the trailing spaces) into the procedure, this is what we find:

DECLARE    @return_value int,
@O_cOutputString char(255),
@O_iErrorState int

EXEC @return_value = [dbo].[smFormatStringsForExecs]
@I_vInputString = N'6252''5 002 ',
@O_cOutputString = @O_cOutputString OUTPUT,
@O_iErrorState = @O_iErrorState OUTPUT

SELECT @O_cOutputString as N'@O_cOutputString',
@O_iErrorState as N'@O_iErrorState'

SELECT 'Return Value' = @return_value

2017-03-10_09-43-34

So the procedure has taken any trailing space out and wrapped the passed string in quotes and doubled up any quotes that were in the string to delimit them. Thus the output of the procedure can be used in string concatenation to build a SQL script dynamically.

So you don’t want to use the output of this parameter directly or it won’t work:

Example
i.e. don’t do this with the output from the format strings procedure…

DELETE FROM RM00101 WHERE CUSTNMBR=@O_cOutputString

-as it will not match any customer numbers (unless your customer numbers are in quotes!).

Dynamics GP eConnect and handling exceptions under Web API

Beware when using eConnect with ASP.NET Web API.

slipping 

… as exceptions raised by eConnect will not be of type eConnect.Exception

thus using our normal typical catch block, such as:

catch(eConnectException ex)

will not work, because all eConnectExceptions will actually cause the following exception to be raised/returned:

System.NullReferenceException: Object reference not set to an instance of an object.

  at Microsoft.Dynamics.GP.eConnect.EventLogHelper.AddExceptionHeader(String action, Object[] inputParameters, StringBuilder errorString)

This is because there is a flaw in the eConnect, eConnect.EventLogHelper.CreateEventLogEntry method. It attempts to extract the Thread.CurrentPrincipal.Identity.Name as part of the output, but when eConnect is used in the context of ASP.NET Web API, this is null

As it is null this causes a new exception to be caused, the original exception superseded by the new System.NullReferenceException caused in the exception handler, that masks the original issue.

Explicitly setting the current threads identity name just before calling eConnect solves this problem and so allows the eConnect.Exception to be caught and processed as would normally be expected.

I discovered this whilst diagnosing a problem on the forums for someone, see: Econnect Exception Handling doesn't work in WEB API 2 application

Exporting from SmartList Dynamics GP “returned 29”

Are you experiencing a dialog box with the following text on attempting to export a Smartlist to Excel?

“RUN APPLICATION ERROR”

"C:\Users\user\AppData\Local\Temp\7\somefilename.xlsx returned 29"

This seems to be an error passed back from Excel regarding permissions issues, or lack of communication to excel from GP via DDE (Dynamic Data Exchange).

The option in excel in advanced options (File>>Options>>Advanced)

“Ignore other applications that use Dynamic Data Exchange (DDE)” should be unchecked.

Ignore other applications that use Dynamic Data Exchange (DDE)

There are a couple of threads on this on the GP forums:

SmartList export to Excel Run Application Error "[filepath]" returned 29

Run application error 29 in SmartList

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 when something disrupts Dynamics GP’s long lived open SQL connections between the client and server.

FP: Couldn't close table! 

This might be due to networking issues like bad routing, changing drivers on network cards, intermittent physical faults with NIC cards, faulty Ethernet cables, faulty intermittent Ethernet sockets on walls when doors in that wall are slammed shut , servers or client machines going to sleep, server NIC card problems, connectivity problems (WIFI), firewall outages, or many other potential causes. However there is always a break in communication between the server and client, just finding the root cause can be tricky.

My long standing 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 even mid way through the day), the application generates the “FP Couldn’t close table” error. They have also been getting other SQL related errors such as syContentPageXMLCache cannot find tableor “Invalid object name ‘##1470453’” – for reference, the latter is a temp table dropped when the connection dropped.

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 and PC

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. I suspect the SQL connection broke under the hood during the session. I was stumped as to what could possibly be causing the connection outages bearing in mind what had already been tried.

Attempting to resolve 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,” FP: Couldn’t close table!” error dialog.

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

The power settings in Windows 10 were checked and they all looked ok from the top level screen, set to sleep “Never”.

Power Options window

However 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.

 

[Updated from original post]

A few days later…

The user experienced the issue again, the  fix had not worked. I did some more research and found some people reporting that although the advanced power settings are set to "never", sometimes by setting them to a very large number of minutes, then later setting back to “never” works. Almost as if the setting has not properly taken.

I updated the machine to have an 18 hour sleep time, to see what happened the next day.

There were also reports of overheating CPUs causing machines to sleep, but this user's machine hardware had been changed before, so I ignored that possibility.

Following day…

First thing in the morning I saw the user go for a coffee, this reminded me to go look at the machine log, but I couldn’t connect or "ping" the machine. On visiting the machine I found it asleep, this is when it dawned on me that what we were seeing may be a "machine-chair interface" issue.

I woke the machine with the mouse, checked the log to see it had gone to sleep moments before the user had gone for coffee. This time frame was too short for any reasonable timeout to have occurred, as the machine would be sleeping every few moments surely? The machine log had previously shown a pattern of sleeps at lunchtime and morning and mid afternoon. I had been assuming these sleeps were the user’s breaks due to some kind of inactivity timeout.

On the user returning from coffee I enquired about how they lock the machine when leaving it (note that our staff handbook mandates machines be locked when unattended). The user then showed me how they lock the machine…

user selects sleep menu

...the user selects the sleep menu!

Goodness knows how much time had been wasted tracking this down, but my recommendation might be now that we disable sleep for desktop machines in group policy!

So it turns out that the user was manually putting the machine to sleep so as to “lock it” each time they went for a break, thus causing the Dynamics GP database connection to break as the network card would be off and causing havoc with the application. The user had not made any association in the discussions at their desk about how we are stumped as to why the machine keeps sleeping and the action of what they saw as “locking” it. This is where being able to stand next to a user to observe an issue can be so valuable in problem solving!

Needless to say we have had no further issues since showing the user how the [Windows]+L key locks the machine.

Further reading

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