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