January 28, 2010 at 11:53 pm
Hi There,
Please help me with the below issue.
I have a table with so may columns (200 in fact). I am trying to append all the columns into an NVARCHAR variable. The Max value an NVARCHAR can store is 2^31-1 bytes where as the NVARCHAR variable is not storing any data beyond 4000 bytes. Please help me find the mistake i am doing. Below is the sample code to create a table with 200 columns and to append the column names to a variable
Code to create a table
DECLARE @COLNAME NVARCHAR(30) = 'ABCDEFGHIJKLMNOPQRSTUVWHYZ',
@int INT = 1,
@SQL NVARCHAR(200)
CREATE TABLE MANYCOLUMNS( ABCDEFGHIJKLMNOPQRSTUVWHYZ NVARCHAR(20))
WHILE @int <= 200
BEGIN
SET @COLNAME = @COLNAME+CAST(@int AS CHAR)
SET @SQL = 'ALTER TABLE MANYCOLUMNS ADD '+@COLNAME+' NVARCHAR(2)'
SET @COLNAME = 'ABCDEFGHIJKLMNOPQRSTUVWHYZ'
EXEC (@SQL)
SET @int += 1
END
Code to append the column name to a variable @Column_List
DECLARE @column_position int,
@Column_List NVARCHAR(MAX),
@Column_Name NVARCHAR(MAX),
@table_name NVARCHAR(800)= 'MANYCOLUMNS',
@Data_Type NVARCHAR(128)
SET @column_position = 0
SET @Column_List = CAST('' AS NVARCHAR(MAX))
SET @Column_Name = CAST('' AS NVARCHAR(MAX))
SET @Data_Type = ''
SELECT @column_position = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name
WHILE @column_position IS NOT NULL
BEGIN
SELECT @Column_Name = CAST(QUOTENAME(COLUMN_NAME) AS NVARCHAR(MAX)),
@Data_Type = DATA_TYPE
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE ORDINAL_POSITION = @column_position AND
TABLE_NAME = @table_name
SET @Column_List = CAST(@Column_List AS NVARCHAR(MAX))+ CAST(@Column_Name AS NVARCHAR(MAX))+ ','
PRINT LTRIM(RTRIM(CAST(@Column_List AS NVARCHAR(MAX))))
SELECT @column_position = MIN(ORDINAL_POSITION)
FROM INFORMATION_SCHEMA.COLUMNS (NOLOCK)
WHERE TABLE_NAME = @table_name AND
ORDINAL_POSITION > @column_position
END
Ideally the print statement should print list of all the columns but it is printing first 4000 characters only and when i change the entire code to use VARCHAR datatype then only first 8000 characters are getting printed.
Please let me know why can't we store all 2^31-1 bytes of data in VARCHAR or NVARCHAR variable when it is declared to its MAX capacity.
January 29, 2010 at 12:00 am
January 29, 2010 at 12:03 am
Just curious, why are you trying to append all the column names into a variable?
Thanks
January 29, 2010 at 12:12 am
Thank you very much, thats a valuable info, I have no idea that PRINT statement had a limitation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply