How to unload native dll, being called from c# .net UDF plugin?

  • Wojtek-436581

    SSC-Addicted

    Points: 448

    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?

  • Solomon Rutzky

    SSCoach

    Points: 16256

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

  • Wojtek-436581

    SSC-Addicted

    Points: 448

    Nothing helped unfortunately ....

  • Wojtek-436581

    SSC-Addicted

    Points: 448

    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 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply