I am building a comma-separated string for a single column’s value that I will then pass to an OPENQUERY so that I can optimize my query (smaller left table, very large right table).

But my problem is with building the string. The following does not pull all the values from the table.

DECLARE @VAR VARCHAR(MAX)

SELECT @VAR = (SELECT STRING_AGG(CAST(mu.colName AS VARCHAR(MAX)), ''''',''''') 
               FROM tableName mu)

The tableName has around 15,000 rows for the ColName. I was under the impression that VARCHAR(MAX) should be able to hold all these values since VARCHAR(MAX) has an upper limit of 2 GB. The values of colName are all INT‘s.