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

• Oliiii

  SSCertifiable

  Points: 5328

  More actions

  October 5, 2010 at 6:12 am

  #1231613

  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

  October 5, 2010 at 6:19 am

  #1231618

  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

  October 5, 2010 at 6:56 am

  #1231644

  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

  October 5, 2010 at 8:14 am

  #1231725

  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 varchar(max);

  SET @Max = REPLICATE('X',8000);

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

  PRINT @Max

  PRINT CHAR(13)+CHAR(13)

  PRINT REPLICATE('*',30)

  EXEC dbo.LongPrint @Max