0

Update of the issue.

The exact error is Failed to initialize the Common Language Runtime (CLR) v2.0.50727 with HRESULT 0x80131022. You may fix the problem and try again later.

The error is happening on my local instance of SQL Server 2008r2 in a sp that calls a linked server (another sql server 2008r2) via an OPENQUERY call. I am executing that sp from my local instance. The code that calls the linked server fails but only when geospatial data (geometry) is included in the select part of the statement( the statement called in the open query.) When there is no geospatial data in the select statement to the linked server it executed fine. So geospatial data over a linked server connceting causes the error.

I should point out, but this may be a red herring, that the same stored procedure was called from an sql task in an ssis package and it passed. It called the procedure on my local instance of sql server. But that is not the issue now. The issue is outlined above.

I should also point out that Microsoft called that particular error a Memory Pressure error.

3
  • 1
    What are the computer specs: OS version, memory, storage configuration? Which updates are installed on OS and Sql Server?
    – vonPryz
    Jul 25, 2016 at 5:56
  • I would start by asking what is total memory present on machine and what have you given in max server memory. Also what is output of select @@version
    – Shanky
    Jul 25, 2016 at 6:34
  • I am using Windows 10 with 15.9 GB usable installed RAM. It is a 64-bit Operating System. The SQL Server version is 2008r2, version 10.50.6220.0 which I think is is service pack 3 updates. The Operating System on SQL Server is 6.3.10586. Select @@Version confirms this. Jul 25, 2016 at 22:18

2 Answers 2

4

The "memory" being referred to in the term "memory pressure" is physical RAM, not hard-drive space.

What exactly are you attempting to do when you get this error? It seems that it could be due to attempting to invoke CLR code from within an sp_OA* OLE-Automation stored procedure call. That is documented in the following Microsoft forum thread: CLR initialization failed with hresult 0x80131022.

According to the following Microsoft KB article (# 2003681), it could be due to actual memory pressure: Possible causes of "Failed to initialize the Common Language Runtime (CLR)" when using SQL CLR. Memory pressure refers to running low on physical RAM as that is what SQL Server prefers to use. For doing stuff in SQLCLR, these types of errors were much more common prior to SQL Server 2012, especially on 32-bit systems. Are you on a 32-bit system?

Also, in this Microsoft forum thread (related to SQL Server 2008 R2) -- Failed to enter Common Language Runtime (CLR) with HRESULT 0x80131022. This may due to low resource conditions -- Bob Beauchemin states:

You can increase the amount of memory available to SQLCLR on 64-bit systems by decreasing SQL Server's max_server_memory setting. You can monitor the amount of memory used by SQLCLR with a sum of sys.dm_os_memory_objects where type like '%CLR%' and the appropriate perfmon counters in the .NET CLR memory series.

For more information on SQL Server memory architecture (for SQL Server 2005, 2008, and 2008 R2), please see the following articles:

6
  • I am attempting to exec a stored procedure directly form SQL. Previously I had run it from an SSIS package and there were no issues. Jul 25, 2016 at 22:20
  • I should add that I have isolated the error to a query on a linked server. Jul 26, 2016 at 1:03
  • @EmptyWaterHoles You need to provide more detail. Is the stored procedure a SQLCLR one? When you talk about running it "direct" vs "from SSIS", do you mean that when running from SSIS, you connected to the remote server directly and did not use a Linked Server, but "directly from SQL" needs the Linked Server? Which server is reporting the error? Can you please update the question with the exact error message? Thanks Jul 26, 2016 at 1:25
  • I have edited and updated the question and changed the title. Jul 26, 2016 at 1:54
  • @EmptyWaterHoles I see the updates. Thanks. Can you please post the query that uses the Linked Server? Are you doing a SELECT * FROM OPENQUERY or using OPENQUERY in a JOIN? Are you saying that the SSIS package called the exact same stored procedure on the exact same instance and it did have the geometry data in the SELECT, and it worked? Was the SSIS package logging onto your local instance as the same login that you are using? Jul 26, 2016 at 2:02
0

This might be a little more of a satisfactory answer. The problem was solved by initialising a Spatial function that calls a CLR assembly. After that the query works. I know that that was the issue because when I restore the database in question the problem comes back and the solution is as described above. I can call any spatial function that calls a CLR assembly and that initialised the CLR and that is the solution. Thanks for your help.

Here is the code that was causing the problem.

SELECT c.ClientKey, mas.Id, mas.UniqueId ,mas.ObjectType ,mas.ObjectSubType ,mas.Status, mas.Number, mas.Name, mas.Owner, mas.Coverage, mas.Location, mas.LastMaintainedDate, mas.NextScheduledDate, mas.WorkOrder, mas.IsActive, mas.CreateDate, mas.UpdateDate
        FROM    OPENQUERY(PIPELINE, 'SELECT
                        o.ClientId,
                        o.Id,
                        o.UniqueId,
                        ot.Description as ObjectType,
                        ost.Description as ObjectSubType,
                        o.Status,
                        o.Number,
                        o.Name,
                        o.Owner,
                        ma.Coverage,
                        ma.Location,
                        ma.LastMaintainedDate,
                        ma.NextScheduledDate,
                        ma.WorkOrder,
                        o.IsActive,
                        o.CreateDate,
                        o.UpdateDate
                can't show table due to sensitivity

Coverage and Location were geometry types.

1
  • Something doesn't seem right about this "fix". Based on your last comment on my answer, you said that running select * from sys.dm_clr_properties returned no rows. Is that true of each time that you get this error? That should generally never happen, and should point to a problem elsewhere. Also, how many times did you run this query when it was getting an error (i.e. no pre-initializing via a "dummy" call)? Just once or did you wait a few seconds and try again? If the AppDomain isn't loaded, the first execution will take a little bit longer. Jul 26, 2016 at 6:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.