CREATE ASSEMBLY ERROR: Msg 701

Question

Hello there,

 

i have the following problem.

 

I need to get some .dll's into MS SQL-Server 2005, that i need to get a own made .dll installed.

 

When i try to:

Code Block

CREATE ASSEMBLY SystemWeb

FROM 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

WITH PERMISSION_SET = UNSAFE;

 

 

i get the following message:

 

Code Block

Warning: The Microsoft .Net frameworks assembly 'system.web, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.enterpriseservices, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=x86.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.runtime.remoting, version=2.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Warning: The Microsoft .Net frameworks assembly 'system.design, version=2.0.0.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a, processorarchitecture=msil.' you are registering is not fully tested in SQL Server hosted environment.

Msg 701, Level 17, State 13, Line 1

There is insufficient system memory to run this query.

 

 

 

When i try to create one of the another assemblies ( system.enterpriseservices, system.runtime.remoting, system.design ) i get the same message.

 

I looked for this Error and found only this BUG-report:

BUG#: 58267 (SQLBUG_70)

Article ID: 274030

http://support.microsoft.com/kb/274030/en-us

 

But this does not solve my problem.

 

As far as i know we use MS SQL-Server 2005 without any Service-Packs.

 

Question:

is there any Table/View to find out the Versionnumber/Service-Pack

 

In the moment i am waiting for our admin to install SP2 for SQL-Server,

hoping that this will fix the problem.

 

Greetings

 

 

Answer 1

Why are you trying to register the System.web.dll ? If you are using this within your own assembly, this would be already discovered using the internal dependency walker during the resgistration.

Jens K. Suessmeyer

---
http://www.sqlserver2005.de
---

Answer 2

Not necessarily.

 

The SQL CLR has system assemblies pre-created for only a very specific subset of the .NET world, and deploying a SQLCLR assembly will not push any dependent assemblies into the database other than the primary one defined by the project.

 

You can see this by trying to add a reference to an existing SQLCLR project in Visual Studio 2005. You will only be able to add a reference to those assemblies already defined in the associated instance of SQL Server 2005.

 

The only way to make an assembly appear in that list is to create the assembly manually within SQL Management Studio first, which then presents its own chain of problems. If any of those dependencies, for example, have further dependencies, or are wrappers for COM or other unmanaged code, it will necessitate an assembly created with the UNSAFE permission set.

 

Many existing assmemblies that provide functionality that could be of interest to SQLCLR developers have a dependency on assemblies not present in SQL Server 2005, among them System.EnterpriseServices. Manually creating this assembly reveals, in turn, a significant number of downstream dependencies, some of which are rather curious. As an example, System.Web depends upon System.Drawing.

 

The 701 error is strongly indicative of non-buffer memory space being consumed by external DLL's, extended stored procedures, and SQLCLR routines. You can adjust the amount of memory reserved for non-buffer allocations by starting Sql Server with the "-g" switch, and specifying the amount of memory in megabytes to reserve for out-of-buffer allocations. The default is 256MB, plus 1/2MB for stack space for up to 255 worker threads.

 

Hope this helps.

 

-David

 

 

 

Answer 3

I have a bit more information on this matter that might be of help.

 

If you are encountering memory pressure when executing Create Assembly statements pertaining to non-registered system assemblies in SQL Server, you may be able to trick your way around the error by creating the assemblies in a specific order. It took some doing on my part to find the correct sequence, and I can't promise this same sequence will work for anyone else.

 

What's really happening, I think (and a SQLCLR guru could surely prove me wrong) is that, at assembly creation time, SQLCLR loads the entire assembly into memory in order for it to check for downstream dependencies, and dependencies on those dependencies, and so on. In essence, that becomes a "recursive" operation until all the assemblies are loaded and all the dependencies are identified. If even only one of those assemblies references just one other assembly that touches a bunch of others, most notably System.EnterpriseServices, SQLCLR will try to register all those other downstream assemblies for you. Loading all of those into the current appdomain is very likely to cause memory pressure in the non-buffer memory area.

 

