If you encounter an "Invalid Character" error in your Microsoft Dynamics NAV integrations, here is a troubleshooting tip

Microsoft Dynamics NAV allows characters in its string fields that might not be compatible with the XML standards used by the .NET XML serializer.  If you encounter errors in you integrations that state invalid XML data or invalid characters have been encountered, here is a tip from our Microsoft Dynamics NAV support engineers on how to find the records that need fixing within Microsoft Dynamics NAV.

In the Connector for Microsoft Dynamics log it is mentioned which map is causing the trouble. In my scenario it was the Microsoft Dynamics NAV Contact to Contact map which means it is table 5050 Contact we need to have a look at. While I was trying to narrow down the faulty records, I synched in batches. That did work fine (though the check for data modified after stopped working and no old data synched). That resulted in having me to update data directly in the SQL Server table. Then I found some empty name fields records. That is how I found out this is where I had to do some cleaning. 

So, I ran the following query (this will find contact who's names are blank, you could also supply the invalid characters that you are looking for as well to this script):

USE DatabaseName


SELECT * FROM DatabaseName$Contact WHERE Name =  ’’ 

With SQL Server management studio you can easily scroll through the list and search for the obvious required field names that are supposed to be there. 


SELECT COUNT(*) FROM DatabaseName$Contact WHERE Name = ’’ 

That returned 4.

SELECT * FROM DatabaseName$Contact WHERE Name =  ’’ 

Then you have it and you can easily delete them:

DELETE FROM DatabaseName$Contact WHERE Name = ’’ 

After that, everything seemed to be running as normal.