自定义聚合函数报错:limit of 8000 bytes
sqlserver 的自定义聚合函数报错:
A .NET Framework error occurred during execution of user-defined routine or aggregate "StrJoin":
System.Data.SqlServer.TruncationException: Trying to convert return value or output parameter of size 15970 bytes to a T-SQL type with a smaller size limit of 8000 bytes.
System.Data.SqlServer.TruncationException:
at System.Data.SqlServer.Internal.CXVariantBase.StringToWSTR(String pstrValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)
at System.Data.SqlServer.Internal.CXVariantBase.SqlStringToWSTR(SqlString strValue, Int64 cbMaxLength, Int32 iOffset, EPadding ePad)
在网上找了半天都是存储过程的解决办法,不适合自定义聚合函数,发一个解决办法,希望有所帮助:
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToDuplicates = false,
IsInvariantToNulls = true,
IsInvariantToOrder = false,
IsNullIfEmpty = true,
MaxByteSize = -1
)]
public struct StrJoin : IBinarySerialize
{
private StringBuilder _result;
public void Init()
{
_result = new StringBuilder();
}
public void Accumulate(SqlString Value)
{
if (Value.IsNull)
{
return;
}
else
{
if (_result.Length > 0)
_result.Append(",");
_result.Append(Value.Value);
}
}
public void Merge(StrJoin Group)
{
_result.Append(Group._result);
}
[return: SqlFacet(MaxSize = -1)]
public SqlString Terminate()
{
if (_result.Length > 0)
{
return new SqlString(_result.ToString());
}
return new SqlString("");
}
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r)
{
_result = new StringBuilder(r.ReadString());
}
public void Write(System.IO.BinaryWriter w)
{
w.Write(_result.ToString());
}
#endregion
}
添加黄色部分就可以解决大小限制的问题。
努力过就不会后悔