The trick is to load some of those other assemblies first, thus precluding the need for the SQLCLR to load them at the time the "upstream" assembly is defined (because they're already present). Some of these "secondary" dependencies have only one or two dependencies themselves, which simplifies the process a bit. If you bring in the lower-level assemblies, you can then create the higher-level assembly and it won't error out with a 701.

 

I won't promise this load order will work for everyone, but here's the sequence I used successfully in order to create an assembly that depended upon System.EnterpriseServices:

 

System.DirectoryServices

System.Drawing

System.DirectoryServices.Protocols

System.Windows.Forms

System.Web.RegularExpressions

System.ServiceProcess

System.Web

 

Originally, System.EnterpriseServices would bring in *almost* all the dependencies it required, then 701 and fail, rolling the assemblies out. There were probably a dozen or more such dependencies. By loading them in this order, I noted at System.Web was able to bring in System.EnterpriseServices independently, and I was home free. My other assembly created properly.

 

You can check to verify which system assemblies are present by executing the command "select * from sys.assemblies"

 

I hope this is of help to someone else.

 

-David

 

 

 

 

Answer 4

Jens K. Suessmeyer wrote:

Why are you trying to register the System.web.dll ? If you are using this within your own assembly, this would be already discovered using the internal dependency walker during the resgistration. Jens K. Suessmeyer --- http://www.sqlserver2005.de ---

Not really ..

in SQL-Server you have only a Subset of the .NET framework.

Many classes you have to include in your Database by hand.

Answer 5

 

I like to thank you,

 

what i did not told is that is, of course, i went that way to load referenced dll's first to decrease the amount of

the dlls.

 

At the end of that proceeding there stood then 4 dlls:

System.Web.dll                     (5.156.864 bytes)

system.enterpriseservices.dll  (258.048 bytes)

system.runtime.remoting.dll    (299.008 bytes)

system.design.dll                   (5.001.216 bytes)

 

everyone of this throwed the 701 Error, not referencing other dlls than obove mentioned.

The rest was already imported by a CREATE ASSEMBLY... call.

 

By that way it is very surprising that these dlls with that little amount of Megabytes(kbytes) use that much memory,

like "soonerdave" mentioned above 256 MB (default setting SQL-Server; and i did not set other).

 

Let's think that SQL-Server has a default pagesize of 8kB, than we have maybe a cumulated allocated memory

of

629,5 => 627 pages

31,5 => 32 pages

36,5 => 37 pages

610,5 => 611 pages

===============

1307 pages = 10.706.944 bytes

 

I can't see any necessarity to use up to 256 MB, sorry.

Even if there where any unresolved reference.

 

I think that soonerdave gave me the workaround with the startoption.

 

BUT

I found a different way to bypass the import of that dlls.

I disassemled that dll i first wanted to import and made one of my own with only that content that i really needed.

So i avoided the import of the dlls.

 

Maybe that's not the preferred way but now i can go further with my work.

 

Thank you to everyone.

Answer 6

Please check if the system where you running this on is a 64 bit.

If so, you need to load different assembly.

This is really confusing because the 32 bit version is in the default .NET framework directory.
You need to load the System.Web.dll from the .64 version directory.

//Bart

Answer 7

TrabBurks wrote:

Please check if the system where you running this on is a 64 bit. If so, you need to load different assembly. This is really confusing because the 32 bit version is in the default .NET framework directory. You need to load the System.Web.dll from the .64 version directory. //Bart

 

No it's not a 64 bit Machine.

It is

Windows 2003 Server SP1

Running on

4 Intel Xeon 3.4 GHz

With 3Gb RAM

Answer 8

 

But did anybody of you notice that i found a Known Bug

according to an ERROR 701.

 

Article ID : 274030

http://support.microsoft.com/kb/274030/en-us

 

therefor i asked if you installed some ServicePacks.

Over here they did not.

And i think this whole discussion would maybe be negligible if they did.

 

 

Answer 9

The KB article you reference there (274030) discusses a bug generating an out of memory condition that arises when a query involving multiple outer joins is executed. The memory for query processing is taken from the buffer pool. Memory used by SQL Server for the purpose of creating assemblies is taken from the multi-page allocator, outside of the buffer pool. As a result, this particular bug very likely does not apply to this particular manifestation of a 701 error.

 

If I had my secret wish list, I would hope that Microsoft might amend SqlServer to include a more detailed error message when a memory allocation fails. In particular, if it were possible, I would love to see a positive indication of whether the memory failure was attemped against the buffer pool or outside the buffer pool...

 

-David

 

 

Answer 10

 

Is it possible to enlarge the amount of the multipage allocator?

 

Or

 

how to find what the value is in the moment?