My procedure creates a COM object with sp_OACreate. Then it uses the sp_OAMethod to call a "load" method of the underlying DLL. Finally it uses sp_OADestroy to dispose the COM object.
The procedure works perfectly fine the first round.
During the second round of execution, sp_OACreate succeeds. But sp_OAMethod returns insufficient memory error. I was under the impression that sp_OADestroy would have released the memory, but that doesn’t seem to be the reality.
I tried the following commands after the first round of execution
DBCC FREESYSTEMCACHE (‘ALL’)
But none of them (nor all of them together) help.
The only way out is to restart the SQL Service after each round of execution.
Is there a way around this?