I just learned something new and figured other people might benefit.
I like to use the string_agg() function in BAQ’s because I can group rows while still being able to show some information from fields that would otherwise duplicate the rows. (like a field in order release when I only want row per line… etc).
So I have a knarly order with over 1000 releases that a user is managing. We have a dashboard that we use for some automation, and the dashboard starting erroring out after some time. I started doing some digging and I saw that I was getting a SQL error, with no syntax errors. Usually that’s a data issue.
So I went to the event viewer, and found the error. String_agg() was too long. I’ve had this problem before and didn’t really know how to get around that.
So I went to the google, and apparently, string_Agg() will take on the properties of what’s being passed in? Or maybe it’s a maximum limit unless the field is larger? I’m not sure. But, if you cast the string as an nvarchar(MAX) then the string_agg() will ALSO be a max limit field, and it will stop the erroring out!
So if Change my field from
string_agg( ShipHead.PackNum , ', ')
To This.
string_agg( cast(ShipHead.PackNum as nvarchar(max)), ', ')
No more erroring out!
Anyways, I hope this is helpful. It was for me today.