Msg 6558: CREATE AGGREGATE failed because type 'Concatenate' does not conform to UDAGG specification

Question

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?

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

Answer 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".