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

Question

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

Viewing 4 posts - 16 through 18 (of 18 total)

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

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

rbarryyoung (7/20/2008)
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 have two webservice consumers in CLR that work just fine in EXTERNAL_ACCESS only. I have rewritten a couple of the built in XP's in CLR as TVF, like xp_ReadErrorLog and xp_RegRead, and not as a wrapper around the old logic like the existing examples on my site show, as true replacements.

If you have written XP's in the past, then you accepted that the code was potentially unsafe, so you probably are willing to load an unsafe assembly to accomplish what you are after. The key difference is that an unsafe assembly in CLR is not as likely to take the SQL Instance down in the event of a memory leak. This is not to say it can't happen, since CLR runs in the SQLOS, and uses memory from the MemToLeave area, you will get assembly unloads before the SQL Instance gets pulled down in most cases.

For this particular thread, you can abstract the logic of the HtmlEncode using the .NET Reflector, and rewrite it in a SAFE implementation. The problem with it is that it uses Pointers in parsing the string which are unsafe in CLR in general. The SAFE implemenation can be done using a char[], and an safe int pointer to move through it. Slightly slower, but not enough that you can really notice it, even on extremely large strings. I wrote an example this morning that can be found on the following link:

http://www.sqlclr.net/Examples/tabid/55/articleType/ArticleView/articleId/30/Default.aspx

I also have a TSQL HTMLEncoder that replaces the most common characters and worked for years for encoding data that would later be consumed by the web in SQL 2000. It isn't as robust, and it doesn't cover the gamit of HTML Escape code that is out there, but it could definately be improved upon since there are tables out on the web that show what all the escape codes are. I am sure that Jeff could produce a table in TSQL that would do the replaces rapidly using a Tally Table inline in a query.

Provide an example of what you want to do with Oracle from CLR, that you can't do with a Linked Server or OpenDataSource command in TSQL, and I'll see what I can do about helping with it.

rbarryyoung (7/20/2008)
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.

This fact was the case for XP's as well, so I don't see the problem here if CLR is supposed to replace XP's. I read on one of the MVP blogs the statement that unless you would have written an XP in the past, you shouldn't be trying to write a CLR solution, and more and more I am in agreement with this. The fact that people know CLR, and it is easy to use, has made its use in SQL become more prolific, and to the point that it is abused more often than not. It isn't a panacea, but it is often treated as if it is.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 2

Jonathan Kehayias (7/21/2008)
Provide an example of what you want to do with Oracle from CLR, that you can't do with a Linked Server or OpenDataSource command in TSQL, and I'll see what I can do about helping with it.

Great! I want to reuse the customer's client routines (c#) for inserting or updating records into their 3rd party application's Oracle database. But I can get away without that. What I need to do is to call the application's Oracle stored procedures to insert (or update) new information from a SQL Server database. What I cannot do is bypass the application stored procedures to modify the database (either data or metadata).

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

What version of Oracle and is SQL running on x64 or x86?

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Jonathan Kehayias (7/21/2008)
What version of Oracle and is SQL running on x64 or x86?

Oracle 10g. SQLServer 2005, x86 for development and test, x64 is the production target.

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