Stefan
c741535aec
Playwright Tests / test (push) Canceled after 0s
- Added a new SQL script to address the "String or binary data would be truncated" error in mfr sync by widening the Note fields in service objects and companies to NVARCHAR(MAX). - Updated the table types and associated stored procedures to accommodate the changes, ensuring compatibility with existing data. - Queued a re-sync for previously rejected rows due to truncation issues. - Created application logins for fuchs_fds and fuchs_web with placeholder passwords for initial setup. - Introduced functions for invoice backdating and invoice date retrieval to streamline invoice processing. - Developed a password generation function with customizable parameters for length and character types. - Added a random number generation function to support password creation.
53 lines
3.2 KiB
Markdown
53 lines
3.2 KiB
Markdown
---
|
|
status: Accepted
|
|
date: 2026-09-30
|
|
applyTo:
|
|
- "Fuchs_Database/dbo/Tables/mfr__*.sql"
|
|
- "Fuchs_Database/dbo/User Defined Types/mfr__tt__*.sql"
|
|
- "Fuchs_Database/dbo/Stored Procedures/mfr__updt__*.sql"
|
|
- "Fuchs_DataService/FdsMfrClient.cs"
|
|
- "Fuchs_DataService/FdsColumnWidths.cs"
|
|
supersededBy: ""
|
|
---
|
|
|
|
# 0033 — mfr free-text columns are NVARCHAR(MAX); table, table type and copies stay aligned
|
|
|
|
## Context
|
|
mfr publishes no length limits for its string properties (`MFR_RESTClient/Docs/mfr_interface_description.md`).
|
|
`[mfr__serviceobjects].[Note]` and `[mfr__companies].[Note]` were `NVARCHAR(255)`, as were their
|
|
merge types `mfr__tt__serviceobjects` / `mfr__tt__companies`. From 2026-09-25, users typed longer
|
|
notes in mfr (ServiceObject up to 753 chars, Company up to 723). The `mfr__updt__*` merge proc copies
|
|
the staging table into the table type, so SQL Server rejected every batch holding such a row
|
|
(msg 8152, which names no column). That included every Company/Contact/ServiceRequest sync that
|
|
`$expand`s the row. The result was about 1000 errors in five days, plus rows the short sync never
|
|
re-fetched, because it only reads rows newer than the local `DateModified` watermark. Merge failures
|
|
were silent until 8bcec40 began logging them, so earlier occurrences went unnoticed.
|
|
|
|
## Decision
|
|
- Free-text mfr properties (`Note`, `Description`, `Comment`, …) are stored as `NVARCHAR(MAX)`.
|
|
Bounded widths are kept only for values mfr structurally keeps short (ids, codes, names), and
|
|
those are revisited when data proves otherwise.
|
|
- A width change is made in the target table, its `mfr__tt__*` type **and** every table variable
|
|
or type that copies the row with `SELECT *` (e.g. `@company` in `fds__getRequest_details`,
|
|
`fds__prepInvoice`, `fds__prepStorno_recreate`), all in the same change. Otherwise the truncation
|
|
just moves to the next copy.
|
|
- Truncating at the mapping layer was rejected. The data service does **not** truncate
|
|
silently. Instead, when a merge fails with 8152/2628, `FdsColumnWidths` compares the staged
|
|
batch against the narrower of the table and type widths. The error log then names column, longest
|
|
length, limit and an entity Id (`over-long columns: Note: 753 > 255 chars (1 row, e.g. Id=…)`).
|
|
This lookup runs only on the failure path.
|
|
|
|
## Consequences
|
|
- Changing a `mfr__tt__*` type means DROP + CREATE (types cannot be ALTERed). The procs that
|
|
DECLARE the type must be dropped first and recreated. See
|
|
`Fuchs_Database/Scripts/2026-09-30_mfr_truncation_fix.sql` for the pattern, including grants.
|
|
- After fixing a rejected merge, queue the affected Ids in `fds__mfr_updaterequests`. The short
|
|
sync will not revisit them, and the nightly full reset is disabled in `fds__getUpdateableTables`.
|
|
- `NVARCHAR(MAX)` columns cannot be index key columns. None of the widened columns is indexed.
|
|
|
|
## Alternatives considered
|
|
- **Truncate to the column width in `SetRowValue`**: this would keep syncing, but it loses user text
|
|
silently. For keys such as `ExternalId` (DATEV customer number) it would corrupt data. Rejected.
|
|
- **Widen to a larger fixed size (e.g. 1000, like `mfr__contacts.Note`)**: this just moves the
|
|
cliff. mfr enforces no limit. Rejected for free text.
|