How to allow the execution of the SQL CLR function in a parallel query plan, as well as have permissions to access data

Question

I have written several CLR SQL functions (UDFs) that read data from an external DB2 database hosted on IBM iSeries (using the IBM DB2.Net provider). For the function to have the necessary permissions to read this data, I need to decorate the function with the SqlFunction attribute, whose DataAccess property is set to DataAccessKind.Read. I also deploy the assembly as UNSAFE.

Reading data from a DB2 database is relatively slow (for example, 3 ms for the simplest ExecuteScalar).

I use these UDFs to efficiently combine data from a DB2 database into Sql Server views.

For example, suppose my UDF is defined as

[SqlFunction(DataAccess = DataAccessKind.Read, IsDeterministic = true)] 
public static SqlMoney GetCostPrice(SqlString partNumber)
{
    decimal costPrice;
    // open DB2 connection and retrieve cost price for part
    return new SqlMoney(costPrice);
}

SQL :

select Parts.PartNumber,
       dbo.GetCostPrice(Parts.PartNumber) as CostPrice
from Parts

, SQL- .

, , , , SQL Server, , SQL CLR DataAccess = DataAccessKind.None.

DataAcessKind None, DbConnection .

! UDF , ?

, , DataAccess = DataAccessKind.None SqlFunction, , , , DbConnection.

, ?

    [SqlFunction(DataAccess = DataAccessKind.None, IsDeterministic = true)]
    public static SqlMoney TestFunction()
    {
        var sqlPerm = new SqlClientPermission(PermissionState.Unrestricted);
        sqlPerm.Assert();

        using (var conn = new SqlConnection("context connection=true"))
        {
            conn.Open();
        }

        return new SqlMoney();
    }

Sql Server Management Studio :

select dbo.TestFunction()

...

.NET Framework "TestFunction": System.InvalidOperationException: . - , , DataAccessKind.Read SystemDataAccessKind.Read, FillRow UDT. System.InvalidOperationException:   System.Data.SqlServer.Internal.ClrLevelContext.CheckSqlAccessReturnCode(SqlAccessApiReturnCode eRc)   System.Data.SqlServer.Internal.ClrLevelContext.GetCurrentContext(SmiEventSink sink, Boolean throwIfNotASqlClrThread, Boolean fAllowImpersonation)   Microsoft.SqlServer.Server.InProcLink.GetCurrentContext(SmiEventSink eventSink)   Microsoft.SqlServer.Server.SmiContextFactory.GetCurrentContext()   System.Data.SqlClient.SqlConnectionFactory.GetContextConnection( SqlConnectionString, Object providerInfo, DbConnection owningConnection)   System.Data.SqlClient.SqlConnectionFactory.CreateConnection( DbConnectionOptions, Object poolGroupProviderInfo, DbConnectionPool, DbConnection owningConnection)   System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)   System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)   System.Data.ProviderBase.DbConnectionClosed.OpenConnection( DbConnection, DbConnectionFactory connectionFactory)   System.Data.SqlClient.SqlConnection.Open()   UserDefinedFunctions.UserDefinedFunctions.TestFunction()

- ?

.

(btw, SQL 2008 .Net 3.5)

Answer 1

( SqlConnection SQL Server), / (.. Context Connection = true) Enlist , false:

Server=DB2; Enlist=false;

Context Connection = true . , - . ? , Enlist true, / , Enlist=false;,

.

.

, , , Linked Server, , "The Db2 Providers ( MS) ".

, TransactionScope Suppress . , TransactionScope ( : Required, RequiresNew Suppress), DataAccess SystemDataAccess None ( ).

,

UserDataAccess UDF .

, UserDataAccess . , CREATE FUNCTION (, AS EXTERNAL NAME [Assembly]... . UserDataAccess SystemDataAccess , . OBJECTPROPERTYEX:

SELECT OBJECTPROPERTYEX(OBJECT_ID(N'SchemaName.FunctionName'), 'UserDataAccess');

---

, :

• , Enlist, false , DataAccess Read, ( DB2 iSeries iSeries Microsoft ADO.NET), :

  • OleDb
  • ODBC
  • IBM DB2 LUW.NET

• , -, SQLCLR , , . SQLCLR , - ( , ) ( 1 - 5 ). , , .