Database Standards & Data Quality#

Prevent mojibake / encoding corruption in MnxResourceKey.ManExValue (SQL Server)#

Some resource strings have been saved with garbled characters (e.g., don’t instead of don't). This happens when UTF-8 bytes are decoded as Windows-1252 / Latin-1 (or similar) before being inserted / updated. ASCII-only text looks fine, so the issue often appears only in a small subset of rows that contain “smart punctuation” (’ " " – …).

1. Golden rule (end-to-end)#

Treat text as Unicode across the entire pipeline — source → application → DB:

• Source files / scripts: UTF-8
• App strings: .NET string (Unicode)
• DB parameters / columns: NVARCHAR + Unicode parameters
• Avoid any implicit conversions to VARCHAR

2. SQL Server storage rules#

1. Use Unicode column types.
2. Store resource text in NVARCHAR / NCHAR.
3. Avoid VARCHAR for user-visible strings, especially descriptions / help text.
4. Always use Unicode string literals in SQL — prefix with N:
   • Correct: N'don't'
   • Incorrect: 'don't' (can be coerced through a code page)
5. Pass parameters as Unicode from the app. In ADO.NET / EF / Dapper use SqlDbType.NVarChar (not VarChar).
6. Never build SQL via string concatenation for these updates / inserts.

3. File, import, and seed rules#

If resource text comes from a file (CSV / JSON / XML / SQL seed):

• Save the file as UTF-8 (prefer UTF-8 with BOM if using older tools).
• When reading files in .NET, specify UTF-8 explicitly: File.ReadAllText(path, Encoding.UTF8).
• For ETL / SSIS: set the correct code page / encoding for the source (UTF-8).
• Avoid copy/paste from Word / PDF into seed scripts without normalization — Word often inserts smart punctuation.

4. Normalize punctuation (optional guardrail)#

If curly quotes / apostrophes are not required, normalize to ASCII before saving:

'          →   '
" "        →   "
– —        →   -
…          →   ...

This reduces risk across legacy components that mishandle Unicode.

5. Ingestion-time validation#

Before persisting resource text, detect mojibake patterns and either block or auto-fix:

• Flag if a string contains ’, “, â€, or Ã — strong indicators of encoding corruption.
• Recommended approach: reject + log (best for data quality). Fallback: auto-fix + log if you must keep the workflow unblocked.

6. Database-side safety check#

Add a monitoring query — run it in CI, migration validation, or a scheduled job:

SELECT COUNT(*) AS MojibakeCount
FROM dbo.MnxResourceKey
WHERE ManExValue LIKE N'%’%'
   OR ManExValue LIKE N'%“%'
   OR ManExValue LIKE N'%â€%'
   OR ManExValue LIKE N'%Ã%';

If MojibakeCount > 0, fail the pipeline or alert.

7. Troubleshooting checklist#

1. Identify the affected rows and when they were updated (timestamps / user, if available).
2. Trace the source path — manual UI entry, import, seed script, migration?
3. Confirm:
   • Column type is NVARCHAR.
   • App parameter types are Unicode.
   • File encoding is UTF-8.
4. Apply a targeted cleanup only for affected rows (pattern-based WHERE + backup first).