Has anyone experienced issues when running a command like this in SSMS?
SELECT
STRING_AGG(JSON_ARRAY(COL1, COL2, COL3 NULL ON NULL), ', ')
FROM
{schema}.{table}
In my case this closes the connection to SQL Server with an error:
Msg 109, Level 20, State 0, Line 42
A transport-level error has occurred when receiving results from the server. (provider: Shared Memory Provider, error: 0 - The pipe has been ended.)
To work around this issue I split this activity into 2 steps:
JSON_ARRAY
STRING_AGG
This bug was fixed in CU1:
2081891 : Fixes an exception that occurs when
JSON_ARRAY
/JSON_OBJECT
return values are used in a parameter in functions that take strings. After you apply this fix, return values ofJSON_ARRAY
andJSON_OBJECT
are made coercible and can be used as string parameters.
So I strongly suggest you upgrade to the latest CU, for this and many other reasons (security, performance, other bug fixes).