SQL Server 2008中的用户定义聚合 - 如何使用MaxByteSize = -1进行部署？

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.

我在这里(和其他地方)读到,在SQL Server 2008中,可以构建一个用户定义的聚合,它可以返回一个长度超过8000个字符的字符串。这正是我所需要的。

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.

据推测,该方法是将maxByteSize设置为-1而不是btw 1和8000;这应该允许任何大小高达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.

出于某种原因,显然,如果使用此设置,则无法直接从Visual Studio 2008进行部署;所以你需要手动部署。

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:

所以:我构建我的项目 - GroupConcat(它应该模拟MySQL的group_concat聚合器) - 它在项目的bin文件夹中提供了一个文件“SqlClassLibrary.dll”。根据上面链接页面上的说明,我在SQL Server中构建程序集。该命令执行成功。但是,当我尝试实际使用groupconcat聚合器时:

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

从部门的projectleads组中选择部门,dbo.groupconcat(projectNumber)

...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?

......它说无法找到它。如果我将maxByteSize设置为8000并直接从VS2008内部署,这一切都正常,但我需要> 8000。谁知道我做错了什么?

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.

注意:我特别需要有一个groupconcat聚合器函数,而不是使用我经常看到的一些SQL Server技巧。

2 个解决方案

#1

Alternately, you can use MaxSize property of SqlFacetAttribute to to indicate the varchar size. Note that in the example below I applied this attribute to the SqlString parameters in the Accumulate method and to the return value of the Terminate method. This results in the following SQL signature:

或者,您可以使用SqlFacetAttribute的MaxSize属性来指示varchar大小。请注意,在下面的示例中,我将此属性应用于Accumulate方法中的SqlString参数以及Terminate方法的返回值。这会产生以下SQL签名:

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

AGGREGATE [dbo]。[Concatenate](@ value nvarchar(max),@ order int,@ selector 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);
}

I don't know if this is any more "correct" than what you ended up doing, but it seems more natural.

我不知道这是否比你最后做的更“正确”,但似乎更自然。

#2

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:

想出来......在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

That does it.

这样做。

#1