`Invoke-SqlCmd` returns incorrectly sized results for binary columns

[jeroen-mostert]

Invoke-SqlCmd returns incorrect types for columns of BINARY/VARBINARY types. Specifically, it will return MaxBinaryLength bytes (default 1024) even if the column contains fewer. Since no length information is made available elsewhere, it's not possible for the consumer to compensate for this unless they already happen to know the length some other way.

Invoke-SqlCmd is certainly not my preferred tool of choice for executing queries, as opposed to scripts (its lack of support for parameterized queries alone damns it), but it is worth noting that sqlcmd (the thing it's trying to copy, for better and worse) does not exhibit this behavior; binary values that don't exceed the display width are formatted as appropriately-sized hexstrings.

Expected behavior

PS> (Invoke-SqlCmd -Server '(localdb)\mssqllocaldb' -Query 'SELECT 0x00 AS A').A.Length
1

Actual behavior

PS> (Invoke-SqlCmd -Server '(localdb)\mssqllocaldb' -Query 'SELECT 0x00 AS A').A.Length
1024

[Matteo-T]

This is fixed in v22.0.52.2+ (when available)