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

CLR procedure calling webservice

Hi, I want to create following procedure to call a webservice. build ok
execution not ok.
When i do it in a seperated program it works. in the clr procedure not.
It always end with 'System.InvalidOperationException' occurred in
System.Xml.dll
Can some one help me.
Ludo
SQL code:
exec dbo.SendStatusToWebservice 'SQL2K5','TEST Ludo','GREEN'
.Net code
using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlTypes;
using System.Data.SqlClient;
using Microsoft.SqlServer.Server;
using System.Diagnostics;
public partial class CLR_Procedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
}
public static void SendStatusToWebservice(SqlString MyAppl, SqlString
MyMessage, SqlString MyStatus)
{
string log;
// Connect to webservice and add logging to it
SQL_UDP.bgc.wss.Library wlib = new SQL_UDP.bgc.wss.Library();
wlib.Credentials = System.Net.CredentialCache.DefaultCredentials;
log = wlib.WSScreateLog(MyAppl.Value);
wlib.WSSwriteLog(log, MyMessage.Value);// + " at @. " +
DateTime.Now.ToString);
wlib.SetBatchStatus(MyStatus.Value, log);
}
};
Debug result:
Auto-attach to process '[3068] [SQL] bgc-mikmxeue486' on machine
'bgc-mikmxeue486' succeeded.
Debugging script from project script file.
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\mscorlib\2.0.0.0__b77a5c561934e089\mscorlib.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
Auto-attach to process '[3068] sqlservr.exe' on machine 'bgc-mikmxeue486'
succeeded.
'sqlservr.exe' (Managed): Loaded 'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Binn\SqlAccess.dll', Skipped loading symbols. Module is
optimized and the debugger option 'Just My Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Data\2.0.0.0__b77a5c561934e089\System.Data.
dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System\2.0.0.0__b77a5c561934e089\System.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_32\System.Transactions\2.0.0.0__b77a5c561934e089\Syst
em.Transactions.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Security\2.0.0.0__b03f5f7f11d50a3a\System
.Security.dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Xml\2.0.0.0__b77a5c561934e089\System.Xml.
dll',
Skipped loading symbols. Module is optimized and the debugger option 'Just M
y
Code' is enabled.
'sqlservr.exe' (Managed): Loaded 'SQL_UDP', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Web.Services\2.0.0.0__b03f5f7f11d50a3a\Sy
stem.Web.Services.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded
'C:\WINNT\assembly\GAC_MSIL\System.Configuration\2.0.0.0__b03f5f7f11d50a3a\S
ystem.Configuration.dll', No symbols loaded.
'sqlservr.exe' (Managed): Loaded 'WebserviceCLR', Symbols loaded.
A .NET Framework error occurred during execution of user defined routine or
aggregate 'CallWebservice':
System.InvalidOperationException: Cannot load dynamically generated
serialization assembly. In some hosting environments assembly load
functionality is restricted, consider using pre-generated serializer. Please
see inner exception for more information. --> System.IO.FileLoadException:
LoadFrom(), LoadFile(), Load(byte[]) and LoadModule() have been disabled by
the host.
System.IO.FileLoadException:
at System.Reflection.Assembly.nLoadImage(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence evidence, StackCrawlMark& stackMark, Boolean
fIntrospection)
at System.Reflection.Assembly.Load(Byte[] rawAssembly, Byte[]
rawSymbolStore, Evidence securityEvidence)
at Microsoft.CSharp.CSharpCodeGenerator.FromFileBatch(CompilerParameters
options, String[] fileNames)
at
Microsoft.CSharp.CSharpCodeGenerator.FromSourceBatch(CompilerParameters
options, String[] sources)
at
Microsoft.CSharp.CSharpCodeGenerator.System.CodeDom.Compiler.ICodeCompiler.C
ompileAssemblyFromSourceBatch(CompilerPa
rameters options, String[] sources)
at
System.CodeDom.Compiler.CodeDomProvider.CompileAssemblyFromSource(CompilerPa
rameter
..
System.InvalidOperationException:
at System.Xml.Serialization.Compiler.Compile(Assembly parent, String ns,
CompilerParameters parameters, Evidence evidence)
at System.Xml.Serialization.TempAssembly.GenerateAssembly(XmlMapping[]
xmlMappings, Type[] types, String defaultNamespace, Evidence evidence,
CompilerParameters parameters, Assembly assembly, Hashtable assemblies)
at System.Xml.Serialization.TempAssembly..ctor(XmlMapping[] xmlMappings,
Type[] types, String defaultNamespace, String location, Evidence evidence)
at System.Xml.Serialization.XmlSerializer.FromMappings(XmlMapping[]
mappings, Type type)
at System.Web.Services.Protocols.SoapClientType..ctor(Type type)
at System.Web.Services.Protocols.SoapHttpClientProtocol..ctor()
at WebserviceCLR.wsslib.Library...
No rows affected.
(0 row(s) returned)
Finished running sp_executesql.
A first chance exception of type 'System.InvalidOperationException' occurred
in System.Xml.dll
The thread 'bgc-mikmxeue486 [61]' (0xd60) has exited with code 0 (0x0).
The program '[3068] [SQL] bgc-mikmxeue486: bgc-mikmxeue486' has exited with
code 0 (0x0).
The program '[3068] sqlservr.exe: Managed' has exited with code 259 (0x103)."examnotes" <Ludo@.discussions.microsoft.com> wrote in
news:825F081A-11D6-4D36-8C9A-F8B4AEA96CE5@.microsoft.com:

> Hi, I want to create following procedure to call a webservice. build
> ok execution not ok.
> When i do it in a seperated program it works. in the clr procedure
> not. It always end with 'System.InvalidOperationException' occurred
> in System.Xml.dll
> Can some one help me.
>
[snip]

> In some hosting
> environments assembly load functionality is restricted, consider using
> pre-generated serializer. Please see inner exception for more
> information. --> System.IO.FileLoadException: LoadFrom(), LoadFile(),
> Load(byte[]) and LoadModule() have been disabled by the host.
> System.IO.FileLoadException:
As the error says, SQLCLR doesn't allow you to load a dynamically
generated assembly (which happens when you do web-services). You need to
sgen the proxy code into a dll and catalogue that assembly in SQL
Server.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********

Closing a SqlDataReader after calling from a DAL....?

I have a DAL that I'm trying to implement - the issue is that I want to call a reader from the DAL, but I'm not sure how to close it. I got best practices from MSDN (located here:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfsystemdatasqlclientsqldatareaderclasstopic.asp ) regarding the SqlDataReader, but can't seem to figure out how to close when using DAL.

Here is sample code in C#:

NOTE: Everything WORKS just fine, however in the DAL for GetRoutes, I can't seem to figure out how to close the reader (see comments in function):

mydatapage.aspx
.
.
.
//Populating some Drop Down List:
private void ddlRoutes_SelectedIndexChanged(object sender, System.EventArgs e) {
SqlDataReader dr = DAL.GetRoutes(ddlRoutes.SelectedValue.ToString());
while (dr.Read())
{
.
.
.
}
}

DataAccessLayer.cs
.
.
.
public static SqlDataReader GetRoutes(string sIdx){
cnn = new SqlConnection(ConnectionString);
cnn.Open();
SqlCommand cmd = new SqlCommand();
SqlParameter par = new SqlParameter();
cmd.Connection=cnn;
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = "sp_GetRoutes";

return cmd.ExecuteReader();

//Everything works above, however I would think that something
//like this SHOULD work, but doesn't:
//
// SqlDataReader dr;
// dr = cmd.ExecuteReader()
// return dr;
// cnn.close();
}

Right now, you pass the SqlDataReader to the method in your page. If you reverse this--andpass the method to the SqlDataReader--your DAL can maintain control of the reader and the database connection.

To learn how to do this, please read the following article:Using Delegates with Data Readers to Control DAL Responsibility

|||

what you have to do is follwing

dr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

then when you close dr it will close the connection. However passing datareader around is not a good idea. It should stay in your DAL.