Sql Server User Defined Table Type column truncated

Question

We've been using a user defined table for quite a while as a way to transfer varying number of search criteria from a .Net MVC application to stored procedures.

CREATE TYPE [dbo].[SearchCriteriaParametersTableType] AS TABLE
(
    ...
    [SearchCriteriaSequence]    int,
    [SqlParameterStringValue]   varchar(MAX),
    ...
)

We recently discovered a bug that I haven't been able to figure out - when the SqlParameterStringValue is a long string, it's truncated after 501 characters. When debugging on the web app side, the full string is populated in that property, but viewed in Sql profiler, the truncated value is in the insert statement (only a portion shown), and the length of that is 501 characters:

declare @p1 dbo.SearchCriteriaParametersTableType
insert into @p1 values(...,N'23491,23492,23494,...,26930,26',...)

When I shorten the column width to say 50, and attempt to pass in a longer value, I get a truncation error. So it seems that it's not the column width that's the constraint, somewhere the passed values is being truncated.

This is running in an MVC 4 web application, the sql is accessed by a new SqlCommand with it's own connection. The database is Sql Server 2012.

Here is how the sql parameter is built:

var searchCriteriaParameter = new SqlParameter
{
    ParameterName = parameterName,
     Direction = ParameterDirection.Input,
     SqlDbType = SqlDbType.Structured,
     TypeName = SearchCriteriaParameterType.SearchCriteriaParameterTypeName,
     Value = CriteriaParametersCollection(populatedCriteriaSummaryItems)
     };
 sqlParameters.Add(searchCriteriaParameter);

  public SearchCriteriaCollection CriteriaParametersCollection(IEnumerable<CriteriaSummaryItem> criteriaValues)
    {
        var list = new SearchCriteriaCollection();
        var i = 0;
        foreach (var criteriaValue in criteriaValues)
        {
            list.Add(criteriaValue.AsSearchCriteriaParameterType(criteriaValue, i));
            i++;
        }
        return list;
    }

 public SearchCriteriaParameterType AsSearchCriteriaParameterType(CriteriaSummaryItem filterItem, int i)
    {
        var mode = filterItem.CriteriaType;
        if (!string.IsNullOrEmpty(mode) && !string.IsNullOrEmpty(TextSearchMode))
        {
            mode += "_" + TextSearchMode;
        }

        var searchCriteriaParameterType = new SearchCriteriaParameterType
        {
            SearchCriterionKey = filterItem.SearchCriterionKey,
            SearchCriteriaSequence = i,
            SqlParameterName = filterItem.SqlParameterName,
            SqlParameterStringValue = filterItem.IDValues,
            CriteriaSearchMode = mode
        };
        return searchCriteriaParameterType;
    }

Any ideas where to look?

Answer 1

This is most likely due to how the SqlMetaData for that field is defined in the SqlDataRecord. That portion of the code (i.e. the definition of SearchCriteriaCollection) is not shown, but I can say that you need to set the Size property of the SqlMetaData for SqlParameterStringValue and that it should be SqlMetaData.Max. For example:

new SqlMetaData("SqlParameterStringValue", SqlDbType.VarChar, SqlMetaData.Max)