Here is what I have done and what seems to work. Please tell me if it is wrong to do this..
I have added two additional CLR storedprocedure like these:
[SqlProcedure]
public static void asdUnloadLibrary()
{
try
{
var hMod = IntPtr.Zero;
if (GetModuleHandleExA(0, "Engine64.dll", ref hMod))
{
while (FreeLibrary(hMod))
{ }
}
else
{
throw new Exception("Library not found");
}
}
catch (Exception e)
{
throw e;
}
return;
}
[SqlProcedure]
public static void asdLoadLibrary()
{
try
{
var hMod = IntPtr.Zero;
LoadLibrary("Engine64.dll");
}
catch (Exception e)
{
throw e;
}
return;
}
Now ... In case I want to copy to server a new native DLL file I will:
1) Execute asdUnloadLibrary stored procedure. This will unload the dll.
2) Then, I can copy another version of dll to system folder
3) Then I can (but I think it is not necessary) do it:
ALTER DATABASE TEST SET TRUSTWORTHY OFF
GO
ALTER DATABASE TEST SET TRUSTWORTHY ON
GO
4) Execute asdLoadLibrary
And Now the original UDF function works again as expected ...