CLR Procedure error with third party .dll

I have a CLR procedure with the below code calling a third party .dll in the system32 directory of the SQL server:

using System;

using System.Collections;

using System.Data;

using System.Data.SqlTypes;

using System.Runtime.InteropServices;

using System.Text;

using Microsoft.SqlServer.Server;

public class CorrectAddressProcedures

{

[DllImport(@."C:\WINDOWS\system32\CorrectA.dll", EntryPoint = "FindCityCounty")]

public static extern int FindCityCounty(StringBuilder zip, StringBuilder cityname, StringBuilder state, StringBuilder countyname, StringBuilder countynum);

public static String Space(int len)

{

StringBuilder str = new StringBuilder("");

str.Append(' ', len);

return str.ToString();

}

[Microsoft.SqlServer.Server.SqlProcedure]

public static void spFindCityCounty(SqlString _zip, out SqlString _cityname, out SqlString _state, out SqlString _countyname, out SqlString _countynum)

{

int rc;

StringBuilder zip = new StringBuilder(5);

StringBuilder cityname = new StringBuilder(28);

StringBuilder state = new StringBuilder(2);

StringBuilder countyname = new StringBuilder(25);

StringBuilder countynum = new StringBuilder(3);

try

{

cityname.Append(Space(28));

state.Append(Space(2));

countyname.Append(Space(25));

countynum.Append(Space(3));

zip.Append(_zip);

rc = FindCityCounty(zip, cityname, state, countyname, countynum);

_cityname = cityname.ToString();

_state = state.ToString();

_countyname = countyname.ToString();

_countynum = countynum.ToString();

}

catch (Exception ex)

{

throw (ex);

}

}

}

I am getting the error

Msg 6522, Level 16, State 1, Procedure spFindCityCounty, Line 0

A .NET Framework error occurred during execution of user defined routine or aggregate 'spFindCityCounty':

System.DllNotFoundException: Unable to load DLL 'C:\WINDOWS\system32\CorrectA.dll': Not enough storage is available to process this command. (Exception from HRESULT: 0x80070008)

System.DllNotFoundException:

at CorrectAddressProcedures.spFindCityCounty(SqlString _zip, SqlString& _cityname, SqlString& _state, SqlString& _countyname, SqlString& _countynum)

.

I have restarted the service with memory allocation settings -g512 and -g1024 but still receive the error. The SQL server is an Intel Xeon 2.8GHZ with 2GB of RAM. Any thoughts of how to solve this would be greatly appreciated.

thanks,
Whitney

I failed to mention above that the .dll in question is a rather large one. 154MB to be exact.|||

hi,

how much memory space have you reserved for your SQL 2005 server.

in sql200 Management Studio:

Server Properties\Memory.

Try to enlarge your maximum memory or reduce the minimum memory to zero.

Restart the SQL Server and try again.

regards,

Martin

|||Checking SSMS I'm assuming the defaults were used. The minimum is set to 0MB and maximum is 2147483647MB. I have a startup parameter of 512MB and have also tried 1024MB with no luck.|||

Our vendor sent us a smaller DLL today (98MB vs 154MB) and the CLR procedures now work without code changes. Does anyone know if there is an undocumented ceiling (say around 100MB) that DLLImport has within the SQL CLR?

While I'm happy my code works I would still like to know the deeper detail on why I was getting the error.

Thanks,
Whitney