January 14, 2011 at 10:08 am
I'm creating a custom aggregate (eventually a mode). I kept getting an error, so I pared the C# down to a bare minimum. The error comes up when I try to use the aggregate. The only object I'm referencing in Terminate is items, so I guess it must be that, but I don't understand why. I'm initializing it in Init; even if it were empty I thought that should be enough for the items.count to be 0.
--------------------------------------------------------------------------------
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "PercBytes":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
at PercBytes.Terminate()
--------------------------------------------------------------------------------
Here's the C# code.
[Serializable]
[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 PercBytes : IBinarySerialize
{
private StringBuilder intermediateResult;
private ArrayList items;
public void Init()
{
this.intermediateResult = new StringBuilder();
this.items = new ArrayList();
}
public void Accumulate(SqlString value)
{
if (value.IsNull)
{
this.items.Add("null");
}
else
{
this.items.Add(value);
}
}
public void Merge(PercBytes other)
{
this.items.Add(other.items);
}
public int Terminate()
{
return items.Count;
}
public void Read(BinaryReader reader)
{
intermediateResult = new StringBuilder(reader.ReadString());
}
public void Write(BinaryWriter writer)
{
writer.Write(this.intermediateResult.ToString());
}
}
Here's the SQL part
CREATE ASSEMBLY CLRLib FROM 'G:\CLRLib.dll'
GO
create AGGREGATE PercBytes (@input nvarchar(max)) RETURNS int
EXTERNAL NAME clrlib.PercBytes
GO
select dbo.PercBytes(BytesSent),App_ID from dbo.fact_runid_1(nolock)
and BytesSent != 0
group by App_ID
March 17, 2011 at 9:58 am
Hi...
You need to change this:
MaxByteSize = 8000
to
MaxByteSize = -1
(mean to the max size)
Regards... And, hope that will help...
April 25, 2011 at 11:13 am
SQL Server spawns multiple aggregates at a time and that means some of them may have their Terminate called prematurely. You just need a null check for the items field in the Terminate.
"The way to get things done is not to mind who gets the credit for doing them." - Benjamin Howett
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply