Error Calling SQLCLR UDF
Running SQL Dev Edition on Win2K3 Enterprise Edition. I get the following error.
Msg 6522, Level 16, State 2, Line 2
A .NET Framework error occurred during execution of user defined routine or aggregate 'AddressCorrect':
System.DllNotFoundException: Unable to load DLL 'D:\CorrectA.dll': Not enough storage is available to process
this command. (Exception from HRESULT: 0x80070008)
System.DllNotFoundException:
at UserDefinedFunctions.CorrectA(String query, String sentlen, StringBuilder errcode, StringBuilder FirmName, StringBuilder urbanization, StringBuilder Dline1, StringBuilder Dline2, StringBuilder LastLine, StringBuilder Stringaddress, StringBuilder DPC, StringBuilder Checkdigit, StringBuilder cityname, StringBuilder stcode, StringBuilder zip, StringBuilder addon, StringBuilder croute, StringBuilder LACS, StringBuilder LOTsequence, StringBuilder LOTcode, StringBuilder PMB, StringBuilder results, StringBuilder strnum, StringBuilder secname, StringBuilder secnum, StringBuilder countyname, StringBuilder countynum)
at UserDefinedFunctions.AddressCorrect(String inputAddress)
Box has 2GB Ram, with no other processes runing, cant understand why it says out of memory.
Appreciate any insights.
Thanks,
Saptagiri
Yes, I got the function running correclty outside SQL Server in a C# console application.
The DLL is quite big (141 MB), its a third party DLL I am trying to use to do address validation. Unfortunately part of the code is the third party's and I can not post it.
But it basically takes an address and validates it agianst a master address file and returns a correct address. It works like a charm as a console application, but it fails when I try to make a UDF using SQL CLR.
I am running it on a WIndows 2003 Server ( with 12 GB Ram), so surely memory is not a problem. There must be some parameter, where I can let SQL CLR accept a huge DLL.
Thanks,
Saptagiri
Wow - that's a big DLL.
You're hitting this error under SQLCLR because SQL limits the amount of memory that can be allocated by CLR and other tasks outside of the main SQL buffer pool. By default, this value is 256 MB and CLR can only claim a portion of that amount. Check the DMV sys.dm_os_memory_clerks to see the amount of memory reserved by each clerk.
To change this value, use the -g flag while starting sql server and set the amount of memory to leave to a higher amount (such as 512). More information about this start-up option is in BOL http://msdn2.microsoft.com/ms190737(en-us,VS.90).aspx
This is assuming that you're running on x86, as it is not an issue on 64-bit systems.
Steve,
Thanks for you reply. I should have said earlier but I am actually running this on
Intel Xeon X64 ( 2 * dual core processor machine)
12 GB RAM
SQL 2005 Dev Edition X64 Edition/ Windows 2003 Enterprise X64 Edition
This is a pure SQL2005 Box, this is my trial box. there are no other processes running on this box.
From your answer, it looks like this should not be an issue, but wonder why I am getting this on a X64 installation. Do you it could be bcos the DLL is a Win32 DLL?
Thanks,
Saptagiri
OK...
yes, I am loading a Win32 DLL, not a .Net DLL, pls see my code below
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
using System.Runtime.InteropServices;
using System.Text;
public partial class UserDefinedFunctions
{
***** I am importing a Win32 DLL and declaring a
[DllImport(@"D:\Xsaptagiri\CorrectA.dll", EntryPoint = "CorrectA")]
public static extern int CorrectA(some variables);
[Microsoft.SqlServer.Server.SqlFunction]
public static SqlString AddressCorrect(string inputAddress)
{
//declare variables for AddressCorrect function call
** I am calling the above declared function, in the DLLImport
int X = CorrectA(some variables);
string Y = x.ToString();
return new SqlString(Y);
}
};
-
If I were to do the same thing in a C# program, it works fine, so am wondering if I can load it into SQL..
Can you try making a new Win32 dll with just a dummy CorrectA function defined in it and see if that works for you in a smaller dll?
I'm curious if this is just a bad error message getting returned when sql server is having trouble finding/loading the dll due to permissions or something like that or if it is really a memory issue caused by the size of the dll.
Anyone who's had issues with P/Invoke from within a CLR Stored Proc/UDF might be interested to know the following (I've pinched the above code sample...)
You can get rid of the explicit file path in the DllImport attribute : -
[DllImport(@"CorrectA.dll", EntryPoint = "CorrectA")]
public static extern int CorrectA(some variables);
and drop the Win32 COM component dll in to the windows/system32 directory and SQL Server process should pick it up.
That's quite exciting when you've spent all afternoon figuring it out! Your .NET assembly will need Unsafe permission level in SQL by the way.
