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
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 BEGIN SELECT TOP 1 @chars = SUBSTRING(text,@current,@limit) FROM myTable PRINT @chars SET @current = @current + @limit END
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) AS /* Example: 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. */ DECLARE @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 BEGIN IF CHARINDEX(CHAR(10), @String) between 1 AND 4000 BEGIN SET @CurrentEnd = CHARINDEX(char(10), @String) -1 set @offset = 2 END ELSE BEGIN SET @CurrentEnd = 4000 set @offset = 1 END 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/