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.

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)

3 thoughts on “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.”

  1. Is it possible to use an app.config with a CLR Stored Procedure? I want to call another DB from my CLR stored procedure – if this site doesnt send a confirmation to my email, can someone contact me for solution?

Leave a Reply

Your email address will not be published. Required fields are marked *