Cannot create a row of size 11616 which is greater than the allowable maximum row size of 8060. When using dynamic SQL to create dynamic columns

Question

-- Generate dynamic column list
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(Attribute) 
                   FROM AttributeValue 
                   WHERE ProjectID = @ProjectId
                   FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

-- Define data types for the columns
DECLARE @columnDefinitions NVARCHAR(MAX);
SET @columnDefinitions = STUFF((SELECT DISTINCT ',' + QUOTENAME(Attribute) + ' NVARCHAR(100)' 
                                FROM AttributeValue 
                                WHERE ProjectID = @ProjectId
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '');

SET @createTableQuery = 'CREATE TABLE ##Attributes (AssetItemID INT, ' + @columnDefinitions + ');';
SET @query = 'INSERT INTO ##Attributes
                  SELECT AssetItemID, ' + @cols + '
                  FROM 
                      (SELECT i.AssetItemID, av.Attribute, i.Value
                       FROM Item2Item i
                       INNER JOIN AttributeValue av ON av.AttributeValueID = i.SubTypeAttributeID
                       INNER JOIN AssetItem (NOLOCK) a ON a.AssetItemID = i.AssetItemID
                       WHERE av.ProjectID = ' +CAST(@ProjectId AS VARCHAR(10))+'
                         AND a.Owner = '+ QUOTENAME(@Package,'''') +' 
                    ) src
                    PIVOT (
                        MAX(Value) FOR [Attribute] IN (' + @cols + ')
                    ) AS piv;';`

I have tried setting columnDefinitions when defining the columns to even NVARCHAR(1) and doesn't work have around 450 attributes but still states row size to big

Answer 1

This problem that you are having usually happens when the combined size of your columns exceeds the SQL Server row size limit. In your case, having around 450 attributes with NVARCHAR(100) each contributes to this.

To make this right, try reducing the column size, maybe to NVARCHAR(50), to shrink the overall row size. else you can do vertical partitioning, like breaking your table into smaller ones with fewer columns.