CLR UDAGG Returns Severe Error On Certain Cases

Hi peoples,



I have created a custom CLR user define aggregate function based on the example that i found at http://msdn2.microsoft.com/en-us/library/ms131056(SQL.90).aspx.

It works great until i discovered that it will failed if i try to do either one of the following:

query a large records e.g: more than 4k records
has IS NULL in my where clause e.g: WHERE myConcatenatedFld IS NULL

Other than these two, it works perfectly fine.



Here is the error that i got:



Msg 0, Level 11, State 0, Line 0

A severe error occurred on the current command. The results, if any, should be discarded.



I have attached the Concatenate class that i used in my UDAGG.






Code Snippet

using System;

using System.Data;

using System.Data.SqlClient;

using System.Data.SqlTypes;

using Microsoft.SqlServer.Server;

using System.IO;

using System.Text;



[Serializable]

[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(

Format.UserDefined, //use custom serialization to serialize the intermediate result

IsInvariantToNulls = true, //optimizer property

IsInvariantToDuplicates = false, //optimizer property

IsInvariantToOrder = false, //optimizer property

MaxByteSize = 8000) //maximum size in bytes of persisted value

]

public class Concatenate : IBinarySerialize

{

/// <summary>

/// The variable that holds the intermediate result of the concatenation

/// </summary>

private StringBuilder intermediateResult;

private const int MAX_STRING_LEN = 4000;

/// <summary>

/// Initialize the internal data structures

/// </summary>

public void Init()

{

this.intermediateResult = new StringBuilder();

}

/// <summary>

/// Accumulate the next value, not if the value is null

/// </summary>

/// <param name="value"></param>

public void Accumulate(SqlString value)

{

//Dont do anything if the input is null or empty

if (value.IsNull || value.Value.Length == 0)

{

return;

}

//Skip concatenation if the output exceed 4000 chars

if (this.intermediateResult.Length + value.Value.Length > MAX_STRING_LEN)

{

return;

}

//Concatenate output

if (!this.intermediateResult.ToString().Contains(value.Value.Trim()))

this.intermediateResult.Append(value.Value.Trim()).Append(", ");

}

/// <summary>

/// Merge the partially computed aggregate with this aggregate.

/// </summary>

/// <param name="other"></param>

public void Merge(Concatenate other)

{

//Skip concatenation if the output exceed 4000 chars

if (this.intermediateResult.Length + other.intermediateResult.Length > MAX_STRING_LEN)

{

return;

}

//Concatenate computed output

this.intermediateResult.Append(other.intermediateResult);

}

/// <summary>

/// Called at the end of aggregation, to return the results of the aggregation.

/// </summary>

/// <returns></returns>

public SqlString Terminate()

{

string output = string.Empty;

//delete the trailing comma, if any

if (this.intermediateResult != null

&& this.intermediateResult.Length > 0)

{

output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 2);

}

return new SqlString(output);

}

public void Read(BinaryReader r)

{

intermediateResult = new StringBuilder(r.ReadString());

}

public void Write(BinaryWriter w)

{

w.Write(this.intermediateResult.ToString());

}

}





Anyone has any idea on how to solve this issue?. Thanks