Post reply

Drop Extended Stored Procedure

  • November 14, 2004 at 8:08 pm

    #85986

    Hi,

    I need to Drop one of my Extended procedures and delete the .DLL file.

    I can drop it from the sql server using the following syntax.

    use master

    exec sp_dropextendedproc 'xp_startexe'

    but still cannot delete the .dll file unless i restart the SQL server.

    According to the BOL we should be able to unload the DLL from the SQL server memory using the following syntax.

    DBCC xp_startexe (FREE)

    But even after that the .DLL file is still in use.

    Can somebody help me on this please?

     

  • November 14, 2004 at 8:34 pm

    #530066

    Try unregistering the DLL, type:

    regsvr32 '[enter full dll file path and name]' /u

    from a DOS prompt and then reboot and try deleting it again.

    Phil

    If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

  • November 14, 2004 at 9:18 pm

    #530069

    I want to do it without rebooting the Machine or SQL server. And RegSvr32 doesn't work since it's not a Registered DLL with Windows.

    This is what BOL says

    Microsoft® SQL Server™ loads an extended stored procedure DLL as soon as a call is made to one of the functions of the DLL. The DLL remains loaded until the server is shut down or until the system administrator uses the DBCC statement to unload it. For example, this command unloads Xp_hello.dll, allowing the system administrator to copy a newer version of this file to the directory without shutting down the server:

    DBCC xp_hello(FREE)

  • November 15, 2004 at 5:09 am

    #530084

    So why dont you try restarting SQL Server and delete it.?

  • November 15, 2004 at 3:00 pm

    #530187

    Assume restarting 50 production SQL servers to update a Extended Stored procedure. That's the main reason i cannot restart the SQL server.

  • November 16, 2004 at 6:45 am

    #530261

    Hi Bimal

    Have you checked to see if the dll is actually loaded with sp_helpextendedproc ?

    Also, a shot in the dark, I noticed that there is no reference to dropping the extended proc before unloading the dll, perhaps this is worth a shot.

    Do you get any error messages when you try to unload the dll (2572) and do you have the correct permissions (sysadmin or db_owner)?

    Max

    Max

  • November 16, 2004 at 2:50 pm

    #530366

    Hi Max,

    Yes I can see the DLL reference when i run the sp_helpextendedproc. And I ran the sp_dropextendedproc to drop it as well. it's working fine. then i tried to unload it with DBCC (dllname) FREE command and it didn't give any error messages but still i cannot delete the dll physically.

    But following workaround worked..

    I did all above and copy my new dll into a different location and registered that with the same name. It worked very well and i have all the new functionality but still the old DLL cannot be deleted.

    I tried all the combinations of those commands to get this done without restarting the SQL server but failed.

    I don't want to place my dlls in different places either so above fix is not something i want to continue with.

  • November 17, 2004 at 12:27 am

    #530414

    Assume restarting 50 production SQL servers to update a Extended Stored procedure. That's the main reason i cannot restart the SQL server.

    Once in a while you have to patch you OS's and this often results in a rebood. Why not combining these two ( scheduled ).

    GKramer

Viewing 8 posts - 1 through 7 (of 7 total)

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