3

I have a F# DLL (.NET Framework 4.5.1, F# 4.1, FSharp.Core 4.4.3.0). The only NuGet added reference is to FSharp.Data.SqlProvider 1.1.41.

The code is in one file only

module DB

open FSharp.Data.Sql

[<Literal>]
let private dbVendor = Common.DatabaseProviderTypes.MSSQLSERVER

Build goes without errors

Import the built dll in SQL server as assembly

CREATE ASSEMBLY [Library2] FROM '<path>\Library2.dll' WITH PERMISSION_SET = UNSAFE

Operation fails with error

Assembly 'Library2' references assembly 'fsharp.core, version=4.3.1.0, culture=neutral, publickeytoken=b03f5f7f11d50a3a.', 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

Now comment the last two rows in the code

//[<Literal>]
//let private dbVendor = Common.DatabaseProviderTypes.MSSQLSERVER

With this the SQL assembly creation succeeds, the assembly is correctly created in SQL server

Finally, if I downgrade the project to F# 3.1 (FSharp.Core 4.3.1.0), then no error in both cases

SQL Server 14.0.1000

Windows 10 Pro

Please note that an issue has already been raised in the project GitHub repo: Issue #541

Questions

  • Does anyone see something wrong in what I am doing?
  • Has anyone incurred in such an issue and solved it? If yes, how?
Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
Franco Tiveron
  • 2,364
  • 18
  • 34

1 Answers1

0

F# is not a natively supported SQLCLR language. I believe only C#, VB.NET, and Visual C++ are (and of course, IL if you are into writing it directly and not using a silly compiler ;).

So, as the error message states, you need to manually load that F# DLL, marking it as UNSAFE.

Please see the following answer of mine, also on S.O., for additional details on using F# in SQLCLR:

F# with sqlclr in a reasonably safe way and scripted assembly

Solomon Rutzky
  • 46,688
  • 9
  • 128
  • 171
  • I have a similar DLL working well but using FSharp.Data.TypeProviders instead of FSharp.Data.SqlProvider. Downgrading to F# 3.1 I can get even this one to work fine. Hence it is possible that there is something to fix in the references used in the SQLProvider project, or maybe a way to import the assembly in SQL server overriding the standard way that SQL server uses to assess the imported assembly references – Franco Tiveron Apr 27 '18 at 04:51
  • Is it possible that in F# 3.1, the `FSharp.Data.SqlProvider` library simply did not reference `FSharp.Core`? And that in the newer version they added that reference for some new functionality? If so, there isn't much you can do about it, not without recompiling `FSharp.Data.SqlProvider` after removing any references to `FSharp.Core`. Else just manually load `FSharp.Core.DLL` (though that comes with a certain amount of risk, depending on what it's doing). – Solomon Rutzky Apr 27 '18 at 04:55
  • Also, regarding resolving of assembly references, you could load the Assembly as a VARBINARY literal, in which case it would not attempt to load dependencies. However, I am not sure that you would necessarily get much farther as I suspect it would try to verify upon execution. Still, might be worth a try. Download a copy of my open source [BinaryFormatter](https://github.com/SqlQuantumLeap/BinaryFormatter/releases) to easily convert the DLL(s) into the `VARBINARY` literal needed for `CREATE ASSEMBLY` :-) – Solomon Rutzky Apr 27 '18 at 05:00
  • @FrancoTiveron I just reviewed the issue on GitHub. I would try the binding redirect first, assuming that the option is handled at compile time and not runtime. If it is a runtime config option, then I don't think it will work as the DLL isn't on the filesystem. Also, have you already loaded `FSharp.Core.DLL` v 4.4.3 into SQL Server? I am curious as to why downgrading to FSharp.Core 4.3.1.0 works (which implies that 4.3.1.0 has been loaded into SQL Server), yet using FSharp.Core 4.4.3.0 complains about not finding v 4.3.1.0. – Solomon Rutzky Apr 27 '18 at 14:55
  • binding redirection was there since the beginning. I tried to pre-load FSharp.Core, both versions (3.1 and 4.3) with same outcome. Also, pre-loading isn't necessary as the dependent assemblies are automatically pre-loaded if found in the same directory. My suspect is that CREATE ASSEMBLY doesn't look at the .config when loading an assembly. If so, it simply parses the assembly and its dependencies. In my case, it finds that the master assembly references 4.3, so it loads it. After this, it finds SQLProvider as dependency and parsing it it finds a reference to 3.1. – Franco Tiveron Apr 27 '18 at 23:00
  • When i downgrade, the master assembly references the same 3.1 version as SQLprovider, which is loaded into SQL server automatically. Hence the process completes successfully – Franco Tiveron Apr 27 '18 at 23:03
  • @FrancoTiveron Did you ever make any progress on this? It occurred to me that it is possible that you might have been using the wrong config file. SQL Server uses a single config file, it's not per Assembly. It is `C:\Program Files\Microsoft SQL Server\MSSQL14.MSSQLSERVER\MSSQL\Binn\sqlservr.exe.config` where the **14** is the version number (SQL 2014 = **12**), and the **MSSQLSERVER** is the instance name (the default instance name being **MSSQLSERVER**). If you haven't tried putting the redirect there, try it and restart the SQL Server process to make sure that the change is picked up. – Solomon Rutzky May 24 '18 at 22:28
  • Tried, no change, I have also submitted the issue to the SQL server team; no answer so far – Franco Tiveron Dec 17 '19 at 05:39