December 29, 2015 at 11:24 am
I am trying to Select data from a table for nvarchar max datatype column.
it gives me 43769 charcters only. When I check the column lengh it gives me 238894 length.
CREATE TABLE Test_NtextData
(
Data NVARCHAR(MAX)
)
GO
DECLARE @Ntext NVARCHAR(MAX)=''
DECLARE @I INT=1
WHILE (@I<=50000)
BEGIN
SET @Ntext=@Ntext+CONVERT(NVARCHAR(100),@I)
SET @I=@I+1
END
INSERT INTO Test_NtextData
SELECT @Ntext
SELECT LEN(Data) from Test_NtextData ---238894 character length
SELECT Data from Test_NtextData ---it gives 43679 Charters only
My concern is why i am getting 43679 characters in select insted of 238894 charcter. and if i wanted to use that entire column data for further processing then how can i use it.
December 29, 2015 at 11:37 am
Your client may be limiting the number of characters returned. It may limit either the total returned or the amount returned per column. For instance, SSMS limits output to 65,535 characters when output to grid. Try the following instead:
SELECT Data from Test_NtextData
FOR XML PATH('')
This will output the data as XML. SSMS allows XML text up to 2GB.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 29, 2015 at 11:47 am
drew.allen (12/29/2015)
Your client may be limiting the number of characters returned. It may limit either the total returned or the amount returned per column. For instance, SSMS limits output to 65,535 characters when output to grid. Try the following instead:
SELECT Data from Test_NtextData
FOR XML PATH('')
This will output the data as XML. SSMS allows XML text up to 2GB.
Drew
This works. Interesting how the actual number of characters returned is 43,679 though (regardless of the 65,535 limit). I tried setting the limit to 30,000 and that worked perfectly.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
December 29, 2015 at 1:36 pm
Phil Parkin (12/29/2015)
drew.allen (12/29/2015)
Your client may be limiting the number of characters returned. It may limit either the total returned or the amount returned per column. For instance, SSMS limits output to 65,535 characters when output to grid. Try the following instead:
SELECT Data from Test_NtextData
FOR XML PATH('')
This will output the data as XML. SSMS allows XML text up to 2GB.
Drew
This works. Interesting how the actual number of characters returned is 43,679 though (regardless of the 65,535 limit). I tried setting the limit to 30,000 and that worked perfectly.
Seems to be a bug/issue in SSMS. It was reported (https://connect.microsoft.com/SQLServer/feedback/details/499608/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode), and closed as not reproducible.
I've reproduced it on all SSMS versions I have starting with 2008 (in 2005 I continue to get the expected number of characters past 43,679 until the maximum) and going to 2014.
For ease of reproducing:
SELECT REPLICATE(CAST('A' AS NVARCHAR(MAX)),65535)
Cheers!
December 29, 2015 at 2:29 pm
Jacob Wilkins (12/29/2015)
Seems to be a bug/issue in SSMS. It was reported (https://connect.microsoft.com/SQLServer/feedback/details/499608/ssms-can-not-paste-more-than-43679-characters-from-a-column-in-grid-mode), and closed as not reproducible.
Precisely why I don't even bother with CONNECT except to see what they haven't fixed and won't. That way I know what to do workarounds for. 😉
--Jeff Moden
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply