So I have a stored procedure in a SQLServer 2005 database, which retrieves data from a table, format the data as a string and put it into a varchar(max) output variable.
However, I notice that although len(s) reports the string to be > 8,000, the actual string I receive (via SQLServer output window) is always truncated to < 8,000 bytes.
Does anybody know what the causes of this might be ? Many thanks.
The output window itself is truncating your data, most likely. The variable itself holds the data but the window is showing only the first X characters.
If you were to read that output variable from, for instance, a .NET application, you'd see the full value.
Are you talking about in SQL Server Management Studio? If so, there are some options to control how many characters are returned (I only have 2008 in front of me, but the settings are in Tools|Options|Query Results|SQL Server|Results to Grid|Maximum Characters Retrieved and Results to Text|Maximum number of characters displayed in each column.
The data is all there, but management studio isn't displaying all of the data.
In cases like this, I've used MS Access to link to the table and read the data. It's sad that you have to use Access to view the data instead of Management Studio or Query Analyzer, but that seems to be the case.
However, I notice that although len(s) reports the string to be > 8,000
I have fallen for the SQL Studio issue too :) but isn't the maximum length of varchar 8,000 bytes, or 4,000 for nvarchar (unicode).
Any chance the column data type is actually text or ntext and you're converting to varchar?