August 30, 2015 at 2:40 pm
Hi,
I have created a UDF plugin for sqlserver 2008. That plugin calls my native c++ dll.
The method declaration is as follows:
[DllImport("Library64.dll", CallingConvention = CallingConvention.Cdecl, CharSet = CharSet.Ansi)]
public static extern IntPtr GetTiming();
Everything works great unless I want to amend some changes to that native dll and overwrite the file again..
It is locked. I cannot do anything unless I restart the sqlserver process (In production it is not an option).
Unloading the AppDomain, setting database trustworthing off and on doesn't work ...
Is there any way (command) to release (FreeLibrary) from sqlserver process?
or how should I rewrite the c# UDF code?
August 30, 2015 at 3:04 pm
It should be noted that this question has been cross-posted on Stack Overflow:
How to unload native dll (called in clr udf own function) from SQL Server process?
John, I posted another suggestion on the Stack Overflow question. Which is:
sp_configure 'clr enabled', 0;
RECONFIGURE;
GO
sp_configure 'clr enabled', 1;
RECONFIGURE;
I am also still looking into other options.
SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR
August 30, 2015 at 3:07 pm
Nothing helped unfortunately ....
August 31, 2015 at 1:43 am
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 ...
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply