I have to output the items in a text area using MS Query Analyzer. I've attempted this:

select top 1 text from myTable

(where text is really a text area)


DECLARE @data VarChar(8000) 
select top 1 @data = text from myTable
PRINT @data

The first prints just the first 2000 approximately figures and also the second only prints the very first 8000 figures. Can there be any method of getting all the text?


  • must use SQL Server 7

I do not think you should use varchar(MAX) in MSSQL7, so here's something which provides you with all of the data (note, what I am understanding is you want to aesthetically begin to see the data, and also you aren't going place it inside a variable or give it back).

So, this can print from the entire string so that you can aesthetically see what's within the area:

DECLARE @limit as int,
    	@charLen as int,
    	@current as int,
    	@chars as varchar(8000)

SET @limit = 8000

SELECT  TOP 1 @charLen = LEN(text)
FROM    myTable

SET @current = 1

WHILE @current < @charLen
    SELECT	TOP 1 @chars = SUBSTRING(text,@current,@limit)
    FROM	myTable
    PRINT @chars

    SET @current = @current + @limit

I've not used Query Analyzer shortly, nevertheless, you may change all the figures displayed within the results window within the Options window. Begin to see the MSDN documentation.


Make use of this saved proc. The only real lower side is you receive a line break every 8000 charachters :(

CREATE PROCEDURE [dbo].[LongPrint]
      @String NVARCHAR(MAX)



exec LongPrint @string =
'This String
Exists to test
the system.'


/* This procedure is designed to overcome the limitation
in the SQL print command that causes it to truncate strings
longer than 8000 characters (4000 for nvarchar).

It will print the text passed to it in substrings smaller than 4000
characters.  If there are carriage returns (CRs) or new lines (NLs in the text),
it will break up the substrings at the carriage returns and the
printed version will exactly reflect the string passed.

If there are insufficient line breaks in the text, it will
print it out in blocks of 4000 characters with an extra carriage
return at that point.

If it is passed a null value, it will do virtually nothing.

NOTE: This is substantially slower than a simple print, so should only be used
when actually needed.

               @CurrentEnd BIGINT, /* track the length of the next substring */
               @offset tinyint /*tracks the amount of offset needed */

set @string = replace(  replace(@string, char(13) + char(10), char(10))   , char(13), char(10))

WHILE LEN(@String) > 1

IF CHARINDEX(CHAR(10), @String) between 1 AND 4000

SET @CurrentEnd =  CHARINDEX(char(10), @String) -1
           set @offset = 2
           SET @CurrentEnd = 4000
            set @offset = 1

PRINT SUBSTRING(@String, 1, @CurrentEnd)

set @string = SUBSTRING(@String, @CurrentEnd+@offset, 1073741822)

END /*End While loop*/

It was initially published on SQLServerCentral.com at http://www.sqlservercentral.com/scripts/Print/63240/