June 18, 2012 at 8:28 am
I am very new to using CLRs but I am starting with a small problem and I am having a lot of problems with it. I am trying to create an aggregate to calculate the median. I have the C# done and I deployed it via Visual Studio (VS 2005). When I run the code with one select it works fine all the time. Sometimes though when I run multiple selects at once (for a union query), I get a null reference exception or the sql server services will restart. In one instance the service never restarted. Here is my median code:
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Collections.Generic;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,
IsInvariantToNulls = true,
IsInvariantToDuplicates = false,
IsInvariantToOrder = false,
MaxByteSize = 8000)]
public struct Median : IBinarySerialize
{
public void Init()
{
dataset = new List<double>();
}
public void Accumulate(SqlDouble Value)
{
if (!Value.IsNull)
dataset.Add(Value.Value);
}
public void Merge(Median Group)
{
this.dataset.AddRange(Group.dataset.ToArray());
}
public SqlDouble Terminate()
{
return findMedian();
}
private SqlDouble findMedian()
{
if (dataset.Count == 0)
return SqlDouble.Null;
dataset.Sort();
//get the median
int size = dataset.Count;
int mid = size / 2;
SqlDouble median = (size % 2 != 0) ? dataset[mid] : (dataset[mid] + dataset[mid - 1]) / 2;
return median;
}
public void Read(System.IO.BinaryReader r)
{
int cnt = r.ReadInt32();
this.dataset = new List<double>();
for (int i = 0; i < cnt; i++)
{
this.dataset.Add(r.ReadDouble());
}
}
public void Write(System.IO.BinaryWriter w)
{
if (this.dataset.Count > 0)
{
w.Write(this.dataset.Count);
foreach (double d in this.dataset)
{
w.Write(d);
}
}
else
w.Write(0);
}
// List to hold all of the values for the Median.
private List<double> dataset;
}
The only thing that makes sense would be the serialization not working correctly but I have tried three separate implementations and one of them was written for the median by an MVP.
Any help would be greatly appreciated.
Nick
June 18, 2012 at 9:40 am
After more experimenting it appears the I am having issues only when I try to UNION ALL the selects together. If I run them as separate Select statements then it appears to work fine. But this leaves me very confused as to why this is happening.
Nick
June 19, 2012 at 5:58 am
I did not run it, but your code looks OK to me. One item that came to mind is that serialized SQLCLR objects in SQL Server 2005 have a max size limit of 8000 bytes. The limit was lifted in SQL Server 2008.
Is there a specific error message you receive when it fails?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 6:09 am
I either get a transport error (when the sql server service restarts) or I get this error message:
"Msg 6522, Level 16, State 2, Line 6
A .NET Framework error occurred during execution of user-defined routine or aggregate "Median":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
.
Warning: Null value is eliminated by an aggregate or other SET operation."
What's weird is it works on all of my other queries except for one that takes about 10 seconds to complete (The others are < 1 sec).
Nick
June 19, 2012 at 6:14 am
nick947 (6/19/2012)
I either get a transport error (when the sql server service restarts) or I get this error message:"Msg 6522, Level 16, State 2, Line 6
A .NET Framework error occurred during execution of user-defined routine or aggregate "Median":
System.NullReferenceException: Object reference not set to an instance of an object.
System.NullReferenceException:
.
Warning: Null value is eliminated by an aggregate or other SET operation."
What's weird is it works on all of my other queries except for one that takes about 10 seconds to complete (The others are < 1 sec).
Nick
The fact that your SQL Server restarts is troubling, and makes me think you need to rebuild SQL Server because that should not happen from running a query. What safety level is your assembly marked, SAFE? Anything else in the call stack of the error message?
When the SQL Server restarts, what is in the SQL Server Error Log right before and after it bounces?
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 6:23 am
If the server needs to be rebuild then I am just going to wait on this (Our new server is ordered and hopefully be ready in a month or two). Right before the service restarts the log says "* A fatal error occurred in .NET Framework runtime." Then it prints out my query and a lot of stuff I really don't understand. When the service comes back up it just shows all of the databases coming back online and normal start up messages.
Edit - Yes, the permissions are set to safe.
Nick
June 19, 2012 at 6:38 am
Yeah, that sounds like a bad build, either of the .NET framework or of SQL Server, or both. Please post back when you get the new server and let us know how you get along.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 6:44 am
Thanks for your help. Hopefully our new server will get here soon and I can get some resolution to this issue.
Nick
June 19, 2012 at 6:47 am
Please post all messages from the SQL Server Error Log if you don't mind and have a moment. I would be interested to see what was written.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 6:59 am
Date,Source,Severity,Message
06/18/2012 13:03:00,spid57,Unknown,A fatal error occurred in the .NET Framework common language runtime. SQL Server is shutting down. If the error recurs after the server is restarted<c/> contact Customer Support Services.
06/18/2012 13:03:00,spid57,Unknown,Error: 6536<c/> Severity: 16<c/> State: 1.
06/18/2012 13:03:00,spid57,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
06/18/2012 13:02:55,spid57,Unknown,Stack Signature for the dump is 0x769DAE3F
06/18/2012 13:02:55,spid57,Unknown,7C8155A1 Module(ntdll+000155A1)
06/18/2012 13:02:55,spid57,Unknown,0CF6E2BE Module(mscorwks+001EE2BE)
06/18/2012 13:02:55,spid57,Unknown,0CF6A1BF Module(mscorwks+001EA1BF)
06/18/2012 13:02:55,spid57,Unknown,0CF694AF Module(mscorwks+001E94AF)
06/18/2012 13:02:55,spid57,Unknown,018CF4DB Module(sqlservr+008CF4DB)
06/18/2012 13:02:55,spid57,Unknown,018CF7C5 Module(sqlservr+008CF7C5)
06/18/2012 13:02:55,spid57,Unknown,021ABAA0 Module(sqlservr+011ABAA0)
06/18/2012 13:02:55,spid57,Unknown,021A7BD4 Module(sqlservr+011A7BD4)
06/18/2012 13:02:55,spid57,Unknown,77E55DEA Module(kernel32+00015DEA)
06/18/2012 13:02:55,spid57,Unknown,* Short Stack Dump
06/18/2012 13:02:55,spid57,Unknown,* -------------------------------------------------------------------------------
06/18/2012 13:02:55,spid57,Unknown,* *******************************************************************************
06/18/2012 13:02:55,spid57,Unknown,* SegSs: 78130023: 00000000 00000000 00000000 00000000 00000000 00000000
06/18/2012 13:02:55,spid57,Unknown,* Esp: 0ADAE1F4: 2AA9F330 000042AC 00000000 00000000 77E55DEA 00000000
06/18/2012 13:02:55,spid57,Unknown,* EFlags: 00000246:
06/18/2012 13:02:55,spid57,Unknown,* SegCs: 7813001B: 00000000 00000000 00000000 00000000 00000000 00000000
06/18/2012 13:02:55,spid57,Unknown,* Ebp: 0ADAE248: 0ADAE28C 021A7BD4 000042AC 00000000 00000000 00000000
06/18/2012 13:02:55,spid57,Unknown,* Eip: 77E55DEA: 10C2C95E 90909000 A1649090 00000018 C334408B 891C428B
06/18/2012 13:02:55,spid57,Unknown,* Edx: 000004BD:
06/18/2012 13:02:55,spid57,Unknown,* Ecx: 0ADAE868: 00000000 00010007 004A0048 0016EA6C 0ADAE224 0ADAE1F8
06/18/2012 13:02:55,spid57,Unknown,* Ebx: 000004BF:
06/18/2012 13:02:55,spid57,Unknown,* Eax: 0ADAE1F8: 000042AC 00000000 00000000 77E55DEA 00000000 7C821B34
06/18/2012 13:02:55,spid57,Unknown,* Esi: 00000000:
06/18/2012 13:02:55,spid57,Unknown,* Edi: 0ADAE314: 0016EA68 005465AC 00001770 0ADAF4A0 180040E8 2F7B924C
06/18/2012 13:02:55,spid57,Unknown,*
06/18/2012 13:02:55,spid57,Unknown,* dbghelp 16900000 16A14FFF 00115000
06/18/2012 13:02:55,spid57,Unknown,* System.Xml 165C0000 167B3FFF 001f4000
06/18/2012 13:02:55,spid57,Unknown,* System.Security 0FF30000 0FF71FFF 00042000
06/18/2012 13:02:55,spid57,Unknown,* System 162D0000 165B3FFF 002e4000
06/18/2012 13:02:55,spid57,Unknown,* System.Security.ni 0F290000 0F345FFF 000b6000
06/18/2012 13:02:55,spid57,Unknown,* System.Transactions 0E890000 0E8D2FFF 00043000
06/18/2012 13:02:55,spid57,Unknown,* System.ni 0F670000 0FE2DFFF 007be000
06/18/2012 13:02:55,spid57,Unknown,* System.Data 0EDC0000 0F086FFF 002c7000
06/18/2012 13:02:55,spid57,Unknown,* mscorjit 0E6E0000 0E732FFF 00053000
06/18/2012 13:02:55,spid57,Unknown,* SqlAccess 0E680000 0E6D5FFF 00056000
06/18/2012 13:02:55,spid57,Unknown,* SensApi 0E460000 0E464FFF 00005000
06/18/2012 13:02:55,spid57,Unknown,* cryptnet 0E440000 0E451FFF 00012000
06/18/2012 13:02:55,spid57,Unknown,* SOFTPUB 0C570000 0C574FFF 00005000
06/18/2012 13:02:55,spid57,Unknown,* mscorsec 0C4C0000 0C4D2FFF 00013000
06/18/2012 13:02:55,spid57,Unknown,* mscorlib.ni 0D8D0000 0E3B7FFF 00ae8000
06/18/2012 13:02:55,spid57,Unknown,* mscorwks 0CD80000 0D2E0FFF 00561000
06/18/2012 13:02:55,spid57,Unknown,* xplog70 0CBB0000 0CBB2FFF 00003000
06/18/2012 13:02:55,spid57,Unknown,* xplog70 0CB90000 0CB9BFFF 0000c000
06/18/2012 13:02:55,spid57,Unknown,* xpstar90 0CA50000 0CA75FFF 00026000
06/18/2012 13:02:55,spid57,Unknown,* odbcint 0CA30000 0CA46FFF 00017000
06/18/2012 13:02:55,spid57,Unknown,* ATL80 7C630000 7C64AFFF 0001b000
06/18/2012 13:02:55,spid57,Unknown,* BatchParser90 0C740000 0C75EFFF 0001f000
06/18/2012 13:02:55,spid57,Unknown,* ODBC32 0C700000 0C73CFFF 0003d000
06/18/2012 13:02:55,spid57,Unknown,* SQLSCM90 0C6E0000 0C6E8FFF 00009000
06/18/2012 13:02:55,spid57,Unknown,* xpstar90 0C680000 0C6C9FFF 0004a000
06/18/2012 13:02:55,spid57,Unknown,* msftepxy 0AA80000 0AA94FFF 00015000
06/18/2012 13:02:55,spid57,Unknown,* SQLNCLIR 0A940000 0A972FFF 00033000
06/18/2012 13:02:55,spid57,Unknown,* comdlg32 0A870000 0A8B9FFF 0004a000
06/18/2012 13:02:55,spid57,Unknown,* COMCTL32 77530000 775C6FFF 00097000
06/18/2012 13:02:55,spid57,Unknown,* sqlncli 0A640000 0A863FFF 00224000
06/18/2012 13:02:55,spid57,Unknown,* CLBCatQ 777B0000 77832FFF 00083000
06/18/2012 13:02:55,spid57,Unknown,* xpsp2res 10000000 102C4FFF 002c5000
06/18/2012 13:02:55,spid57,Unknown,* ntdsapi 766F0000 76704FFF 00015000
06/18/2012 13:02:55,spid57,Unknown,* wshtcpip 71AE0000 71AE7FFF 00008000
06/18/2012 13:02:55,spid57,Unknown,* hnetcfg 0A520000 0A578FFF 00059000
06/18/2012 13:02:55,spid57,Unknown,* dssenh 0A4F0000 0A513FFF 00024000
06/18/2012 13:02:55,spid57,Unknown,* imagehlp 76C10000 76C38FFF 00029000
06/18/2012 13:02:55,spid57,Unknown,* WINTRUST 76BB0000 76BDAFFF 0002b000
06/18/2012 13:02:55,spid57,Unknown,* dbghelp 0A100000 0A214FFF 00115000
06/18/2012 13:02:55,spid57,Unknown,* msfte 09EA0000 0A0F8FFF 00259000
06/18/2012 13:02:55,spid57,Unknown,* security 09510000 09513FFF 00004000
06/18/2012 13:02:55,spid57,Unknown,* rasadhlp 09140000 09144FFF 00005000
06/18/2012 13:02:55,spid57,Unknown,* winrnr 09120000 09126FFF 00007000
06/18/2012 13:02:55,spid57,Unknown,* DNSAPI 76ED0000 76EF8FFF 00029000
06/18/2012 13:02:55,spid57,Unknown,* RESUTILS 090B0000 090C2FFF 00013000
06/18/2012 13:02:55,spid57,Unknown,* CLUSAPI 09090000 090A1FFF 00012000
06/18/2012 13:02:55,spid57,Unknown,* OLEAUT32 77D00000 77D8BFFF 0008c000
06/18/2012 13:02:55,spid57,Unknown,* WSOCK32 71BB0000 71BB8FFF 00009000
06/18/2012 13:02:55,spid57,Unknown,* VERSION 77B90000 77B97FFF 00008000
06/18/2012 13:02:55,spid57,Unknown,* MTXCLU 09070000 09088FFF 00019000
06/18/2012 13:02:55,spid57,Unknown,* msvcp60 780C0000 78120FFF 00061000
06/18/2012 13:02:55,spid57,Unknown,* MSDTCPRX 08FF0000 09067FFF 00078000
06/18/2012 13:02:55,spid57,Unknown,* XOLEHLP 08FE0000 08FE5FFF 00006000
06/18/2012 13:02:55,spid57,Unknown,* COMRES 08F10000 08FD5FFF 000c6000
06/18/2012 13:02:55,spid57,Unknown,* schannel 76750000 76776FFF 00027000
06/18/2012 13:02:55,spid57,Unknown,* cryptdll 766E0000 766EBFFF 0000c000
06/18/2012 13:02:55,spid57,Unknown,* kerberos 08E50000 08EA7FFF 00058000
06/18/2012 13:02:55,spid57,Unknown,* iphlpapi 76CF0000 76D09FFF 0001a000
06/18/2012 13:02:55,spid57,Unknown,* msv1_0 76C90000 76CB6FFF 00027000
06/18/2012 13:02:55,spid57,Unknown,* MSCOREE 08BE0000 08C24FFF 00045000
06/18/2012 13:02:55,spid57,Unknown,* AUTHZ 76C40000 76C53FFF 00014000
06/18/2012 13:02:55,spid57,Unknown,* rsaenh 08120000 0814EFFF 0002f000
06/18/2012 13:02:55,spid57,Unknown,* WLDAP32 76F10000 76F3DFFF 0002e000
06/18/2012 13:02:55,spid57,Unknown,* SAMLIB 5CCF0000 5CCFEFFF 0000f000
06/18/2012 13:02:55,spid57,Unknown,* ole32 77670000 777A3FFF 00134000
06/18/2012 13:02:55,spid57,Unknown,* NTMARTA 77E00000 77E21FFF 00022000
06/18/2012 13:02:55,spid57,Unknown,* SQLOS 344D0000 344D4FFF 00005000
06/18/2012 13:02:55,spid57,Unknown,* sqlevn70 4F610000 4F7B8FFF 001a9000
06/18/2012 13:02:55,spid57,Unknown,* instapi 48060000 48069FFF 0000a000
06/18/2012 13:02:55,spid57,Unknown,* psapi 76B70000 76B7AFFF 0000b000
06/18/2012 13:02:55,spid57,Unknown,* comctl32 77420000 77522FFF 00103000
06/18/2012 13:02:55,spid57,Unknown,* SHLWAPI 77DA0000 77DF1FFF 00052000
06/18/2012 13:02:55,spid57,Unknown,* SHELL32 7C8D0000 7D0D2FFF 00803000
06/18/2012 13:02:55,spid57,Unknown,* NETAPI32 71C40000 71C97FFF 00058000
06/18/2012 13:02:55,spid57,Unknown,* opends60 333E0000 333E6FFF 00007000
06/18/2012 13:02:55,spid57,Unknown,* USERENV 76920000 769E3FFF 000c4000
06/18/2012 13:02:55,spid57,Unknown,* WS2HELP 71BF0000 71BF7FFF 00008000
06/18/2012 13:02:55,spid57,Unknown,* WS2_32 71C00000 71C16FFF 00017000
06/18/2012 13:02:55,spid57,Unknown,* MSWSOCK 71B20000 71B60FFF 00041000
06/18/2012 13:02:55,spid57,Unknown,* Secur32 76F50000 76F62FFF 00013000
06/18/2012 13:02:55,spid57,Unknown,* MSASN1 76190000 761A1FFF 00012000
06/18/2012 13:02:55,spid57,Unknown,* CRYPT32 761B0000 76242FFF 00093000
06/18/2012 13:02:55,spid57,Unknown,* GDI32 77C00000 77C47FFF 00048000
06/18/2012 13:02:55,spid57,Unknown,* USER32 77380000 77411FFF 00092000
06/18/2012 13:02:55,spid57,Unknown,* RPCRT4 77C50000 77CEEFFF 0009f000
06/18/2012 13:02:55,spid57,Unknown,* ADVAPI32 77F50000 77FEBFFF 0009c000
06/18/2012 13:02:55,spid57,Unknown,* MSVCP80 7C420000 7C4A6FFF 00087000
06/18/2012 13:02:55,spid57,Unknown,* msvcrt 77BA0000 77BF9FFF 0005a000
06/18/2012 13:02:55,spid57,Unknown,* MSVCR80 78130000 781CAFFF 0009b000
06/18/2012 13:02:55,spid57,Unknown,* kernel32 77E40000 77F41FFF 00102000
06/18/2012 13:02:55,spid57,Unknown,* ntdll 7C800000 7C8BFFFF 000c0000
06/18/2012 13:02:55,spid57,Unknown,* sqlservr 01000000 02C13FFF 01c14000
06/18/2012 13:02:55,spid57,Unknown,* MODULE BASE END SIZE
06/18/2012 13:02:55,spid57,Unknown,*
06/18/2012 13:02:55,spid57,Unknown,*
06/18/2012 13:02:55,spid57,Unknown,* EN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' END
06/18/2012 13:02:55,spid57,Unknown,* FROM dbo.UDF_RxMidSummary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WH
06/18/2012 13:02:55,spid57,Unknown,* ' ELSE 'SCP' END AS Specialty<c/> MIN(NULLIF(RxPoints<c/> 0)) AS RxPoints
06/18/2012 13:02:55,spid57,Unknown,* 'Min without 0s' AS MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP
06/18/2012 13:02:55,spid57,Unknown,* N URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' END UNION ALL SELECT
06/18/2012 13:02:55,spid57,Unknown,* FROM dbo.UDF_RxMidSummary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHE
06/18/2012 13:02:55,spid57,Unknown,* D AS Specialty<c/> Functions.dbo.Median(NULLIF(RxPoints<c/> 0)) AS RxPoints
06/18/2012 13:02:55,spid57,Unknown,* S MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' EN
06/18/2012 13:02:55,spid57,Unknown,* HEN 'PCP' ELSE 'SCP' END UNION ALL SELECT 'Median without 0s' A
06/18/2012 13:02:55,spid57,Unknown,* mmary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHEN URMS.PCP = 'True' T
06/18/2012 13:02:55,spid57,Unknown,* Specialty<c/> AVG(NULLIF(RxPoints<c/> 0)) AS RxPoints FROM dbo.UDF_RxMidSu
06/18/2012 13:02:55,spid57,Unknown,* asureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' END AS
06/18/2012 13:02:55,spid57,Unknown,* N 'PCP' ELSE 'SCP' END UNION ALL SELECT 'Mean without 0s' AS Me
06/18/2012 13:02:55,spid57,Unknown,* ary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHEN URMS.PCP = 'True' THE
06/18/2012 13:02:55,spid57,Unknown,* P' END AS Specialty<c/> AVG(RxPoints) AS RxPoints FROM dbo.UDF_RxMidSumm
06/18/2012 13:02:55,spid57,Unknown,* 0s' AS MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SC
06/18/2012 13:02:55,spid57,Unknown,* = 'True' THEN 'PCP' ELSE 'SCP' END UNION ALL SELECT 'Mean with
06/18/2012 13:02:55,spid57,Unknown,* DF_RxMidSummary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHEN URMS.PCP
06/18/2012 13:02:55,spid57,Unknown,* D AS Specialty<c/> Functions.dbo.Median(RxPoints) AS RxPoints FROM dbo.U
06/18/2012 13:02:55,spid57,Unknown,* S MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' EN
06/18/2012 13:02:55,spid57,Unknown,* ate = '1/1/11' SET @EndDate = '12/31/11' SELECT 'Median with 0s' A
06/18/2012 13:02:55,spid57,Unknown,* DECLARE @StartDate DATETIME<c/> @EndDate DATETIME SET @StartD
06/18/2012 13:02:55,spid57,Unknown,* Input Buffer 510 bytes -
06/18/2012 13:02:55,spid57,Unknown,*
06/18/2012 13:02:55,spid57,Unknown,* Full CLR state dump: A fatal error occurred in .NET Framework runtime.
06/18/2012 13:02:55,spid57,Unknown,*
06/18/2012 13:02:55,spid57,Unknown,* 06/18/12 13:02:55 spid 57
06/18/2012 13:02:55,spid57,Unknown,* BEGIN STACK DUMP:
06/18/2012 13:02:55,spid57,Unknown,*
06/18/2012 13:02:55,spid57,Unknown,* *******************************************************************************
06/18/2012 13:02:55,spid57,Unknown,***Stack Dump being sent to D:\MSSQL.1\MSSQL\LOG\SQLDump0052.txt
06/18/2012 13:02:55,spid57,Unknown,**Dump thread - spid = 57<c/> PSS = 0x2AA9F330<c/> EC = 0x2FDB00E8
06/18/2012 13:02:55,spid57,Unknown,Using 'dbghelp.dll' version '4.0.5'
06/18/2012 13:02:55,spid57,Unknown,External dump process return code 0x20000001.<nl/>External dump process returned no errors.
06/18/2012 13:02:54,spid57,Unknown,Stack Signature for the dump is 0x769DA69F
06/18/2012 13:02:54,spid57,Unknown,7C8155A1 Module(ntdll+000155A1)
06/18/2012 13:02:54,spid57,Unknown,0CF6E2BE Module(mscorwks+001EE2BE)
06/18/2012 13:02:54,spid57,Unknown,0CF6A1BF Module(mscorwks+001EA1BF)
06/18/2012 13:02:54,spid57,Unknown,0CF694AF Module(mscorwks+001E94AF)
06/18/2012 13:02:54,spid57,Unknown,018CF4DB Module(sqlservr+008CF4DB)
06/18/2012 13:02:54,spid57,Unknown,018CF780 Module(sqlservr+008CF780)
06/18/2012 13:02:54,spid57,Unknown,021ABAA0 Module(sqlservr+011ABAA0)
06/18/2012 13:02:54,spid57,Unknown,021A7BD4 Module(sqlservr+011A7BD4)
06/18/2012 13:02:54,spid57,Unknown,77E55DEA Module(kernel32+00015DEA)
06/18/2012 13:02:54,spid57,Unknown,* Short Stack Dump
06/18/2012 13:02:54,spid57,Unknown,* -------------------------------------------------------------------------------
06/18/2012 13:02:54,spid57,Unknown,* *******************************************************************************
06/18/2012 13:02:54,spid57,Unknown,* SegSs: 78130023: 00000000 00000000 00000000 00000000 00000000 00000000
06/18/2012 13:02:54,spid57,Unknown,* Esp: 0ADAE1F4: 2AA9F330 000042AC 00000000 00000000 77E55DEA 00000000
06/18/2012 13:02:54,spid57,Unknown,* EFlags: 00000246:
06/18/2012 13:02:54,spid57,Unknown,* SegCs: 7813001B: 00000000 00000000 00000000 00000000 00000000 00000000
06/18/2012 13:02:54,spid57,Unknown,* Ebp: 0ADAE248: 0ADAE28C 021A7BD4 000042AC 00000000 00000000 00000000
06/18/2012 13:02:54,spid57,Unknown,* Eip: 77E55DEA: 10C2C95E 90909000 A1649090 00000018 C334408B 891C428B
06/18/2012 13:02:54,spid57,Unknown,* Edx: 000004BD:
06/18/2012 13:02:54,spid57,Unknown,* Ecx: 0ADAE868: 00000000 00010007 004A0048 0016EA6C 0ADAE224 0ADAE1F8
06/18/2012 13:02:54,spid57,Unknown,* Ebx: 000004BF:
06/18/2012 13:02:54,spid57,Unknown,* Eax: 0ADAE1F8: 000042AC 00000000 00000000 77E55DEA 00000000 7C821B34
06/18/2012 13:02:54,spid57,Unknown,* Esi: 00000000:
06/18/2012 13:02:54,spid57,Unknown,* Edi: 0ADAE314: 0016EA68 005465AC 00001770 0ADAF4A0 180040E8 2F7B924C
06/18/2012 13:02:54,spid57,Unknown,*
06/18/2012 13:02:54,spid57,Unknown,* dbghelp 16900000 16A14FFF 00115000
06/18/2012 13:02:54,spid57,Unknown,* System.Xml 165C0000 167B3FFF 001f4000
06/18/2012 13:02:54,spid57,Unknown,* System.Security 0FF30000 0FF71FFF 00042000
06/18/2012 13:02:54,spid57,Unknown,* System 162D0000 165B3FFF 002e4000
06/18/2012 13:02:54,spid57,Unknown,* System.Security.ni 0F290000 0F345FFF 000b6000
06/18/2012 13:02:54,spid57,Unknown,* System.Transactions 0E890000 0E8D2FFF 00043000
06/18/2012 13:02:54,spid57,Unknown,* System.ni 0F670000 0FE2DFFF 007be000
06/18/2012 13:02:54,spid57,Unknown,* System.Data 0EDC0000 0F086FFF 002c7000
06/18/2012 13:02:54,spid57,Unknown,* mscorjit 0E6E0000 0E732FFF 00053000
06/18/2012 13:02:54,spid57,Unknown,* SqlAccess 0E680000 0E6D5FFF 00056000
06/18/2012 13:02:54,spid57,Unknown,* SensApi 0E460000 0E464FFF 00005000
06/18/2012 13:02:54,spid57,Unknown,* cryptnet 0E440000 0E451FFF 00012000
06/18/2012 13:02:54,spid57,Unknown,* SOFTPUB 0C570000 0C574FFF 00005000
06/18/2012 13:02:54,spid57,Unknown,* mscorsec 0C4C0000 0C4D2FFF 00013000
06/18/2012 13:02:54,spid57,Unknown,* mscorlib.ni 0D8D0000 0E3B7FFF 00ae8000
06/18/2012 13:02:54,spid57,Unknown,* mscorwks 0CD80000 0D2E0FFF 00561000
06/18/2012 13:02:54,spid57,Unknown,* xplog70 0CBB0000 0CBB2FFF 00003000
06/18/2012 13:02:54,spid57,Unknown,* xplog70 0CB90000 0CB9BFFF 0000c000
06/18/2012 13:02:54,spid57,Unknown,* xpstar90 0CA50000 0CA75FFF 00026000
06/18/2012 13:02:54,spid57,Unknown,* odbcint 0CA30000 0CA46FFF 00017000
06/18/2012 13:02:54,spid57,Unknown,* ATL80 7C630000 7C64AFFF 0001b000
06/18/2012 13:02:54,spid57,Unknown,* BatchParser90 0C740000 0C75EFFF 0001f000
06/18/2012 13:02:54,spid57,Unknown,* ODBC32 0C700000 0C73CFFF 0003d000
06/18/2012 13:02:54,spid57,Unknown,* SQLSCM90 0C6E0000 0C6E8FFF 00009000
06/18/2012 13:02:54,spid57,Unknown,* xpstar90 0C680000 0C6C9FFF 0004a000
06/18/2012 13:02:54,spid57,Unknown,* msftepxy 0AA80000 0AA94FFF 00015000
06/18/2012 13:02:54,spid57,Unknown,* SQLNCLIR 0A940000 0A972FFF 00033000
06/18/2012 13:02:54,spid57,Unknown,* comdlg32 0A870000 0A8B9FFF 0004a000
06/18/2012 13:02:54,spid57,Unknown,* COMCTL32 77530000 775C6FFF 00097000
06/18/2012 13:02:54,spid57,Unknown,* sqlncli 0A640000 0A863FFF 00224000
06/18/2012 13:02:54,spid57,Unknown,* CLBCatQ 777B0000 77832FFF 00083000
06/18/2012 13:02:54,spid57,Unknown,* xpsp2res 10000000 102C4FFF 002c5000
06/18/2012 13:02:54,spid57,Unknown,* ntdsapi 766F0000 76704FFF 00015000
06/18/2012 13:02:54,spid57,Unknown,* wshtcpip 71AE0000 71AE7FFF 00008000
06/18/2012 13:02:54,spid57,Unknown,* hnetcfg 0A520000 0A578FFF 00059000
06/18/2012 13:02:54,spid57,Unknown,* dssenh 0A4F0000 0A513FFF 00024000
06/18/2012 13:02:54,spid57,Unknown,* imagehlp 76C10000 76C38FFF 00029000
06/18/2012 13:02:54,spid57,Unknown,* WINTRUST 76BB0000 76BDAFFF 0002b000
06/18/2012 13:02:54,spid57,Unknown,* dbghelp 0A100000 0A214FFF 00115000
06/18/2012 13:02:54,spid57,Unknown,* msfte 09EA0000 0A0F8FFF 00259000
06/18/2012 13:02:54,spid57,Unknown,* security 09510000 09513FFF 00004000
06/18/2012 13:02:54,spid57,Unknown,* rasadhlp 09140000 09144FFF 00005000
06/18/2012 13:02:54,spid57,Unknown,* winrnr 09120000 09126FFF 00007000
06/18/2012 13:02:54,spid57,Unknown,* DNSAPI 76ED0000 76EF8FFF 00029000
06/18/2012 13:02:54,spid57,Unknown,* RESUTILS 090B0000 090C2FFF 00013000
06/18/2012 13:02:54,spid57,Unknown,* CLUSAPI 09090000 090A1FFF 00012000
06/18/2012 13:02:54,spid57,Unknown,* OLEAUT32 77D00000 77D8BFFF 0008c000
06/18/2012 13:02:54,spid57,Unknown,* WSOCK32 71BB0000 71BB8FFF 00009000
06/18/2012 13:02:54,spid57,Unknown,* VERSION 77B90000 77B97FFF 00008000
06/18/2012 13:02:54,spid57,Unknown,* MTXCLU 09070000 09088FFF 00019000
06/18/2012 13:02:54,spid57,Unknown,* msvcp60 780C0000 78120FFF 00061000
06/18/2012 13:02:54,spid57,Unknown,* MSDTCPRX 08FF0000 09067FFF 00078000
06/18/2012 13:02:54,spid57,Unknown,* XOLEHLP 08FE0000 08FE5FFF 00006000
06/18/2012 13:02:54,spid57,Unknown,* COMRES 08F10000 08FD5FFF 000c6000
06/18/2012 13:02:54,spid57,Unknown,* schannel 76750000 76776FFF 00027000
06/18/2012 13:02:54,spid57,Unknown,* cryptdll 766E0000 766EBFFF 0000c000
06/18/2012 13:02:54,spid57,Unknown,* kerberos 08E50000 08EA7FFF 00058000
06/18/2012 13:02:54,spid57,Unknown,* iphlpapi 76CF0000 76D09FFF 0001a000
06/18/2012 13:02:54,spid57,Unknown,* msv1_0 76C90000 76CB6FFF 00027000
06/18/2012 13:02:54,spid57,Unknown,* MSCOREE 08BE0000 08C24FFF 00045000
06/18/2012 13:02:54,spid57,Unknown,* AUTHZ 76C40000 76C53FFF 00014000
06/18/2012 13:02:54,spid57,Unknown,* rsaenh 08120000 0814EFFF 0002f000
06/18/2012 13:02:54,spid57,Unknown,* WLDAP32 76F10000 76F3DFFF 0002e000
06/18/2012 13:02:54,spid57,Unknown,* SAMLIB 5CCF0000 5CCFEFFF 0000f000
06/18/2012 13:02:54,spid57,Unknown,* ole32 77670000 777A3FFF 00134000
06/18/2012 13:02:54,spid57,Unknown,* NTMARTA 77E00000 77E21FFF 00022000
06/18/2012 13:02:54,spid57,Unknown,* SQLOS 344D0000 344D4FFF 00005000
06/18/2012 13:02:54,spid57,Unknown,* sqlevn70 4F610000 4F7B8FFF 001a9000
06/18/2012 13:02:54,spid57,Unknown,* instapi 48060000 48069FFF 0000a000
06/18/2012 13:02:54,spid57,Unknown,* psapi 76B70000 76B7AFFF 0000b000
06/18/2012 13:02:54,spid57,Unknown,* comctl32 77420000 77522FFF 00103000
06/18/2012 13:02:54,spid57,Unknown,* SHLWAPI 77DA0000 77DF1FFF 00052000
06/18/2012 13:02:54,spid57,Unknown,* SHELL32 7C8D0000 7D0D2FFF 00803000
06/18/2012 13:02:54,spid57,Unknown,* NETAPI32 71C40000 71C97FFF 00058000
06/18/2012 13:02:54,spid57,Unknown,* opends60 333E0000 333E6FFF 00007000
06/18/2012 13:02:54,spid57,Unknown,* USERENV 76920000 769E3FFF 000c4000
06/18/2012 13:02:54,spid57,Unknown,* WS2HELP 71BF0000 71BF7FFF 00008000
06/18/2012 13:02:54,spid57,Unknown,* WS2_32 71C00000 71C16FFF 00017000
06/18/2012 13:02:54,spid57,Unknown,* MSWSOCK 71B20000 71B60FFF 00041000
06/18/2012 13:02:54,spid57,Unknown,* Secur32 76F50000 76F62FFF 00013000
06/18/2012 13:02:54,spid57,Unknown,* MSASN1 76190000 761A1FFF 00012000
06/18/2012 13:02:54,spid57,Unknown,* CRYPT32 761B0000 76242FFF 00093000
06/18/2012 13:02:54,spid57,Unknown,* GDI32 77C00000 77C47FFF 00048000
06/18/2012 13:02:54,spid57,Unknown,* USER32 77380000 77411FFF 00092000
06/18/2012 13:02:54,spid57,Unknown,* RPCRT4 77C50000 77CEEFFF 0009f000
06/18/2012 13:02:54,spid57,Unknown,* ADVAPI32 77F50000 77FEBFFF 0009c000
06/18/2012 13:02:54,spid57,Unknown,* MSVCP80 7C420000 7C4A6FFF 00087000
06/18/2012 13:02:54,spid57,Unknown,* msvcrt 77BA0000 77BF9FFF 0005a000
06/18/2012 13:02:54,spid57,Unknown,* MSVCR80 78130000 781CAFFF 0009b000
06/18/2012 13:02:54,spid57,Unknown,* kernel32 77E40000 77F41FFF 00102000
06/18/2012 13:02:54,spid57,Unknown,* ntdll 7C800000 7C8BFFFF 000c0000
06/18/2012 13:02:54,spid57,Unknown,* sqlservr 01000000 02C13FFF 01c14000
06/18/2012 13:02:54,spid57,Unknown,* MODULE BASE END SIZE
06/18/2012 13:02:54,spid57,Unknown,*
06/18/2012 13:02:54,spid57,Unknown,*
06/18/2012 13:02:54,spid57,Unknown,* EN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' END
06/18/2012 13:02:54,spid57,Unknown,* FROM dbo.UDF_RxMidSummary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WH
06/18/2012 13:02:54,spid57,Unknown,* ' ELSE 'SCP' END AS Specialty<c/> MIN(NULLIF(RxPoints<c/> 0)) AS RxPoints
06/18/2012 13:02:54,spid57,Unknown,* 'Min without 0s' AS MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP
06/18/2012 13:02:54,spid57,Unknown,* N URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' END UNION ALL SELECT
06/18/2012 13:02:54,spid57,Unknown,* FROM dbo.UDF_RxMidSummary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHE
06/18/2012 13:02:54,spid57,Unknown,* D AS Specialty<c/> Functions.dbo.Median(NULLIF(RxPoints<c/> 0)) AS RxPoints
06/18/2012 13:02:54,spid57,Unknown,* S MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' EN
06/18/2012 13:02:54,spid57,Unknown,* HEN 'PCP' ELSE 'SCP' END UNION ALL SELECT 'Median without 0s' A
06/18/2012 13:02:54,spid57,Unknown,* mmary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHEN URMS.PCP = 'True' T
06/18/2012 13:02:54,spid57,Unknown,* Specialty<c/> AVG(NULLIF(RxPoints<c/> 0)) AS RxPoints FROM dbo.UDF_RxMidSu
06/18/2012 13:02:54,spid57,Unknown,* asureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' END AS
06/18/2012 13:02:54,spid57,Unknown,* N 'PCP' ELSE 'SCP' END UNION ALL SELECT 'Mean without 0s' AS Me
06/18/2012 13:02:54,spid57,Unknown,* ary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHEN URMS.PCP = 'True' THE
06/18/2012 13:02:54,spid57,Unknown,* P' END AS Specialty<c/> AVG(RxPoints) AS RxPoints FROM dbo.UDF_RxMidSumm
06/18/2012 13:02:54,spid57,Unknown,* 0s' AS MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SC
06/18/2012 13:02:54,spid57,Unknown,* = 'True' THEN 'PCP' ELSE 'SCP' END UNION ALL SELECT 'Mean with
06/18/2012 13:02:54,spid57,Unknown,* DF_RxMidSummary(@StartDate<c/> @EndDate) URMS GROUP BY CASE WHEN URMS.PCP
06/18/2012 13:02:54,spid57,Unknown,* D AS Specialty<c/> Functions.dbo.Median(RxPoints) AS RxPoints FROM dbo.U
06/18/2012 13:02:54,spid57,Unknown,* S MeasureType<c/> CASE WHEN URMS.PCP = 'True' THEN 'PCP' ELSE 'SCP' EN
06/18/2012 13:02:54,spid57,Unknown,* ate = '1/1/11' SET @EndDate = '12/31/11' SELECT 'Median with 0s' A
06/18/2012 13:02:54,spid57,Unknown,* DECLARE @StartDate DATETIME<c/> @EndDate DATETIME SET @StartD
06/18/2012 13:02:54,spid57,Unknown,* Input Buffer 510 bytes -
06/18/2012 13:02:54,spid57,Unknown,*
06/18/2012 13:02:54,spid57,Unknown,* A fatal error occurred in .NET Framework runtime.
06/18/2012 13:02:54,spid57,Unknown,*
06/18/2012 13:02:54,spid57,Unknown,* 06/18/12 13:02:54 spid 57
06/18/2012 13:02:54,spid57,Unknown,* BEGIN STACK DUMP:
06/18/2012 13:02:54,spid57,Unknown,*
06/18/2012 13:02:54,spid57,Unknown,* ******************************************************************
Nick
June 19, 2012 at 7:49 am
I am seeing some reports of this kind of issue with earlier builds of 2005. What build are you running?
SELECT SERVERPROPERTY('Edition') AS Edition,
SERVERPROPERTY('ProductVersion') AS ProductVersion,
SERVERPROPERTY('ProductLevel') AS ProductLevel
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 7:51 am
Edition ProductVersionProductLevel
Standard Edition9.00.3215.00SP2
Nick
June 19, 2012 at 9:34 am
SP2 is very old. Is applying SP4+CU3+MS11-049 an option?
One more question about the data. How many rows are you calculating the Median over? Median being what it is, you have to store every value before you can calculate the answer meaning your object may be asking for a lot of memory and I have also seen reports of a high volume of memory allocations coming from SQLCLR becoming an issue at times. The Sort in Terminate is also going to be very expensive. I wrote a Median aggregate in SQLCLR not too long ago as a proof-of-concept (POC) and IIRC I used a sorted collection so the class sorted values into the collection as they were read. I am not sure how this compares to your technique in terms of performance, but I can share my code if you're interested. Mine never made it beyond the POC and I never really hammered it from a performance perspective. SQL Server may not be serializing your object, which is why rewriting that method had no affect one way or the other. Just some thoughts.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
June 19, 2012 at 10:01 am
I don't think my group will want to update the service pack. Since we would have to test all of our DBs and retest them when we upgrade.
For the number of rows, the underlying table has 270,107 values but I do an aggregate before that, so when I run the median function it is only being done on 426 rows. It does work just fine when I use it on a straight select of 500 runs I have never had a problem.
That's an interesting idea I will try to rewrite my accumulate function to insert into a sorted position so I won't use the List classes sort function.
Nick
November 9, 2012 at 2:24 pm
Looks like it was the build of that server. I am currently testing my code on our new server (SQL Server 2012) and everything appears to be running great. Thanks for all the help.
Nick
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply