Custom aggregate in SQL Server 2008 - How to deploy using MaxByteSize = -1?

Question

I read here (and elsewhere) that in SQL Server 2008 it is possible to create a custom population that can return a string longer than 8000 characters. This is exactly what I need.

Presumably, the method should set maxByteSize to -1 instead of btw 1 and 8000; This should allow a size of up to 2 GB.

For some reason, apparently you cannot install directly from Visual Studio 2008 if you use this option; so you need to manually deploy.

So: I create my project - GroupConcat (which should mimic the aggregator MySQL group_concat) - which gives me the file "SqlClassLibrary.dll" in the project bin folder. Following the instructions on the page above, I create the assembly in SQL Server. The command is successful. However, when I try to actually use the groupconcat aggregator:

select department, dbo.groupconcat(projectNumber) from projectleads group by department

... he says that it is impossible to find. All this works fine if I install maxByteSize on 8000 and deploy directly from VS2008, but I need> 8000. Does anyone know what I'm doing wrong?

Thanks Dan

NOTE. I especially need to have the groupconcat aggregator function, and not use some of the SQL Server tricks I often saw.

Answer 1

... Vis Studio, , .dll, c:\temp GroupConcat.dll:

CREATE ASSEMBLY GroupConcat from 'C:\temp\GroupConcat.dll' with permission_set = safe
GO

CREATE AGGREGATE groupconcat(@input nvarchar(max))
RETURNS nvarchar(max)
EXTERNAL NAME GroupConcat
GO

.

Answer 2

MaxSize SqlFacetAttribute, varchar. , SqlString Accumulate Terminate. SQL:

AGGREGATE [dbo].[Concatenate] (@value nvarchar(max), @order int, @seperator nvarchar(max)) RETURNS nvarchar(max)

[Serializable]
[SqlUserDefinedAggregate(
    Format.UserDefined,
    IsInvariantToOrder      = true,
    IsInvariantToNulls      = true,
    IsInvariantToDuplicates = false,
    IsNullIfEmpty           = false,
    MaxByteSize             = -1)]
public struct Concatenate : IBinarySerialize
{
    public void Init();

    public void Accumulate([SqlFacet(MaxSize = -1)] SqlString value,
                                                    SqlInt32  order,
                           [SqlFacet(MaxSize = -1)] SqlString seperator);

    public void Merge(Concatenate group);

    [return: SqlFacet(MaxSize = -1)]
    public SqlString Terminate();

    public void Read(BinaryReader r);

    public void Write(BinaryWriter w);
}

, "", , , .