M
Michael C
Hi All,
I'm trying to do some CLR integration with sql server 2005. I've created my
own stored proc in c# and got that working but now I'm trying to create my
own user defined aggregate. I've download a sample and copy/pasted it into a
new project. I can compile and add it to sqlserver via management studio
with no problems. But when I execute the CREATE AGGREGATE statement I get
this error:
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG
specification due to method 'Accumulate'.
Every search result I find refers to VB.Net and suggests removing the
ComClass attribute. However I am using C# which does not have the ComClass
attribute and this attribute is definately not on my class (or any other com
attribute). Is there any solution to this? I've tried all the obvious stuff
such as giving the assembly a strong name, putting ComVisible(false) on the
class and functions etc, ticking/unticking ComVisible at the project level.
Thanks in advance,
Michael
Here's the link to the sample:
http://msdn.microsoft.com/en-us/library/ms131056.aspx
Here's the C# code I'm using
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr 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;
/// <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)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(',');
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
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 - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}
I'm trying to do some CLR integration with sql server 2005. I've created my
own stored proc in c# and got that working but now I'm trying to create my
own user defined aggregate. I've download a sample and copy/pasted it into a
new project. I can compile and add it to sqlserver via management studio
with no problems. But when I execute the CREATE AGGREGATE statement I get
this error:
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG
specification due to method 'Accumulate'.
Every search result I find refers to VB.Net and suggests removing the
ComClass attribute. However I am using C# which does not have the ComClass
attribute and this attribute is definately not on my class (or any other com
attribute). Is there any solution to this? I've tried all the obvious stuff
such as giving the assembly a strong name, putting ComVisible(false) on the
class and functions etc, ticking/unticking ComVisible at the project level.
Thanks in advance,
Michael
Here's the link to the sample:
http://msdn.microsoft.com/en-us/library/ms131056.aspx
Here's the C# code I'm using
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr 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;
/// <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)
{
if (value.IsNull)
{
return;
}
this.intermediateResult.Append(value.Value).Append(',');
}
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(Concatenate other)
{
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 - 1);
}
return new SqlString(output);
}
public void Read(BinaryReader r)
{
intermediateResult = new StringBuilder(r.ReadString());
}
public void Write(BinaryWriter w)
{
w.Write(this.intermediateResult.ToString());
}
}