Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

Getting The Most Out of SQL Server 2005 UDTs and UDAs Expand / Collapse
Author
Message
Posted Wednesday, November 13, 2013 1:24 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 3:45 PM
Points: 15, Visits: 44
Hi guys, I am using the CLR and for some reason I get this error :
A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":
System.InvalidCastException: Specified cast is not valid.
System.InvalidCastException:
at Agg_Median.Write(BinaryWriter ToBeSerialized)

Any idea on how to fix this?
Thanks
Post #1514047
Posted Thursday, January 29, 2015 11:14 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 3:45 PM
Points: 15, Visits: 44
Hi everyone, not sure if anyone is still looking at this thread but recently I have been running into the following error. I inspected the data but I am not noticing anything unusual.
Any input would be greatly appreciated.
Thanks



Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":
System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.
System.ArgumentException:
at System.Collections.Comparer.Compare(Object a, Object b)
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
System.InvalidOperationException:
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)
at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)
at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)
at System.Collections.ArrayList.Sort()
at Agg_Median.Write(BinaryWriter ToBeSerialized)
Post #1655969
Posted Thursday, January 29, 2015 11:49 AM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 PM
Points: 409, Visits: 2,149
massoud-1045248 (1/29/2015)
Hi everyone, not sure if anyone is still looking at this thread but recently I have been running into the following error. I inspected the data but I am not noticing anything unusual.
Any input would be greatly appreciated.
Thanks



Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":
System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.
System.ArgumentException:
at System.Collections.Comparer.Compare(Object a, Object b)
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
System.InvalidOperationException:
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)
at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)
at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)
at System.Collections.ArrayList.Sort()
at Agg_Median.Write(BinaryWriter ToBeSerialized)


Hello Massoud. Are you using the code from the article exactly as it is shown or did you make any modifications? I have no idea how it could be possible to get the error you posted above, nor the error you mentioned in the prior post (sorry, I never got a notification for that posting). Do you always or only sometimes get this error? The only problem I see, looking over the code again, is that it doesn't handle groups that have only NULL values, but that causes a different error: "Index was out of range". What version of SQL Server are you using? Can you post some sample data? How did you get past the error from last time regarding "Specified cast is not valid"?

Take care,
Solomon..





SQL# - http://www.SQLsharp.com/
Post #1655988
Posted Thursday, January 29, 2015 1:50 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 3:45 PM
Points: 15, Visits: 44
Hello Solomon, thanks for your reply. It's been a while but I use this code to compute a median for a list that varies every day.
so basically date(T), median(list(T)).
meaning : 01/02/2015 Median (list), 01/03/2015 Median (list) ....
I think my work around was to exclude some dates and move on when it wasn't working. Now when I go and compute without the time series and look at a specific date , it works .
For example when I have the time series with the specific condition
"valuationdate > getdate() - 235" in the where statement which gives me data up to 06/09/2014 but I get the error message :
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "Agg_Median":
System.InvalidOperationException: Failed to compare two elements in the array. ---> System.ArgumentException: At least one object must implement IComparable.
System.ArgumentException:
at System.Collections.Comparer.Compare(Object a, Object b)
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
System.InvalidOperationException:
at System.Array.SorterObjectArray.SwapIfGreaterWithItems(Int32 a, Int32 b)
at System.Array.SorterObjectArray.QuickSort(Int32 left, Int32 right)
at System.Array.Sort(Array keys, Array items, Int32 index, Int32 length, IComparer comparer)
at System.Collections.ArrayList.Sort(Int32 index, Int32 count, IComparer comparer)
at System.Collections.ArrayList.Sort()
at Agg_Median.Write(BinaryWriter ToBeSerialized)


but when I exclude via inserting the following in the where statement
and valuationdate not in ('06/06/2014','06/07/2014','06/08/2014') it still doesn't work if I increase to
"valuationdate > getdate() - 236"
And when I look at 06/08/2014 specifically, it returns no error message.

Happy to share more data if needed.
Thanks again.
Post #1656038
Posted Thursday, January 29, 2015 2:22 PM


SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Yesterday @ 9:34 PM
Points: 409, Visits: 2,149
massoud-1045248 (1/29/2015)
Hello Solomon, thanks for your reply. It's been a while but I use this code to compute a median for a list that varies every day.

so basically date(T), median(list(T)).
meaning : 01/02/2015 Median (list), 01/03/2015 Median (list) ....
I think my work around was to exclude some dates and move on when it wasn't working. Now when I go and compute without the time series and look at a specific date , it works .


Hi again. So, if I am understanding you correctly, you are doing a MEDIAN of a list of DATETIME (or just DATE) instead of FLOAT? I had never considered such a thing. Interesting. But I can see one immediate problem: what to do in the case of an even list, since DATE / DATETIME values cannot be averaged. I suppose you could just pick the higher or lower value in each case, or in SQL Server 2008 and newer, pass in a second parameter to determine which side to err on.

If you want to continue debugging this, since it really isn't the code from the article, please post a message to the CLR Integration and Programming forum and we can take it from there.

Take care,
Solomon..





SQL# - http://www.SQLsharp.com/
Post #1656047
Posted Thursday, January 29, 2015 2:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, February 17, 2015 3:45 PM
Points: 15, Visits: 44
Thanks Solomon.
It is a median of float values. Sorry for the confusion. The return of the query is date, median(list) is what I meant.
I will follow up on the forum mentioned.
Thanks again!
Post #1656052
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse