2

I've created a SQLCLR Assembly and added it, when I run the T-SQL command: 

CREATE AGGREGATE Concat (@input nvarchar(max))
RETURNS nvarchar(max)
EXTERNAL NAME Sql_ClrAggregates.Concatenate;

I get the error:

Msg 6558, Level 16, State 1, Line 1
CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification due to method 'Accumulate'.

What is the UDAGG specification?

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Stephen Turner
  • 7,125
  • 4
  • 51
  • 68

2 Answers2

2

I would say that the UDAGG Specification is the document Requirements for CLR User-Defined Aggregates (Linked to 2014 version)

It states what you are required to do. Your error states that the problem is with the Accumulate method, and so we check the requirements:

input_type should be the managed SQL Server data type equivalent to the native SQL Server data type specified by input_sqltype in the CREATE AGGREGATE statement.

Of course, your answer contains a little more detail about the specifics of where you've gone wrong, which I couldn't comment on since you didn't show the C# code in the question.

There is no formal name given to "bit supplied between CREATE AGGREGATE and EXTERNAL" as your answer implies. The complete syntax for CREATE AGGREGATE is described as:

CREATE AGGREGATE [ schema_name . ] aggregate_name 
        (@param_name <input_sqltype> 
        [ ,...n ] )
RETURNS <return_sqltype>
EXTERNAL NAME assembly_name [ .class_name ]

<input_sqltype> ::= 
        system_scalar_type | { [ udt_schema_name. ] udt_type_name }

<return_sqltype> ::= 
        system_scalar_type | { [ udt_schema_name. ] udt_type_name }

As a further example, this aggregate:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;

[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.Native)]
public struct SqlAggregate1
{
    public void Accumulate(SqlString Value)
    {
        // Put your code here
    }

    public void Merge (SqlAggregate1 Group)
    {
        // Put your code here
    }

    public SqlString Terminate ()
    {
        // Put your code here
        return new SqlString (string.Empty);
    }

    // This is a place-holder member field
    public int _var1;
}

With this SQL:

CREATE AGGREGATE [dbo].[SqlAggregate1](@Value NVARCHAR (MAX))
    RETURNS NVARCHAR (MAX)
    EXTERNAL NAME [Database2].[SqlAggregate1];

Produces the error:

Msg 6558, Level 16, State 1, Line 1 CREATE AGGREGATE failed because type 'SqlAggregate1' does not conform to UDAGG specification due to method 'Init'.

Despite there being absolute agreement between the definitions of Accumulate and Terminate and "the bit supplied between CREATE AGGREGATE and EXTERNAL NAME".

Damien_The_Unbeliever
  • 234,701
  • 27
  • 340
  • 448
1

The UDAGG specification partly referrs to the bit supplied between CREATE AGGREGATE and EXTERNAL NAME i.e.

Concat (@input nvarchar(max)) RETURNS nvarchar(max)

Check that this matches the method in the assembly, in c# this would be 

public void Accumulate(SqlString Value)
{ ... }

with the terminate function returning the result: 

public SqlString Terminate()
{ ... }

You'll get a similar error if you add public fields, methods etc. that don't meet the requirements but that field will be mentioned specifically.

Stephen Turner
  • 7,125
  • 4
  • 51
  • 68
  • I'm sorry, but this is incorrect. See the example at the bottom of my answer where my `Accumulate` and `Terminate` methods are in agreement with the `CREATE AGGREGATE` statement but we still get an error stating that the class doesn't conform to the UDAGG specification. – Damien_The_Unbeliever Jun 26 '15 at 08:57
  • It is incorrect in stating that "the UDAGG specification is the bit supplied between CREATE AGGREGATE and EXTERNAL NAME" - that is, you're attempting to define "the UDAGG specification" but the definition you've supplied is incorrect. It may have solved your problem but it doesn't answer the question you've asked, which is "what is the UDAGG specification?" – Damien_The_Unbeliever Jun 26 '15 at 09:06
  • Thanks, I've clarified my answer and mentioned the requirements as well. – Stephen Turner Jun 26 '15 at 09:12
  • webturner and @Damien_The_Unbeliever: just to be clear about the example code in this answer, "matching" the datatypes can be done with either `NVARCHAR(1 - 4000)` or `NVARCHAR(MAX)` on the T-SQL side, and either `SqlString` or `SqlChars` on the .NET side. Any combination of those would work. This answer implies that one must use `SqlString` and `NVARCHAR(MAX)` together. – Solomon Rutzky Sep 15 '16 at 05:46