There is insufficient system memory to run this query - system.web

Question

Post reply

There is insufficient system memory to run this query - system.web

Pavel Spálený

SSC Journeyman

Points: 79
More actions
July 19, 2008 at 3:05 am

#194169

Hi,
I have a big troupble with one MS SQL server. There is 4GB RAM and using is about 2GB RAM.
I need registr .NET assembly system.web bec I need use httputilizie to encode some special chars (e.g. space to &nbsp).
But when I try to registr assembly, I have this error : There is insufficient system memory to run this query
I use this query:
CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe
thanks a lot
Pavel Spaleny

Viewing 15 posts - 1 through 15 (of 18 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 1

What are you trying to do with the CLR?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Pavel Spálený SSC Journeyman Points: 79 More actions · Answer 2

At assembly I call webservice. One of input parametr is body of e-mail. And at some emails are (at ascii) some chars (e.g. char(0), bullets etc.)). When are at email these "special" chars, assembly can not call webservice (xml parser crash).

So, i want to call at assembly (is at C#) HttpUtility.HtmlEncode() method to change special chars to HTML coded chars (and XML whitch assembly send to webservice will be OK ). But these method is at system.web assembly...

RGP SSCarpal Tunnel Points: 4381 More actions · Answer 3

Pavel Spálený (7/19/2008)
Hi,
I have a big troupble with one MS SQL server. There is 4GB RAM and using is about 2GB RAM.
I need registr .NET assembly system.web bec I need use httputilizie to encode some special chars (e.g. space to &nbsp).
But when I try to registr assembly, I have this error : There is insufficient system memory to run this query
I use this query:
CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe
thanks a lot
Pavel Spaleny

Hi, I just have a few questions. are you using MS SQL 2005?? are you running "

CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

with permission_set = unsafe

"

through Query Analyser?

Can you please tell the complete message you get?

thanks,

😛

Pavel Spálený SSC Journeyman Points: 79 More actions · Answer 4

Yes, I use SQL Server 2005 with last SP.

this is my query:

ALTER DATABASE OM_RWE_TEST SET TRUSTWORTHY ON

go

EXEC sp_changedbowner 'sa'

GO

CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'

with permission_set = unsafe

all I run at SA login

george sibbald SSC Guru Points: 104210 More actions · Answer 5

you may be running out of memory because of whatever else is running on the server. Look at lastwaittype in sysprocesses, if you see a lot or resource_semaphore waittypes this is indicative of this. If you see that only when you run your query, its your query that causes it!

Use profiler to see the longest duration and highest memory usage queies and see if they can be improved.

As you are on SQL2005 and especially if SQL is all that runs on the server, you can set the /3GB switch in the boot.ini and thus allow SQL to allocate more than 2GB memory.

---------------------------------------------------------------------

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 6

Instead of trying to reinvent the wheel... why not just use sp_MakeWebTask?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

FYI: sp_MakeWebTask is deprecated in 2005 and supposedly removed in 2008 (I haven't checked this). BOL recommends using Reporting Services for this(?).

[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]

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 8

That's a bummer... just goes to show what I've said in the past when people pitch a fit about using undocumented stored procedures... I tell them "Look! Even the documented stored procedures aren't safe!" 😛

I sure wish Microsoft would stop removing the good stuff... :crazy: though I'm not sure one little ol' MVP can sway them.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 9

rbarryyoung (7/20/2008)
FYI: sp_MakeWebTask is deprecated in 2005 and supposedly removed in 2008 (I haven't checked this).

So far it still exists in SQL 2008.

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 10

Pavel Spálený (7/20/2008)
CREATE ASSEMBLY SystemWeb from 'C:\WINDOWS\Microsoft.NET\Framework\v2.0.50727\System.Web.dll'
with permission_set = unsafe

System.Web is unsupported in SQL Server for a reason. Even if you can get it loaded, you can't get support for any problems associated with an assembly that uses it. Microsoft Support will require that the problems be reproduced with the assemblies removed from SQL Server. By cataloging unapproved assemblies inside SQL Server, you open yourself up to problems in SQL if the GAC MVID of the assembly changes. For instance, applying a service pack to the .NET Framework can result in the following error the next time your CLR Assembly Loads:

Msg 10314, Level 16, State 11, Line 1

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65536. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE.... (rest of message elided).

or

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

A .NET Framework error occurred during execution of user-defined routine or aggregate "TestUnapproved":

System.IO.FileLoadException: Could not load file or assembly 'System.Runtime.Remoting, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' or one of its dependencies. Assembly in host store has a different signature than assembly in GAC.

You need to look at how to do this outside of the datatier. Your application of CLR inside of SQL is misplaced for this.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

The problem is that there is virtually nothing worthwhile that you can do in SQLCLR that isn't either unsafe, unsupported, impossible or just better done in T-SQL to begin with. The more I learn about SQLCLR, the more amazed I become at how Microsoft has completely boned us on this.

I do not see any way that SQLCLR can realistically replace XP's.

[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]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 12

rbarryyoung (7/20/2008)
The problem is that there is virtually nothing worthwhile that you can do in SQLCLR that isn't either unsafe, unsupported, impossible or just better done in T-SQL to begin with. The more I learn about SQLCLR, the more amazed I become at how Microsoft has completely boned us on this.
I do not see any way that SQLCLR can realistically replace XP's.

There is a lot that can be done that is External_access and not unsafe, that is quite useful. I wouldn't allow any unsafe code in a database. You can replace a number XP's with External_Access code though. Calling a webservice only requires External_Access. The big problem is that people don't want to work in the confines of the approved assemblies in SQLCLR. You can solve many problems by first sticking to the approved code base, and then if you need something in an unapproved assembly, you may need to write a little bit of code to abstract certain parts of logic into a safe implementation for SQLCLR, rather than trying to register an unsafe assembly into SQL.

One thing people fail to pay head to is, as you say, you can do a whole lot with TSQL that will perform better than CLR will if you know what you are doing. A lot of times, I see people who try to do it all in CLR because they don't know, or want to learn TSQL. CLR is not the answer to most problems. I still stand by the old rule of thumb that procedural business logic belongs in a middle tier outside of the database. At my job SQL Server does CRUD for the most part in any internally developed application, that is what it does best. All the CLR work I do is for fun, for DBA task simplification, or to answer posts online.

Jeff Moden SSC Guru Points: 1004403 More actions · Answer 13

Jonathan Kehayias (7/20/2008)
I still stand by the old rule of thumb that procedural business logic belongs in a middle tier outside of the database.

Heh... like everything else, it depends... it depends mostly on how well someone knows T-SQL and some of the "Tricks of the Trade"... 😛

http://www.sqlservercentral.com/articles/Advanced+Querying/61716/

http://www.sqlservercentral.com/articles/TSQL/62867/

http://www.sqlservercentral.com/articles/T-SQL/63003/

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 14

Jonathan Kehayias (7/20/2008)
rbarryyoung (7/20/2008)
The problem is that there is virtually nothing worthwhile that you can do in SQLCLR that isn't either unsafe, unsupported, impossible or just better done in T-SQL to begin with. The more I learn about SQLCLR, the more amazed I become at how Microsoft has completely boned us on this.
I do not see any way that SQLCLR can realistically replace XP's.
There is a lot that can be done that is External_access and not unsafe, that is quite useful.

I am eager to be enlightened, Jonathan. Please list some of these "many things" that can be done in SQLCLR with External_Access permissions, that is worthwhile in a real-world, practical sense that we couldn't do better from T-SQL? I mean beyond limited browsing of the Filesystem?

I wouldn't allow any unsafe code in a database.

Well, I sure don't want to, but since my customers & I believed MS & BOL about what they said could be done with SQLCLR in EXTERNAL_ACCESS and now, many months later we are at the point of deployment only to find out that it can only be done with UNSAFE permissions, we do not have a lot of choices left.

You can replace a number XP's with External_Access code though.

Sure, if you don't count that they will do less and run slower. That may not matter to MS, but it matters to us.

The big problem is that people don't want to work in the confines of the approved assemblies in SQLCLR.

No, the big problem is that these restriction are unreasonably constraining, in the extreme and there is virtually NO help to be had in working around them. You will note, for instance, that in the case of the present thread, the only help that you hav egiven to OP is to tell him that he cannot/should not do what he is trying to do. He has received any help in acheiving his ultimate goal ("need use httputilizie to encode some special chars"). There does not seem to me to be anything unreasonable in that goal and yet no one has any help for him!

You can solve many problems by first sticking to the approved code base, and then if you need something in an unapproved assembly, you may need to write a little bit of code to abstract certain parts of logic into a safe implementation for SQLCLR, rather than trying to register an unsafe assembly into SQL.

Um, no, in my experience this rarely ever works or is possible. But I am willing to be proven wrong. Please, tell my how to safely use SQLCLR to interact with an Oracle server?

I still stand by the old rule of thumb that procedural business logic belongs in a middle tier outside of the database.

But that is not the "old" rule. The old rule was "procedural business logic belongs in a middle layer." That bit about the "tier outside of the database" was only added several years ago, and primarily by the ORM vendors.

The fact is, practical problems where the correct answer is "SQLCLR" are few and far between. And the principal reason for that are the extreme barriers that Microsoft have placed in front of its usage, both in terms of performance and functionality.

[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]