I think I got my December update too early. There's an update of the SQL Server 2005 samples on the downloads website. I have (thought I had) the December update, this one actually says "Date published 1/4/2006" and its 20mb instead of the old once (just under 16mb).
The site says: Since its original release, new samples have been added for the following technologies: CLR, SMO, Integration Services, Replication, and Reporting Services. See the SQLServerDatabasesAndSamplesOverview.htm file for descriptions of the new and original samples.
Well, the samples folks (thanks Bonnie and everyone) have really outdone themselves this time. Thanks!
One of the samples that particularly intruiged me was a TVF that returned a resultset from Oracle using System.Data.OracleClient. I was surprised because, although System.Data.OracleClient is on the "approved assemblies" list, the DDL cataloged the assembly as UNSAFE. Hmmm…thought I'd only need EXTERNAL_ACCESS.
So I fired up my combined VPC of SQL Server 2005/Oracle 10g and decided to see for myself. When cataloged as EXTERNAL_ACCESS I got:
Msg 10314, Level 16, State 11, Line 1
An error occurred in the Microsoft .NET Framework while trying to load assembly id 65541. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error:
System.IO.FileLoadException: Could not load file or assembly 'OracleTVF, Version=1.0.0.0, Culture=neutral, PublicKeyToken=65071e111a9fd000' or one of its dependencies. Failed to grant minimum permission requests. (Exception from HRESULT: 0x80131417) —> System.Security.Policy.PolicyException: Required permissions cannot be acquired.
When cataloged as UNSAFE, it worked fine. Well, after I changed it to access the SCOTT.DEPT table. I don't have the "titles" table the sample referred to.
The reason for this (UNSAFE) is most likely that System.Data.OracleClient (which is approved) needs to load Oracle's client libraries. These are written in unmanged code, which makes them unsafe to load, even if System.Data.OracleClient isn't unsafe. BTW, I didn't try ODP.NET. This would have required cataloging my DLL as UNSAFE because Oracle.OracleClient.dll provider isn't on the approved list. But I'm running UNSAFE already. Oh.
One thought on “New samples; Calling Oracle from SQLCLR”
Hi,
Your post is very interesting.
I am currently facing a very very similar situation.
I have developed a SharePoint application which operates a number of searches on an Oracle Database. Everything went fine until I tried to deploy to the site.
The OracleClient assembly could not be loaded. No further explanations were given but after reading your blog I am quite sure that I am facing the same problem as you did.
The one problem that I am facing is that I have no idea where and how to change the permission set for this dll. You mentioned "when cataloged as unsafe, it worked fine". How do you catalog it as unsafe?
Your help is greatly appreciated!
Cheers
Fred
Comments are closed.