User-Defined Aggregate in SQL Server 2008 - How to deploy with MaxByteSize = -1?

Question

I read here (and elsewhere) that it's possible, in SQL Server 2008, to build a user-defined aggregate which can return a string longer than 8000 characters. This is exactly what I need.

Supposedly, the method is to set maxByteSize to -1 instead of a number btw 1 and 8000; this should allow any size up to 2GB.

For some reason, apparently, you can't deploy straight from Visual Studio 2008 if you use this setting; so you need to manually deploy.

So: I build my project - GroupConcat (which is supposed to simulate MySQL's group_concat aggregator) - which gives me, in the project's bin folder, a file "SqlClassLibrary.dll". Per the instructions on the above-linked page, I build the assembly in SQL Server. The command executes successfully. However, when I try to actually use the groupconcat aggregator:

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

...it says it can't be found. This all works fine if I set maxByteSize to 8000 and deploy directly from within VS2008, but I need >8000. Anybody know what I'm doing wrong?

Thanks -dan

NOTE: I do specifically need to have a groupconcat aggregator function rather than using some of the SQL Server tricks I've often seen.

Answer 1

Figured it out... After building the solution in Vis Studio, assuming I've dropped the .dll it creates into c:\temp and called it 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

That does it.