32-bit UDA fails on 64-bit with large dataset (worked before)

Hi. Could someone please help me?

I have a 32-bit UDA that concatenates strings with a delimiter.

My platform is WS 2003/64, 1G RAM, SCSI, SQL 9.0.1399.

When running the uda on a small dataset, say:

select dbo.UDA(name) from sys.objects

it works fine. But on larger datasets it says:

The statement has been terminated. Msg 0, Level 11, State 0, Line 0 A severe error occurred on the current command. The results, if any, should be discarded.

DROP AGGREGATE UDA
go
DROP ASSEMBLY StringSumAggregateAsm
go
CREATE ASSEMBLY StringSumAggregateAsm FROM 'D:\Aggregates.dll'
go
CREATE AGGREGATE UDA(@.input nvarchar(max)) RETURNS nvarchar(max) EXTERNAL NAME UDA;
go

// Should return empty string.
using System; using System.Data; using System.Data.Sql; using System.Data.SqlTypes; using Microsoft.SqlServer.Server;
[Serializable][SqlUserDefinedAggregate(Format.UserDefined, MaxByteSize=8000)]
public struct UDA : IBinarySerialize
{ public void Init() { } public void Accumulate(SqlString Value) { } public void Merge(UDA Group) { }
public SqlString Terminate() { return new SqlString(""); }
#region IBinarySerialize Members
public void Read(System.IO.BinaryReader r) { r.ReadString(); }
public void Write(System.IO.BinaryWriter w) { w.Write(""); }
#endregion
}

Hi Gorm,

The code you provided doesn't concatenate strings because Accumulate is empty. Does it repro with the example you've provided?

Thanks, Nikita

|||

Sorry about that.

Yep.
Besides the concat, of course. Sample might have misspelling too, I tried to strip it down a bit for clarity (failed, obviously)

(Same error on large datasets, any successful query gives an empty string)

It might matter that by "large dataset" I mean 500000+ returned rows, not many rows accumulated for each returned row.

|||

Ok I got a repro on our side. I will figure out what's wrong and let you know if there is a workaround.

-Nikita (Sql Clr Team)

|||

I figured out what is the problem. This is a known bug in Sql Server which is already fixed for the coming SP1. I'll let you know when we going to release SP1 and if there is a possibility of a QFE fixing this bug.

Thanks, Nikita

|||

Hello again.
Thank you for lightening fast reply.

Do you have any additional details on this "known bug in Sql Server"?
eg.
- Will more RAM help?
- Will 64-bit CLR help? Is that possible?
- Is it on 64-bit, only? Can I do some sort of distributed query with a 32-bit instance which will help?

BTW: The query is actually called as part of a SSIS-package, amI missing any obvious work-arounds in this respect?

-- On the side --
- Please (please please, etc.) include me in the beta of SP1, I used to be in ASCEND, but since I am working alone, I haven't tried to do steps towards BetaOne...
- Will I, in a future version of SQL Server, be able to intercept SOUNDEX or nvarchar-compare in CLR?
-- EoOn the side --

Thanks again.

Merry Christmas.

|||

Hi Gorm,

So here are the details.

1. More RAM will not help

2.64-bit CLR will not help

3. This bug is common for all platforms

The workaround here is not using NVARCHAR(MAX) a a parameter of a UDA. Use NVARCHAR(<some constant>). Since we don't support large UDAs in Sql Server 2005 it doesn't make sence anyway.

--On the side answers

Please contact customer support for beta related questions.

Future version is not set up yet.

-- EoOn the answers --

Thanks and Merry Christmas, Nikita

|||

Ok Nikita.

I changed my sql-scripts from

CREATE AGGREGATE UDA(@.input nvarchar(max)) RETURNS nvarchar(max) EXTERNAL NAME UDA;

to

CREATE AGGREGATE UDA(@.input nvarchar(4000)) RETURNS nvarchar(4000) EXTERNAL NAME UDA;

And suddenly my plans for cristmas is using extra time with my four-year old daughter in sted of creating a "select aggregate(something)"-replacement.

Thank you very much for this gift
Merry Cristmas

Gorm.