Geek Answers Handbook

SQL CLR Project Deployment Fails While Creating Assembly in Database

I have a folder with 3 DLL files in a folder on the server that I use to create the assembly. First I tried the following code and received an error message saying that it could not find the system.data.datasetextensions.dll file on the server, and I copied and pasted the dll from my computer to the same folder where my clr project was located and tried again to execute the command.

Create Assembly OoplesCLR from 'c:\ooplesclr\OoplesFinanceCLR.dll' with Permission_set = SAFE
GO

Now I get this error after copying the dll from my computer to the server folder

Warning: The Microsoft .NET Framework assembly 'system.data.datasetextensions, version=4.0.0.0, culture=neutral, publickeytoken=b77a5c561934e089.' you are registering is not fully tested in the SQL Server hosted environment and is not supported. In the future, if you upgrade or service this assembly or the .NET Framework, your CLR integration routine may stop working. Please refer SQL Server Books Online for more details.
Msg 6218, Level 16, State 2, Line 1
CREATE ASSEMBLY for assembly 'OoplesFinanceCLR' failed because assembly 'System.Data.DataSetExtensions' failed verification. Check if the referenced assemblies are up-to-date and trusted (for external_access or unsafe) to execute in the database. CLR Verifier error messages if any will follow this message
[ : System.Data.DataRowComparer::get_Default][mdToken=0x6000001][offset 0x00000000] Code size is zero.
[ : System.Data.DataRowComparer`1[TRow]::Equals][mdToken=0x6000004][offset 0x00000000] Code size is zero.
[ : System.Data.DataRowComparer`1[TRow]::GetHashCode][mdToken=0x6000005][offset 0x00000000] Code size is zero.
[ : System.Data.DataRowComparer`1[TRow]::.cctor][mdToken=0x6000006][offset 0x00000000] Code size is zero.
[ : System.Data.DataRowComparer`1[TRow]::.ctor][mdToken=0x6000002][offset 0x00000000] Code size is zero.
[ : System.Data.DataRowComparer`1[TRow]::get_Default][mdToken=0x6000003][offset 0x00000000] Code size is zero.
[ : System.Data.DataTableExtensions::CopyToDataTable[T]][mdToken=0x6000008][offset 0x00000000] Code size is zero.
[ : System.Data.DataTableExtensions::CopyToDataTable[T]][mdToken=0x6000009][offset 0x00000000] Code size is zero.
[ : System.Data.DataTableExtensions::CopyToDataTable[T]][mdToken=0x600000a][offset 0x00000000] Code size is zero.
[ : System.Data.DataTableExtensions::AsDataView[T]][mdToken=0x600000c][offset 0x00000000] Code size is zero.
[ : System.Data.DataTableExtensions::AsEnumerable][mdToken=0x6000007][offset 0x00000000] Code size is zero.
[ : System.Data.DataTableExtensions::AsDataView][mdToken=0x600000b][offset 0x00000000] Code size is zero.
[ : System.Data.EnumerableRowCollection::System.Collections.IEnumerable.GetEnumerator][mdToken=0x600000e][offset 0x00000000] Code size is zero.
[ : System.Data.EnumerableRowCollection::.ctor][mdToken=0x600000d][offset 0x00000000] Code size is zero.
[ : System.Data.EnumerableRowCollection`1[TRow]::System.Collections.IEnumerable.GetEnumerator][mdToken=0x600000f][offset 0x00000000] Code size is zero.

What am I doing wrong and how can I fix it?

UPDATE 1: I changed the database to validity and then ran this command and I get the same error:

DataSetExtensions 'c:\ooplesclr\System.Data.DataSetExtensions.dll' Permission_set = UNSAFE GO

2: . :

public partial class UserDefinedFunctions
{
[SqlFunction]
public static SqlString CalculateInfo()
{
    // first get data from the tables and then process the data
    getData();
    // Put your code here
    return new SqlString ("test");
}

3: - , , , ...

GO
CREATE FUNCTION [dbo].[CalculateInfo]
( )
RETURNS NVARCHAR (MAX)
AS
 EXTERNAL NAME [OoplesCLR].[UserDefinedFunctions].[CalculateInfo]

4: , , , :

Msg 6522, Level 16, State 1, Line 4

"CalculateInfo" .NET Framework: System.Security.HostProtectionException: , CLR.

The protected resources (only available with full trust) were: All
The demanded resources were: Synchronization, ExternalThreading

System.Security.HostProtectionException: 
   at UserDefinedFunctions.getData()
   at UserDefinedFunctions.CalculateInfo()

?

+1
1

-, .NET Framework. MSDN .NET Framework. System.Data.DataSetExtensions . .

- , . , , Microsoft , , .

, :

SELECT * FROM sys.assemblies sa WHERE sa.is_user_defined = 1;

. System.Data.DataSetExtensions , , , CREATE ASSEMBLY, DLL .NET Framework.

System.Data.DataSetExtensions CREATE ASSEMBLY, C:\Windows\Microsoft.NET\Framework ( Framework64). , , " ", DLL .

, , , -, SAFE. , , , , :

  • , , TRUSTWORTHY :

    ALTER DATABASE [DatabaseName] SET TRUSTWORTHY ON;
  • WITH PERMISSION_SET = UNSAFE

:

USE [DatabaseName];

ALTER DATABASE CURRENT SET TRUSTWORTHY ON;

CREATE ASSEMBLY [System.Data.DataSetExtensions]
FROM
   'C:\Windows\Microsoft.NET\Framework64\v4.0.30319\System.Data.DataSetExtensions.dll'
WITH PERMISSION_SET = UNSAFE;

, , , UNSAFE.

System.Data.DataSetExtensions.dll, .

SQLCLR . , SQL Server Central: Stairway to SQLCLR ( ).

+3

More articles:


All Articles