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

  Kiến thức lập trình
`-- 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

New contributor

Oli Ivett is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.

LEAVE A COMMENT