April 17, 2009 at 2:59 am
I am having difficulties creating an Assembly for a vb.net project I have written.
I am getting the error:
Msg 6511, Level 16, State 82, Line 2
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x8007000e. You may fix the problem and try again later.
I have been successful in creating this assembly before on this server but i made a small change the the assembly so I deleted the assembly and am now trying to recreate it.
I am not 100% sure that I even need to delete the Assembly within SQL for it to register any changes in the built project since the first Created the Assembly?
My code for the creation of the assembly is as below and i haven't changed it since it previously worked:
USE [NSG_Data]
GO
/****** Object: SqlAssembly [asmFindNearestRoad] Script Date: 16/16/2009 08:32:48 ******/
CREATE ASSEMBLY [asmFindNearestRoad]
AUTHORIZATION [dbo]
FROM '\\###\###\###\NSG Data\VB Projects\FindNearestRoad\obj\Release\FindNearestRoad.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
n.b. # represents varying numbers of characters in my UNC path!
I have searched the internet for the cause of this error and from my research i have found that the code 0x8007000e is a to do with a lack of virtual memory available to the CLR.
One proposed solution which i have found is to use the -g switch in the Startup Parameter of the SQL Service (MSSQLSERVER) which i have tried both -g512 and -g1024 and stopped and restarted the service.
Neither values have been either sufficient or a solution.
I have also read that it might be a problem with SP2 for SQL as per
http://forums.asp.net/t/1098345.aspx
I am running SQL2005 with SP2 on Windows Server 2003.
I was hoping that someone else might know of another reason for this problem. I appreciate any assistance.
April 17, 2009 at 9:23 am
See if you can load the prior version.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
April 19, 2009 at 12:28 am
Hey Matthew,
The error code appears to be COR_E_OUTOFMEMORY.
Can you tell us a bit more about the server and SQL Server configuration please, especially as far as memory is concerned. For example:
* Is the server 32 or 64 bit
* How much physical memory?
* How much memory is shown as available in task manager?
* Which edition of SQL Server is it?
* Please post the output of DBCC MEMORYSTATUS
* Does the problem persist just after a server reboot / SQL Server service restart
It might also be instructive to give us a peek at the code behind the new CLR routine, highlighting changes since the previous version.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 1:22 am
Paul thanks for your questions i shall answer them shortly. Just a quick update we tried increasing the memory available by increasing the -g switch to -g1024 and restarted the server over the weekend.
I have tried creating the assembly this morning and have encountered a different error:
Msg 6513, Level 16, State 27, Line 2
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.
I did read about AWE during my research into the problem and will have to revisit that section.
Unfortunately our DBA is now on holiday because i am sure he would understand the message.
April 20, 2009 at 2:29 am
Paul in answer to your questions.
The server is 32 bit, it has 4.00GB of physical memory.
Virtual Memory is configered:
C: 512 - 1024MB
D: 6144 - 8192MB
Which I am told is how a well known server manurfacturer decides to split the partitions of the disks when supplied.
Currently in task manager I have.
PF Usage: 2.99GB
Physical Memory: 4193360 Total, 1037748 Available, System, 769124 System Cache.
The results of dbcc MemoryStatus are:
Memory Manager KB
------------------------------ --------------------
VM Reserved 2721472
VM Committed 2350000
AWE Allocated 0
Reserved Memory 1024
Reserved Memory In Use 0
(5 row(s) affected)
Memory node Id = 0 KB
------------------------------ --------------------
VM Reserved 2717312
VM Committed 2345992
AWE Allocated 0
MultiPage Allocator 17672
SinglePage Allocator 1582648
(5 row(s) affected)
MEMORYCLERK_SQLGENERAL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 20992
MultiPage Allocator 2408
(7 row(s) affected)
MEMORYCLERK_SQLBUFFERPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 2684888
VM Committed 2314832
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 656
(7 row(s) affected)
MEMORYCLERK_SQLOPTIMIZER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 616
MultiPage Allocator 72
(7 row(s) affected)
MEMORYCLERK_SQLUTILITIES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 360
VM Committed 360
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 400
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLSTORENG (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 4736
VM Committed 4736
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 2680
MultiPage Allocator 120
(7 row(s) affected)
MEMORYCLERK_SQLCONNECTIONPOOL (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 832
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLCLR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 576
VM Committed 40
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 216
MultiPage Allocator 120
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 168
MultiPage Allocator 192
(7 row(s) affected)
MEMORYCLERK_SQLHTTP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SNI (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 72
MultiPage Allocator 16
(7 row(s) affected)
MEMORYCLERK_FULLTEXT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_SQLXP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_BHF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 264
MultiPage Allocator 0
(7 row(s) affected)
MEMORYCLERK_HOST (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 128
MultiPage Allocator 64
(7 row(s) affected)
MEMORYCLERK_SOSNODE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 12848
MultiPage Allocator 9928
(7 row(s) affected)
MEMORYCLERK_SQLSERVICEBROKERTRANSPORT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 24
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_OBJCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 26352
MultiPage Allocator 152
(7 row(s) affected)
CACHESTORE_SQLCP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1494720
MultiPage Allocator 3752
(7 row(s) affected)
CACHESTORE_PHDR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4640
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XPROC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 80
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_TEMPTABLES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 32
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_NOTIF (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_VIEWDEFINITIONS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBTYPE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBELEMENT (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_XMLDBATTRIBUTE (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_STACKFRAMES (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 0
MultiPage Allocator 8
(7 row(s) affected)
CACHESTORE_BROKERTBLACS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 632
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERKEK (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERDSH (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERUSERCERTLOOKUP (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERRSB (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERREADONLY (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 88
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_BROKERTO (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 8
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_EVENTS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 16
MultiPage Allocator 0
(7 row(s) affected)
CACHESTORE_SYSTEMROWSET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 4536
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SCHEMAMGR (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3560
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_DBMETADATA (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 3552
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_TOKENPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 448
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_OBJPERM (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 776
MultiPage Allocator 0
(7 row(s) affected)
USERSTORE_SXC (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 248
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LBSS (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 408
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_SNI_PACKET (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 1944
MultiPage Allocator 48
(7 row(s) affected)
OBJECTSTORE_SERVICE_BROKER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 0
VM Committed 0
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 304
MultiPage Allocator 0
(7 row(s) affected)
OBJECTSTORE_LOCK_MANAGER (Total) KB
---------------------------------------------------------------- --------------------
VM Reserved 8192
VM Committed 8192
AWE Allocated 0
SM Reserved 0
SM Commited 0
SinglePage Allocator 888
MultiPage Allocator 0
(7 row(s) affected)
Buffer Distribution Buffers
------------------------------ -----------
Stolen 4916
Free 21945
Cached 192915
Database (clean) 57740
Database (dirty) 8916
I/O 0
Latched 0
(7 row(s) affected)
Buffer Counts Buffers
------------------------------ --------------------
Committed 286432
Target 332272
Hashed 66656
Stolen Potential 117827
External Reservation 0
Min Free 128
Visible 332272
Available Paging File 956312
(8 row(s) affected)
Procedure Cache Value
------------------------------ -----------
TotalProcs 17074
TotalPages 191212
InUsePages 346
(3 row(s) affected)
Global Memory Objects Buffers
------------------------------ --------------------
Resource 289
Locks 114
XDES 32
SETLS 4
SE Dataset Allocators 8
SubpDesc Allocators 4
SE SchemaManager 444
SQLCache 2384
Replication 2
ServerGlobal 26
XP Global 2
SortTables 2
(12 row(s) affected)
Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 104895
Maximum (Buffers) 104895
Limit 104909
Next Request 0
Waiting For 0
Cost 0
Timeout 0
Wait Time 0
Last Target 110430
(11 row(s) affected)
Small Query Memory Objects Value
------------------------------ -----------
Grants 0
Waiting 0
Available (Buffers) 5523
Maximum (Buffers) 5523
Limit 5523
(5 row(s) affected)
Optimization Queue Value
------------------------------ --------------------
Overall Memory 2181758976
Target Memory 519471104
Last Notification 1
Timeout 6
Early Termination Factor 5
(5 row(s) affected)
Small Gateway Value
------------------------------ --------------------
Configured Units 16
Available Units 16
Acquires 0
Waiters 0
Threshold Factor 250000
Threshold 250000
(6 row(s) affected)
Medium Gateway Value
------------------------------ --------------------
Configured Units 4
Available Units 4
Acquires 0
Waiters 0
Threshold Factor 12
(5 row(s) affected)
Big Gateway Value
------------------------------ --------------------
Configured Units 1
Available Units 1
Acquires 0
Waiters 0
Threshold Factor 8
(5 row(s) affected)
MEMORYBROKER_FOR_CACHE Value
-------------------------------- --------------------
Allocations 192916
Rate 32
Target Allocations 251450
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_STEAL Value
-------------------------------- --------------------
Allocations 4910
Rate 0
Target Allocations 63412
Future Allocations 0
Last Notification 1
(5 row(s) affected)
MEMORYBROKER_FOR_RESERVE Value
-------------------------------- --------------------
Allocations 0
Rate 0
Target Allocations 117800
Future Allocations 59298
Last Notification 1
(5 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
The problem does still persist after a server reboot. By that the server was rebooted on Friday night on my request and I have tried to load the assembly this morning with no success.
My vb.net code is:
Imports System.Data.SqlClient
Imports System.Data.SqlTypes
Imports System.Security
Imports Microsoft.SqlServer.Server
Public Class FindNearestRoad
_
Public Shared Function FindRoad(ByVal xWorks As Double, ByVal yWorks As Double) As Double
Dim oSQLConn As SqlConnection = New SqlConnection
Dim strConn As String = "Data Source=(local);" & _
"Initial Catalog=NSG_Data;" & _
"Integrated Security=sspi;"
'"Data Source=(local);" & _
'"Initial Catalog=NSG_Data;" & _
'"Integrated Security=sspi;" & _
'"Enlist=false"
oSQLConn.ConnectionString = strConn
Dim oSQLCmd As SqlCommand = New SqlCommand
Dim oSQLPer As SqlClientPermission = New SqlClientPermission(Security.Permissions.PermissionState.None)
oSQLPer.Add(strConn, "", KeyRestrictionBehavior.AllowOnly)
oSQLPer.Assert()
oSQLConn.Open()
Dim strCmd As String = "SELECT * FROM [dbo].[FullESUCoords] ORDER BY [ESUID], [COORD_NUMBER]"
oSQLCmd.Connection = oSQLConn
oSQLCmd.CommandText = strCmd
Dim oSQLRead As SqlClient.SqlDataReader = oSQLCmd.ExecuteReader
Dim dblClosestDistance(1) As Double
dblClosestDistance(0) = 100000000.0#
Dim dblDistance As Double
Dim dblDistanceCoords(1, 1) As Double
While oSQLRead.Read()
If oSQLRead("COORD_NUMBER") 1 Then
dblDistanceCoords(1, 0) = oSQLRead("ESU_X_COORD")
dblDistanceCoords(1, 1) = oSQLRead("ESU_Y_COORD")
dblDistance = Distance(dblDistanceCoords(0, 0), dblDistanceCoords(0, 1), dblDistanceCoords(1, 0), dblDistanceCoords(1, 1), xWorks, yWorks)
If dblClosestDistance(0) > dblDistance Then
dblClosestDistance(0) = dblDistance
dblClosestDistance(1) = oSQLRead("ESUID")
End If
dblDistanceCoords(0, 0) = dblDistanceCoords(1, 0)
dblDistanceCoords(0, 1) = dblDistanceCoords(1, 1)
Else
dblDistanceCoords(0, 0) = oSQLRead("ESU_X_COORD")
dblDistanceCoords(0, 1) = oSQLRead("ESU_Y_COORD")
dblDistanceCoords(1, 0) = 0
dblDistanceCoords(1, 1) = 0
End If
End While
FindRoad = dblClosestDistance(1)
oSQLRead.Close()
oSQLConn.Close()
End Function
Public Shared Function Distance(ByVal x1 As Double, ByVal y1 As Double, ByVal x2 As Double, ByVal y2 As Double, ByVal xTest As Double, ByVal yTest As Double) As Double
Dim A As Boolean = False
Dim B As Boolean = False
'http://mathworld.wolfram.com/Point-LineDistance2-Dimensional.html
'd = [(x2 - x1)(y1-y0) - (x1-x0)(y2-y1)]/[(x2 - x1)² + (y2 - y1)²]^½
' x0 = xTest
' x1 = x1
' x2 = x2
' y0 = yTest
' y1 = y1
' y2 = y2
'http://www.topcoder.com/tc?module=Static&d1=tutorials&d2=geometry1
'A[0] = x1
'A[1] = x2
'B[0] = y1
'B[1] = y2
'C[0] = xtest
'C[1] = ytest
'Beyond A i.e. [x1,y1]. Test the Dot product of BA·AC
If (((x1 - x2) * (xTest - x1)) + ((y1 - y2) * (yTest - y1))) > 0 Then A = True
'Beyond B i.e. [x2,y2]. Test the Dot product of AB·BC
If (((x2 - x1) * (xTest - x2)) + ((y2 - y1) * (yTest - y2))) > 0 Then B = True
'If both dot products are negative, then the nearest point to C is somewhere along the segment.
If Not A And Not B Then
'This part works to give the distance to the line perpendicular at any point i.e. on an infinate line. thus if the line has such a gradient to intersect with the point (xTest, yTest) then the distance will be zero thus we need the above test to only run this code if the perpendicular line to the boundry is within the two points marked and not outside on the infinate
'Using the formula to calculate the perpendicular distance to the line between the two points
If x1 x2 Or y1 y2 Then 'result of d becomes an overflow if the coords are the same i.e. 0/0
Distance = (((x2 - x1) * (y1 - yTest)) - ((x1 - xTest) * (y2 - y1))) / Math.Sqrt(((x2 - x1) ^ 2) + (((y2 - y1) ^ 2)))
Distance = Math.Sqrt(Distance ^ 2)
Else
Distance = Math.Sqrt(((xTest - x1) ^ 2) + ((y1 - yTest) ^ 2))
End If
ElseIf A Then
'Closest to point A i.e. [x1,y1]. Application of Pythagoras
Distance = Math.Sqrt(((xTest - x1) ^ 2) + ((y1 - yTest) ^ 2))
ElseIf B Then
'Closest to point B i.e. [x2,y2]. Application of Pythagoras
Distance = Math.Sqrt(((xTest - x2) ^ 2) + ((y2 - yTest) ^ 2))
End If
End Function
End Class
The change I made to the code was in the connection string to include '"Enlist=false" which i have now commented out to try and revert the code back to when the assembly was loading correctly.
I needed to add Enlist=false to the connection string to create a new transaction for the code to run in. I needed this change after developing a trigger that executes the code to update the inserted record with the nearest road value.
The trigger reads:
USE NSG_Data
GO
CREATE TRIGGER [dbo].[trgINSERTINTOtblNearestRoadNSGRef]
ON tblNearestRoad
AFTER INSERT AS
BEGIN
DECLARE@X float,
@Y float,
@ESU float,
@NSGRef int
SET @X = (SELECT [Easting] FROM inserted)
SET @Y = (SELECT [Northing] FROM inserted)
SET @ESU = dbo.clrFindNearestRoad(@X, @Y)
SET @NSGRef = (
SELECT dbo.tblStreet.USRN
FROM dbo.tblStreet INNER JOIN
dbo.tblStreetXREF ON dbo.tblStreet.USRN = dbo.tblStreetXREF.USRN
WHERE (dbo.tblStreetXREF.XREF_ID = @ESU) AND (RECORD_TYPE = 1
UPDATE dbo.tblNearestRoad
SET [USRN] = @NSGRef, [Status] = 'Success'
WHERE (JCSJobNumber = (SELECT JCSJobNumber FROM inserted))
ELSE
UPDATE dbo.tblNearestRoad
SET[Status] = 'Fail'
WHERE (JCSJobNumber = (SELECT JCSJobNumber FROM inserted))
END
GO
I have had the system all working correctly in my test database and didn't have any of these errors on that machine. For interest it is the 30 trial virtual pc hard disk which is available for download from microsoft.
April 20, 2009 at 3:11 am
Thanks for the details Matthew (though you forgot to include whether it is SQL Server Enterprise, Developer, Standard or Workgroup!)
Before I get too deeply into your post, I've just noticed from your original post:
matthewr (4/17/2009)
CREATE ASSEMBLY [asmFindNearestRoad]AUTHORIZATION [dbo]
FROM '\\###\###\###\NSG Data\VB Projects\FindNearestRoad\obj\Release\FindNearestRoad.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
I missed the UNC path first time around! :blush:
Copy the DLL to a local drive on server (and make sure the account the SQL Server service is running under has access to it!) and try this again. OOM errors are often the default when MS products can't detect the real reason for failure. I can't guarantee that this will solve all your problems, but it is a strong contender, and we need to eliminate it before proceeding.
If you are able to restart SQL Server without undue drama, please also remove that -g switch.
BTW I usually enable the clr (sp_configure + RECONFIGURE) before attempt to CREATE ASSEMBLY 😉
If you still have problems, could you please post the output of:
select * from sys.dm_clr_properties
You can run that in any database.
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 3:29 am
Paul good point to try. Thanks again for all your help.
I have just moved the dll to C:\ and have tried the following code:
USE [NSG_Data]
GO
EXEC sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
/****** Object: SqlAssembly [asmFindNearestRoad] Script Date: 16/16/2009 08:32:48 ******/
CREATE ASSEMBLY [asmFindNearestRoad]
AUTHORIZATION [dbo]
FROM 'C:\FindNearestRoad.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS
I have just remembered was using a mapped network drive, U:\ as the location of the file before it stopped working, but changed it to a UNC path to see if that made any difference and didn't ever change it back. Unfortunately C: doesn't work either.
You are certainly right about the order of my execution. It does indeed make much more since to do it your way round.
Unfortunately we are still getting the same message as my second post:
Configuration option 'clr enabled' changed from 1 to 1. Run the RECONFIGURE statement to install.
Msg 6513, Level 16, State 27, Line 2
Failed to initialize the Common Language Runtime (CLR) v2.0.50727 due to memory pressure. Please restart SQL server in Address Windowing Extensions (AWE) mode to use CLR integration features.
I have since my last post read:
We have implemented on the advice of our IT support contractors step 2 and part of step 3 only so far just to try. They are going to stop and start the SQLservice tonight for me.
Unfortunately because i didn't experience this problem in test i am now trying to solve it in live and as such it takes at least a day to try anything that requires either a server restart or service restart.
April 20, 2009 at 3:36 am
Paul I forgot again.
We are running SQL Standard 9.00.3042.00 SP2.
April 20, 2009 at 5:20 am
Matthew,
Thanks for that.
Did you remove the -g start up option? That is most likely casing the 'memory pressure' message. Once that goes away, moving the DLL to the local drive may well have fixed the original error - if you see what I mean.
See this Microsoft article to see why using the -g switch is not helpful here. Reserving 1GB on 2GB physical for extended procedure .dll files and OLE DB providers is not a good idea 🙂
Standard edition does not support 'lock pages in memory' - that's an Enterprise- (and Developer/Trial) only feature.
Locking pages in memory only helps prevent buffer pool pages being swapped out by the OS.
It is a red herring here - the reason SQL Server is short of memory is down to the massive -g allocation.
I'm going to look through the monster post from earlier now.
Oh, and if I wasn't clear, please *remove* the -g option, not just reduce the value. The default 256MB is ample!
Cheers,
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 5:38 am
I have just taken the -g switch out of the Startup Parameter.
I didn't put it in myself so just to clarify that I have done it correctly
I have removed the text:
; -g1024
from the end of the Startup Parameters within the MSSQLSERVER service.
From what the message said when I made the amendment and from my prior reading this requires a restart of the service which I have scheduled to do at the end of play today.
I can then, tomorrow morning, try the other changes we have made today which are:
1. local access to dll
2. Given the service running user accounts access to “Enable Lock Pages in Memory to Prevent Database Paging to Disk”
(which you mention isn't beneficial to Standard edition, but we already did it before you mentioned it).
3. Enable AWE Option.
Hopefully one of or a combination of these will solve it. Perhaps we have now made too many changes for one day and won't know which change solves the problem should it be resolved tomorrow morning.
I shall keep you all informed. I appreciate the assistance, thanks very much.
April 20, 2009 at 5:57 am
Matthew,
You removed the -g1024 correctly.
Drop the assembly if it exists, add re-create it from a local disk. Good stuff.
Lock pages in memory will do nothing, as you know. Oh well, it won't do any harm either.
Enabling AWE pointless on a server with less than 4GB RAM. It won't do any harm, I think - not 100% on that.
Good luck!
And you are welcome, of course.
Paul
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
April 20, 2009 at 6:00 am
Just had another thought my thirty day trial version must have been enterpriser eddition, although I never did check.
The windows server operating system on that virtual hard disk was certainly windows server 2003 enterpriser.
I have no way of double checking that because i have unfortunately run out of trial time. I need to buy the developers version to have on my computer for the next project.
That was part of the reason for the UNC path for where the project was so that I didn't lose all my work when the trial period run out.
April 20, 2009 at 2:03 pm
Your function may be overloading the call stack on recursion. You might try passing an incremented variable on each call and if > 1000 (or some convenient #) to exit with a message.
April 21, 2009 at 2:51 am
We have done it! It has worked! Thanks Paul and everyone else that has contributed.
Am really pleased this is my first .net / sql project and although it has taken a few months to complete I am very pleased with what I have managed to achieve and what I have learnt.
Roll on the next project.
April 21, 2009 at 2:54 am
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply