Microsoft Dynamics GP and Illegal Characters Part 2
In the previous article in this series, Microsoft Dynamics GP and Illegal Characters Part 1, we discussed how string delimiter or terminator characters can cause issues and need to be handled correctly. The article discussed the single quote (') and double quote (") characters. Sometimes we have seen a single quote (') character cause problems at the SQL level when it is included in a data field and not handled correctly by the code.
This article discusses a little known issue with the Report Writer which can cause the Microsoft Dynamics GP application to crash. It can be caused by including the back quote (`) character, which is usually found below the tilde (~) character on the keyboard.
The problem here has nothing to do with SQL Server. This time it is the Dexterity Report Writer that cannot handle the character. The back quote character is used internally by the Report Writer by the code that handles the break fields for Additional Headers and Footers.
The inclusion of the back quote character in a key field which is used as a break field in an Additional Header or Footer on a report can cause the Report Writer to interpret the data incorrectly and cause it to crash. This sounds like the issue in this forum post, which prompted me to write the two articles on the subject.
Finding the Offending Data
Below are example SQL Scripts that can be used to help find problem characters in the master and transactions records for Receivables Management and Payables Management.
SQL Script for Receivables Management
select
CUSTNMBR, CUSTNAME
from RM00101 C
where CUSTNMBR LIKE '%''%' or CUSTNMBR LIKE '%`%'
or CUSTNAME LIKE '%''%' or CUSTNAME LIKE '%`%'
select
CUSTNMBR, DOCNUMBR, RMDTYPAL, '1a' as MODE
from RM10301 WT
where CUSTNMBR LIKE '%''%' or CUSTNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
CUSTNMBR, DOCNUMBR, RMDTYPAL, '1b' as MODE
from RM10201 WP
where CUSTNMBR LIKE '%''%' or CUSTNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
CUSTNMBR, DOCNUMBR, RMDTYPAL, '2' as MODE
from RM20101 O
where CUSTNMBR LIKE '%''%' or CUSTNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
CUSTNMBR, DOCNUMBR, RMDTYPAL, '3' as MODE
from RM30101 H
where CUSTNMBR LIKE '%''%' or CUSTNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
SQL Script for Payables Management
select
VENDORID, VENDNAME
from PM00200 V
where VENDORID LIKE '%''%' or VENDORID LIKE '%`%'
or VENDNAME LIKE '%''%' or VENDNAME LIKE '%`%'
select
VENDORID, VCHRNMBR, DOCNUMBR, DOCTYPE, '1a' as MODE
from PM10000 WT
where VENDORID LIKE '%''%' or VENDORID LIKE '%`%'
or VCHRNMBR LIKE '%''%' or VCHRNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
VENDORID, VCHRNMBR, DOCNUMBR, DOCTYPE, '1b' as MODE
from PM10300 WCP
where VENDORID LIKE '%''%' or VENDORID LIKE '%`%'
or VCHRNMBR LIKE '%''%' or VCHRNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
VENDORID, VCHRNMBR, DOCNUMBR, DOCTYPE, '1c' as MODE
from PM10400 WMP
where VENDORID LIKE '%''%' or VENDORID LIKE '%`%'
or VCHRNMBR LIKE '%''%' or VCHRNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
VENDORID, VCHRNMBR, DOCNUMBR, DOCTYPE, '2' as MODE
from PM20000 O
where VENDORID LIKE '%''%' or VENDORID LIKE '%`%'
or VCHRNMBR LIKE '%''%' or VCHRNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
UNION
select
VENDORID, VCHRNMBR, DOCNUMBR, DOCTYPE, '3' as MODE
from PM30200 H
where VENDORID LIKE '%''%' or VENDORID LIKE '%`%'
or VCHRNMBR LIKE '%''%' or VCHRNMBR LIKE '%`%'
or DOCNUMBR LIKE '%''%' or DOCNUMBR LIKE '%`%'
Once you are able to find the offending data, you can usually edit it directly with SQL. If the data is in a key field, be sure to be sure to also edit all rows that refer to the data so you do not create orphan records. If you need assistance identifying the tables which will need updating you could use the script mentioned in the article below or you could log a support incident.
spSearchOnAllDB: SQL Stored Procedure to Search an Entire Database
Notes:
To get the spSearchOnAllDB script to work with a single quote ('), you will need to replace any single quotes in the search string parameter with 4 single quotes in a row (''''). This will allow the parameter to be passed and the dynamic scripts inside the stored procedure to work. You can also use this stored procedure to search for single quotes in any field using the syntax below:
exec spSearchOnAllDB '%''''%'
If you require assistance from support to actually fix the data, this will usually be a billable service.
Always have a backup of the data before attempting any back end manipulation.
Please let us know if you have seen these type of issues before.
David
24-Jun-2009: Added hint about searching for single quotes using spSearchOnAllDB stored procedure.