Export-BcpDatabase fails if with "could not serialize the data for node '@columname'" if control characters are used in database
Description
When Export-BcpDatabase is used on a database that contains control characters in any column, it will fail with an error message like
One or more errors occurred. (FOR XML could not serialize the data for node '@Name' because it contains a character (0x0002) which is not allowed in XML. To retrieve this data using FOR XML, convert it to binary, varbinary or image data type and use the BINARY BASE64 directive.)
Cause
This happens when control characters are used anywhere in the database. Control characters are characters that can't be printed on a screen and are reserved for special uses. For example the bell character ( 0x07 ) is used to make a beep sound
Solution
Control characters are not valid for any field in the database. Remove them. Control characters are all characters from 0x00 to 0x1F