CODEWITHSUNDEEP
sqlsql-servernvarchar
Arvind Agrahari
Arvind Agrahari

Reputation: 315

Size limit for nvarchar(max) , Print issue

I want to save XML record that length is more than 43679 char and its saved into table. Because when i'm checking its length its giving more than 43679 char but when i'm going to read or Print data of this column its only showing 43679 char.

The below image can help you to understand the problem.

enter image description here

like example 

declare @t table (a nvarchar(max));
insert into @t (a) values (REPLICATE(CONVERT(nvarchar(max),'a'),200000));
select LEN(a) from @t  -- result 200000
select print(a) from @t -- print only 43679 char.

Please help me out of this situation.

Upvotes: 0

Views: 1468

Answers (3)

Sean Pearce
Sean Pearce

Reputation: 1170

SQL Server Management Studio has a character limit when printing to the messages pane. There is a workaround to achieve what you need.

Using FOR XML to select your data using TYPE you can specify [processing-instruction] and give it a name. Your text will be presented as a link which you can open. This text will have wrappers and the name you specified. Here is an example.

declare @t table (a nvarchar(max));
insert into @t (a) values (REPLICATE(CONVERT(nvarchar(max),'a'),200000));
select LEN(a) from @t  -- result 200000
SELECT a [processing-instruction(TextOutput)] from @t FOR XML PATH(''), TYPE;

Upvotes: 1

krish
krish

Reputation: 881

This is a well known bug in SSMS, You can't paste more than 43679 char from a grid view column and unfortunately this limit can't be increased, You can get around this by displaying your Data in Xml format instead of nvarchar

Upvotes: 1

Rajesh Ranjan
Rajesh Ranjan

Reputation: 537

The datatypes like NCHAR, NVARCHAR, NVARCHAR(MAX) stores half of CHAR, VARCHAR & NVARCHAR(MAX). Because these datatype used to store UNICODE characters. Use these datatypes when you need to store data other then default language (Collation). UNICODE characters take 2 bytes for each character. That's why lenth of NCHAR, NVARCHAR, NVARCHAR(MAX) stores half of CHAR, VARCHAR & NVARCHAR(MAX).

Upvotes: 0

Related Questions