I noticed that MSDN contains sp_OAStop, which shuts down the OLE environment that starts inside SQL Server the first time you call sp_OACreate. But I can’t figure out what sp_OAStop is good for, except that maybe using OLE Automation consumes resources that could be freed by calling sp_OAStop.
Please tell me any more about this. Will using sp_OACreate slow down my server? Should I call sp_OAStop at the end of any stored procedure that uses sp_OACreate?
The sp_OAStop procedure shuts down the OLE automation execution environment. I can’t think of a time that you’ll need to call this unless you’re doing server troubleshooting, because all the clients that use the OLE automation stored procedures share that environment.
I don’t know the exact resource usage of these stored procedures but if you have appropriate server hardware it seems unlikely that these procedures would call problems with resources. Even if you call sp_OAStop the next time sp_OACreate is called, the execution environment restarts. Additionally, if sp_OAStop is called and any clients are in the process of issuing OLE automation statements they’ll receive an error