SQL CLR

ALTER DATABASE SqlClrDB SET TRUSTWORTHY ON

DECLARE @path varchar(max);
SET @path = 'D:\'

CREATE ASSEMBLY [Assembly1] from (@path+'Assembly1.dll') 
WITH PERMISSION_SET = UNSAFE


CREATE ASSEMBLY [Assembly2] from (@path+'Assembly2.dll')
WITH PERMISSION_SET = UNSAFE

Assembly 'Assembly2' references assembly 'assembly1, version=0.0.0.0, culture=neutral, publickeytoken=null.', which is not present in the current database. SQL Server attempted to locate and automatically load the referenced assembly from the same location where referring assembly came from, but that operation has failed (reason: version, culture or public key mismatch). Please load the referenced assembly into the current database and retry your request.


I have faced with this problem and the root of this issue was that I am runing x64 SQL Server and Assembly1 was targeted to x86 platform and Assembly2 - Any CPU

I have made test table so you can see what is the big picture:

A1 \ A2	x86	x64	Any CPU
x86	X	V	X
x64	V	V	V
Any CPU	V	V	V

As you can see in most cases we could import libraries in to SQL Server even if you could not rebuild A1 library x64 library.

Create External Procedure

I decided to test first what will happend if we throw exception in code:


//C#
[SqlProcedure]
public static void TestTrowEvaluationException()
{
      throw new Exception("TestTrowException");
}

-- SQL
CREATE PROCEDURE [dbo].[TestTrowException] 
AS EXTERNAL NAME [ExpressionEvaluationSQLCLR].[ExpressionEvaluationSQLCLR.ExpressionEvaluationProcedures].[TestTrowEvaluationException]
go

/*
 The syntax of name is easy
[Assembly_Name].[Namespace.Class].[MethodName]
*/

Msg 6522, Level 16, State 1, Procedure TestTrowEvaluationException, Line 0
A .NET Framework error occurred during execution of user-defined routine or aggregate "TestTrowEvaluationException": 
ExpressionEvaluation.ExpressionVisitor.EvaluationException: TestTrowException
ExpressionEvaluation.ExpressionVisitor.EvaluationException: 
   at ExpressionEvaluationSQLCLR.ExpressionEvaluationProcedures.TestTrowEvaluationException()
.