Problem
You receive “TruncationException: Trying to convert return value or output parameter of size XXX bytes to a T-SQL type with a smaller size limit of 8000 bytes” error while trying to execute CLR stored procedure in SQL Server 2008.
Explanation
CLR stored procedure is trying to return via output parameter string that exceeds 8000 bytes. Declaring output parameter as nvarchar(MAX) is not enough to overcome 8000 bytes limit.
Solution
In order to return strings via CLR procedures that are more than 8000 bytes you need to apply a special attribute called SqlFacet(MaxSize) to the output parameter.
Example in VB.NET:
Public Shared Sub MySP(<SqlFacet(MaxSize:=-1)> ByRef ret As SqlString)
Example in C#:
public static SqlString MySP([param: SqlFacet(MaxSize=-1)] SqlString ret)