0

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?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Reuven Trabin
  • 451
  • 7
  • 18
  • Can you run a SQL profiler trace to see that the entire string is being passed through to SQL Server? – Jane S Oct 07 '14 at 21:23
  • Can you show the code that sets up the SqlCommand and the parameters? – Aaron Bertrand Oct 07 '14 at 21:53
  • 1
    Jane S: the 2 line snippet above is from sql profiler. I choose all the t-sql and stored procdure events and I didn't see any other statements besides the ones above that transfer values to that column. If there is another setting that you have in mind to show, let me know – Reuven Trabin Oct 08 '14 at 15:48
  • Aaron - I added the addition code above. At the debug breakpoints, just before the sql command is executed, the full string look populated. – Reuven Trabin Oct 08 '14 at 16:03

1 Answers1

2

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)
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171