Does SQL Server Management Studio 'Messages' output window have a size limit?
If outputting mes开发者_如何学Csages with PRINT or RAISERROR is there a buffer size limit to the window and if so can it be changed.
I've looked everywhere and but can't see the wood for the trees!
Calification: I'm intested in the amount of data the output window can display before you start removing the earlier displayed messages. It could be that it just keeps going but there must be some limit, no?
I don't think there is a limit other than any limit imposed by the available memory in the machine. If there is one it is high enough to cater for most potential use cases. Take this SQL as an example:
declare @count int
set @count = 0
while (@count < 80000)
begin
print cast(@count as varchar(10)) + replicate('x', 7900)
set @count = (@count + 1)
end
This print 80000 rows of ~7900 characters. In my test each row is shown in the messages output window (takes a while to run though). So if there is a limit it is quite high.
EDIT
It is also worth mentioning that both PRINT and RAISERROR will truncate if the output string is too long. For example
print replicate('x', 7997) + 'end' -- Output : ...xxxxend
print replicate('x', 7998) + 'end' -- Truncated Output : ...xxxxen
declare @err varchar(max)
set @err = replicate('x', 2044) + 'end' -- Total length 2047
raiserror(@err, 1, 0) -- Output : ...xxxxend
set @err = replicate('x', 2045) + 'end' -- Total length 2048
raiserror(@err, 1, 0) -- Output Truncated with ellipses : ...xxxx...
From recollection, the messages and results windows will keep trying to display everything they're given, until SSMS hits a hard resource limit (e.g. memory) and displays an error message. I believe it then discards any further messages/results that the server returns.
The only limits in the output that I believe there is any control over is the number of characters displayed from long (var)char and XML columns. (65535 and 2MB being the respective defaults, in SSMS 2008).
精彩评论