Drop Extended Stored Procedure

Question

Post reply

Drop Extended Stored Procedure

Bimal Fernando

SSCommitted

Points: 1945
More actions
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?

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

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

Phil Parkin SSC Guru Points: 246959 More actions · Answer 1

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

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Bimal Fernando SSCommitted Points: 1945 More actions · Answer 2

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)

Rosh K Mathews SSC Enthusiast Points: 175 More actions · Answer 3

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

Bimal Fernando SSCommitted Points: 1945 More actions · Answer 4

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

Max-146500 SSCarpal Tunnel Points: 4691 More actions · Answer 5

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

Bimal Fernando SSCommitted Points: 1945 More actions · Answer 6

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.

Guus Kramer Ten Centuries Points: 1031 More actions · Answer 7

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