Prev: Stored Procedures
Next: Reminder - Microsoft Responds to the Evolution of Community
From: Morris Neuman on 20 May 2010 23:10
Using VS 2008 to develop a C# CLR Stored Procedure for an SQL2005 DB
(Express). I can successfully run and debug the skeletal SP that prints
"hello world" but when I add the code below to open a connection to a MS
Access database file I get the following exception in the debugger:

"Request for the permission of type 'System.Data.Odbc.OdbcPermission,
System.Data, Version=2.0.0.0, Culture=neutral,
PublicKeyToken=b77a5c561934e089' failed."

I've ALTERED DATABASE SET TRUSTWORTHY TRUE and set the permission level of
the Assembly to EXTERNAL.

The Access database is not password protected.

The code used to access MS Access database:

public partial class StoredProcedures
{
[Microsoft.SqlServer.Server.SqlProcedure]
public static void SPHello3()
{
SqlContext.Pipe.Send("Hello world!\n");
string connectionString = "DSN=MS Access
Database;DBQ=C:\\CMData\\CALLMaster.mdb;DefaultDir=C:\\CMData;DriverId=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;UID=admin;";
//"Microsoft.Jet.OLEDB.4.0;Data
Source=C:\\CMData\\CALLMaster.mdb;User ID=;Password=;";
string queryString = "SELECT * FROM Audiotex";
try
{
using (OdbcConnection connectionMDB = new
OdbcConnection(connectionString))
{
connectionMDB.Open();

The connectionMDB.Open() throws the Exception.

I even ran CASPOL.exe AF <assembly name> as per the documentation.
All the components used is local on the same development machine.

What Do I need to get this to work.

--
Thanks
Morris
From: "Charles Wang [MSFT]" on 21 May 2010 03:20
Morris,
This issue is most likely caused by the fact that the assembly's permission
level is at Safe or External. ODBC driver need to call some unsafe
assemblies which requires that your assembly is also unsafe too. To resolve
this issue, please go to your CLR project properties window, select
Database, and change "Permission Level" to Unsafe.

In addition, you need to aware that for Access 2007 and its previous
version databases, there is no 64-bit Jet driver. You need to make sure
that your SQL Server instance is a 32-bit instance, otherwise you may
encounter an error of "data source name not found and no default driver
specified.".

Hope this helps.

Best regards,
Charles Wang

 | 
Pages: 1
Prev: Stored Procedures
Next: Reminder - Microsoft Responds to the Evolution of Community