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=22.214.171.124, 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.