User Defined Aggregate assembly / CLR / Database compatibility level
-
Hello,
I'm having a weird behavior on a clr aggregate function. (concatenation of several strings using a delimiter - code below)- The CLR assembly was developed using .NET framework version v4.5
- SQL Server is v2016 (SP1) Dev editionWhen I use database compatibility levels: 2008 (100), 2012 (110) the assembly works as expected.
When I use database compatibility levels: 2014 (120), 2016 (130) the assembly does not works as expected and the delimiter specified "sometimes" does not appears in the resulting concatenate string.Why does the compatibility level affects the CLR behavior? Is this a version compatibility issue between SQL versions and .NET versions ?
Thanks in advance,
Rui-------------------------
using System;
using System.Data;
using Microsoft.SqlServer.Server;
using System.Data.SqlTypes;
using System.IO;
using System.Text;
using System.Collections.Generic;[Serializable]
[SqlUserDefinedAggregate(
Format.UserDefined, //use clr serialization to serialize the intermediate result
IsInvariantToNulls = true, //optimizer property
IsInvariantToDuplicates = false, //optimizer property
IsInvariantToOrder = false, //optimizer property
MaxByteSize = -1 //maximum size in bytes of persisted value
)
]
public class StringConcatenate : IBinarySerialize
{
private List<string> _list;
private string _delimiter;
private bool _sort = true;/// <summary>
/// IsNull property
/// </summary>
public Boolean IsNull { get; private set; }/// <summary>
/// Initialize the internal data structures
/// </summary>
public void Init()
{
_list = new List<string>();
_delimiter = string.Empty;this.IsNull = true;
}/// <summary>
/// Accumulate the next value, not if the value is null
/// </summary>
/// <param name="value"></param>
public void Accumulate(SqlString value, SqlString delimiter, SqlBoolean sort)
{
_sort = sort.Value;if (!delimiter.IsNull
& delimiter.Value.Length > 0)
{
_delimiter = delimiter.Value; /// save for Merge
}_list.Add(value.Value);
if (value.IsNull == false) this.IsNull = false;
}/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(StringConcatenate other)
{
_list.AddRange(other._list);
}/// <summary>
/// Called at the end of aggregation, to return the results of the aggregation.
/// </summary>
/// <returns></returns>
public SqlString Terminate()
{
if (_sort)
{
_list.Sort();
}return new SqlString(String.Join(_delimiter, _list));
}public void Read(BinaryReader r)
{
_sort = r.ReadBoolean();
_delimiter = r.ReadString();
_list = new List<string>(r.ReadString().Split(new string[] { _delimiter }, StringSplitOptions.None));
}public void Write(BinaryWriter w)
{
w.Write(_sort);
w.Write(_delimiter);
w.Write(String.Join(_delimiter, _list));
}
} -
Can you give us some examples ... a couple which work and a couple which do not, perhaps? Do certain delimiters always work and others not?
Or (the scary scenario!) are you saying that the exact same inputs sometimes work and sometimes do not?If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Have you tried re-compiling your assembly using VS 2017 and re-deploying? It might just be a version thing, but any time you have CLR and also change versions of SQL Server, you would be wise to test first and with any failures first try to re-build / compile your assembly using a version of Visual Studio that "agrees" with the version of SQL Server you're working with, so to speak. So for SQL 2012, Visual Studio 2010 or later, for SQL 2014, Visual Studio 2015 or later, and for SQL Server 2016, Visual Studio 2017 or later.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Ok after some more testing I've some new conclusions:
- it seems the problem is not in the database compatibility level (although in my first test it had impacts changing it)
- the problem is with the size of the recordset beeing aggregated.I've created a small test data with 2 scenarios:
- in the 1st small recordset scenario everything works ok.
- in the 2nd scenario the delimiter disappears from the concatenated string. maybe some C# limits are beeing hit somewhere and this is the result ?
if object_id('dbo.testdata') is not null
drop table dbo.testdata
go
create table dbo.testdata
(
column1 integer
, column2 nvarchar(10)
)
go
insert into dbo.testdata(column1, column2) values
(1, 'a')
, (2, 'b')
, (3, 'c')
, (4, 'd')
, (5, 'e')
, (6, 'f')
, (7, 'g')
, (8, 'h')
, (9, 'i')
, (10, 'j');
go
insert into dbo.testdata(column1, column2) select * from dbo.testdata;
go 10-- scenario1: with 10240 records
select 'record count=' + cast(count(*) as varchar) from dbo.testdata
select column1, dbo.StringConcatenate(column2, ',', 1) from dbo.testdata group by column1-- scenario2: now with 81920 records
truncate table dbo.testdata
insert into dbo.testdata(column1, column2) values
(1, 'a')
, (2, 'b')
, (3, 'c')
, (4, 'd')
, (5, 'e')
, (6, 'f')
, (7, 'g')
, (8, 'h')
, (9, 'i')
, (10, 'j');
go
insert into dbo.testdata(column1, column2) select * from dbo.testdata;
go 13
select 'record count=' + cast(count(*) as varchar) from dbo.testdata
select column1, dbo.StringConcatenate(column2, ',', 1) from dbo.testdata group by column1 -
Have you tried stepping through the code in debug mode? Maybe that will throw some light on the issue.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
-
Ok, I'll try it and return when have more info.
Thanks for the tip,
Rui -
I suspect that part of your problem is that you're taking varchar data and subjecting it to translation to Unicode, as the C# string data type is Unicode. You may want to look at alternative methods or alternative languages, for that matter.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy) -
Ok after some debugging the cause of the problem was simple: a bug in my clr function, in the Merge method.
The delimiter field was not passed from the "other" instance to "this" one.
The merge method is only executed when partial calculations of the aggregation are needed.
When the input recordset became bigger the merge method was invoked and the problem appeared.Thanks,
Rui...
/// <summary>
/// Merge the partially computed aggregate with this aggregate.
/// </summary>
/// <param name="other"></param>
public void Merge(StringConcatenate other)
{
this._delimiter = other._delimiter; <<----- missing line
_list.AddRange(other._list);
}
...
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply