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:
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
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
}
32-bit UDA fails on 64-bit with large dataset (worked before)
Stephen C. Steel
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
C Stephens
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
Laxmi.
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.
PBG157
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.
Arran
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.
LanceM
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
BadAndre
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)