it seems that the PRINT function will not print past 4000 characters for nvarchar and 8000 character for char in management studio. Is there any way around this?
here is some demo code to prove it
This is a documented limitation of the print command in BOL. As a workaround, I use this stored procedure that I wrote:
This was originally posted on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/
I must admit to being a bit confused with this, but sadly I dont have time to envestigate it any further at the moment.
I started playing with the code and altered it to
And get the results
so... a variable can hold more than 8000 (16001 in this case). Using Copy/Paste to get the @z result into excel shows there are indeed 16001 characters in the variable
Maybe you can used the logic this is exhibiting to get around your problem?
Changing the line
[EDIT2] Gaaah, I;ve got caught up in it ..!
REPLICATE help in BoL ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_6tsql/html/0cd467fb-3f22-471a-892c-0039d9f7fa1a.htm states that
I think REPLICATE AND/OR LEN have a limitation too:
Couldn't set anything bigger than 8000 on both varchar and nvarchar.
The same way you cant declare text variables, maybe you can't manipulate any string variable higher then 8000.
Even setting results to text, the select statement only returned the initial 8000 chars ( and the SSMS output to text limit is set to the max value of 8192 ).
answered Nov 18 '09 at 09:17 AM
These limitations are documented in BOL. I guess they must be legacy hangovers still present internally in
Some potential workarounds, but they might not fit your situation:
Edit : As noted by some of the other answers,