October 26, 2005 at 7:02 am
I have a VB COM dll sitting on my sql server that I call using sp_OACreate etc. However I now need to update the dll but find I can't as sql server is holding it open. I do use sp_OADestroy at the end of my t-sql code, but an untrapped error may have gotten through at some stage to cause this.
Does anyone know how I can kill off sql server's hold on the COM dll without restarting the sqlserver service (unfortunately necessary as its a production environment)?
October 26, 2005 at 12:10 pm
sp_OAStop will stop the COM execution layer, which should release all open references to your library - and every other active in-proc COM call as well. See BOL for caveats; any connections using the shared OLE Automation layer will report errors. This procedure will effectively kill all active OA calls, so be aware of what is going on elsewhere when you do it.
The layer will restart automatically with the next call to sp_OACreate, so you may need to take extra steps to ensure your library isn't immediately instantiated by another connection, depending on how often it gets called, as soon as you get it killed off.
Moving forward, Ken Henderson's "The Guru's Guide to SQL Server Stored Procedures, XML, and HTML" has a chapter on SQL Server and COM Automation with some great info, including a suggestion to build your COM libraries as COM Servers (.exe files instead of .dll), which can be explicitly called out-of-process by setting the optional third parameter context of sp_OACreate to 4 (Local (.exe) OLE Server only). COM servers running out-of-process can be killed off (task manager, etc.) outside of SQL Server without affecting any other running process, except for the active connection holding the ref you kill off.
There are some performance and resource trade-offs to running out-of-proc instead of in-proc, but it does offer more control.
[insert usual block about testing, etc. here]
-Eddie
Eddie Wuerch
MCM: SQL
October 27, 2005 at 2:59 am
Excellent, thanks for your help Eddie. I'll give the sp_OAStop a try when its a bit quieter on the system.
The out of process .exe option seems a much better idea, think I'll switch to that too.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply