SQL 2008 query result message tab display limit

Question

Post reply

SQL 2008 query result message tab display limit

mpsathiskumar

SSC-Addicted

Points: 400
More actions
October 5, 2010 at 6:02 am

#224716

Hi All,
I need to print the message more than 8000 characters. But sql server 2008 can't support more than 8000 characters. It's allowing and display only 8000 characters alone.
Can you please anyone tell me how to change this limitations?
Sample query:
Print(REPLICATE('1',8005))
This query returns only 8000 single ones like (11111...), but it should be display 8005 single ones.
Thanks & Regards,
Sathiskumar.P

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Oliiii SSCertifiable Points: 5328 More actions · Answer 1

The replicate command truncate to 8000 characters if you are not using varchar(max) or nvarchar(max).

If you use varchar(max) it works fine though:

select len(replicate(convert(varchar(max),1),8005))

Output: 8005

mpsathiskumar SSC-Addicted Points: 400 More actions · Answer 2

No, I want to display 8005 single ones (111111111111.....) in query result message tab and I have not problem for display the 8005 count.

Sample query:

Print(REPLICATE('1',8005))

This query returns only 8000 single ones in the query result message tab like (11111...), but it should be display 8005 single ones in that message tab not count.

Thanks & Regards,

Sathiskumar.P

steveb. SSC-Forever Points: 46733 More actions · Answer 3

This query returns only 8000 single ones in the query result message tab like (11111...), but it should be display 8005 single ones in that message tab not count.

You wont be able to display more than 8000 characters with PRINT no matter how much you want to

read the PRINT specs here , max is 8000 non unicode chars

http://msdn.microsoft.com/en-us/library/ms176047.aspx

Also this is a strange request, why would you want to do this?

winash SSChampion Points: 11446 More actions · Answer 4

You can use this procedure http://www.sqlservercentral.com/scripts/Print/63240/ to print out your data in chunks of 8000...I've used it in the past to debug dynamic SQL statements that way exceed 8000 characters and which fail at some obscure point(yep - that was fun)

DECLARE @max-2 varchar(max);

SET @max-2 = REPLICATE('X',8000);

SET @max-2 = @max-2+'----This will not show up in PRINT';

PRINT @max-2

PRINT CHAR(13)+CHAR(13)

PRINT REPLICATE('*',30)

EXEC dbo.LongPrint @max-2