GP Web Services and Invalid Character Errors
I am going to go over the steps to find and resolve invalid data characters in Dynamics GP that cause GP Web Services exceptions. A common process of "copying and pasting" a block of text from an external program into the Note window for a Dynamics GP record or an import routine directly to the SQL tables can cause Dynamics GP Web Service to throw an error. When using a .Net application that does a GetSalesOrderByKey error, you may see something like this:
"The application encountered an unhandled system exception. Contact your system administrator for details."
Unfortunately the error returned does not tell us what the exact problem may be.
Another common place we see these invalid data exceptions is in the CRM Connector for Dynamics GP. Since the Dynamics GP Adapter that the CRM Connector uses calls GP Web Services, you can potentially see lots of these types of errors in the CRM Connector Logs on an initial data synchronization. An example of the error in the Connector Log is the following:
"[Sales Order to Order] has encountered an error while processing key [ORDST2235]. The application encountered an unhandled system exception. Contact your system administrator for details."
The invalid data record not only causes the GP web services method call to fail, but also will cause the GP Web Services Exception Management Console to not display the exception for the date range you are on. In fact, you get a misleading error message in the Console that suggests you to confirm the configuration file is correct like below. Ugh. The workaround for this error with the Exception Console is to manually run a delete statement on the WSExceptionLog table in the system database (DYNAMICS).
The following will remove any exception messages where the ExceptionMessage contains the keyword 'hexadecimal'. This keyword is always present when you encounter this type of invalid data scenario:
delete DYNAMICS..WSExceptionLog where ExceptionMessage like '%hexadecimal%'
Finding and Fixing the Invalid Data
When data is retrieved out of Dynamics GP using Web Services, the eConnect Requester functionality is being used. Each read operation in GP Web Services uses a SQL view that is mapped to an eConnect requester document in the eConnect_Out_Setup table. More information on how the eConnect Requester functionality works is at MSDN.
When there is an invalid hexadecimal character in the table, the eConnect XML string will show up similar to the following example:
<Notes>
<NOTEINDX>3168.00000</NOTEINDX>
<DATE1>
2012-11-15T00:00:00
</DATE1>
<TIME1>1900-01-01T16:54:56</TIME1>
<TXTFIELD>
This is a note that has a char(4) character:
</TXTFIELD>
</Notes>
Note the sequence for the invalid hexadecimal character typically starts with the ' &# ' characters in the XML representation, but it may be different in other cases. The Note itself for this record in Dynamics GP looks like the following with the unreadable character being displayed at the end:
The strategy I like to use when troubleshooting which record is causing the problem when running a GetList or GetByKey operation is to first run a SQL Profiler. You will only need to capture the RPC: Starting and RPC: Completed Event Classes under the Stored Procedures Events when setting up the SQL Profiler Trace:
These events will be enough since you will be able to see the insert into the WSExceptionLog table and also identify the call to the eConnectOut stored procedure when the error is hit from GP Web Services. After you reproduce the error and are looking at the SQL Profiler results, do a search for the WSExceptionLog table in the TextData column. You should see the insert statement like the following which will give you the document key:
Generally, the last exec call to the eConnectOut stored procedure right before the insert to the WSExceptionLog table will be the statement that caused the problem. I copy the entire text for the call to the eConnectOut stored procedure and paste it into SQL Query to execute it. An example of the entire eConnectOut call looks like the following:
declare @p40 int
set @p40=0
exec eConnectOut @I_vDOCTYPE='WSSalesOrder',@I_vOUTPUTTYPE=2,@I_vINDEX1TO=default,@I_vINDEX2TO='ORDST2235',@I_vINDEX3TO=default,@I_vINDEX4TO=default,@I_vINDEX5TO=default,@I_vINDEX6TO=default,@I_vINDEX7TO=default,@I_vINDEX8TO=default,@I_vINDEX9TO=default,@I_vINDEX10TO=default,@I_vINDEX11TO=default,@I_vINDEX12TO=default,@I_vINDEX13TO=default,@I_vINDEX14TO=default,@I_vINDEX15TO=default,@I_vINDEX1FROM=default,@I_vINDEX2FROM='ORDST2235',@I_vINDEX3FROM=default,@I_vINDEX4FROM=default,@I_vINDEX5FROM=default,@I_vINDEX6FROM=default,@I_vINDEX7FROM=default,@I_vINDEX8FROM=default,@I_vINDEX9FROM=default,@I_vINDEX10FROM=default,@I_vINDEX11FROM=default,@I_vINDEX12FROM=default,@I_vINDEX13FROM=default,@I_vINDEX14FROM=default,@I_vINDEX15FROM=default,@I_vFORLOAD=default,@I_vFORLIST=1,@I_vACTION=default,@I_vROWCOUNT=1000,@I_vREMOVE=default,@I_vDATE1=default,@I_vWhereClause=default,@O_iErrorState=@p40 output
select @p40
When you execute the above statement in SQL Query, a XML result set will be returned. You can now search the XML result set for the ' &# ' character sequence to determine where the exact invalid data is located for this record. The common one is the <TXTFIELD> element which is mapped to the note text in the SY03900 table. I have seen some of these characters in document numbers also so they really can be anywhere!
To finally fix the bad record in this case with the note record, you can either go into Dynamics GP and type over the bad character and save the note. This would be the easiest and most common way to resolve it. If you have several or hundreds of these in the Dynamics GP tables, you can write a SQL update statement to replace the invalid character with a blank space or some other readable character like an asterisk like below. Note the REPLACE function in SQL Server does not work on a text data type column like TXTFIELD but will work on other columns like the SOP10100.SOPNUMBE:
update SOP10100 set SOPNUMBE = replace(SOPNUMBE,char(4),'*') where SOPNUMBE like 'ORDST2235%'
NOTE - It is important for users of Dynamics GP to understand that it is not possible to have this type of data when using the Dynamics GP user interface and typing in the data. This type of invalid data only comes into the environment if you are doing some type of copying\pasting from another source or writing to the tables directly via some import routine. Using eConnect for Dynamics GP or GP Web Services to import data will not create these types of data issues from what I have seen. So once a customer runs into this problem they will need to determine what process they are doing that is creating this data and then stop doing it!
I have seen some elaborate stored procedures out there that will step through a table or even database to replace hexadecimal characters like these. I would be interested in hearing any feedback from the community on how you have dealt with these types of invalid characters and how the customer was inserting them in the first place.
Thanks,
Chris