Here's an excerpt from an sp_OA proc code example in the SQL Server 2000 Books Online:
DECLARE @object intDECLARE @hr intDECLARE @property varchar(255)DECLARE @return varchar(255)DECLARE @src varchar(255), @desc varchar(255)-- Create an object.EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUTIF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURNEND-- Set a property.EXEC @hr = sp_OASetProperty @object, 'HostName', 'Gizmo'IF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURNEND
/* other code */
-- Destroy the object.EXEC @hr = sp_OADestroy @objectIF @hr <> 0BEGIN EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURNEND
Question: Does this code leak the SQLServer reference contained in @object if there's a problem setting the HostName property? After all, in the case of a non-zero HRESULT being returned by sp_OASetProperty, we ultimately call RETURN -- so does that mean the @object reference is leaked? Also, what if a batch-aborting error such as a bad CONVERT() call occurs between the sp_OACreate and the sp_OADestroy call? Is the reference to the SQLServer object simply leaked?
No. Why not? Because the sp_OA procs track the objects they create and install an ODS post event handler that frees all of them when the batch exits. So, every time you create an object using sp_OACreate, it's added to a list. When you call sp_OADestroy, the object is taken off of the list. When the containing batch completes (for whatever reason -- successfully, due to an error -- whatever), the ODS post event batch handler kicks in and frees any objects that are still in the list.
This has a couple of important ramifications. The first is, of course, that COM objects can't be leaked indefinitely. When the batch that created them ends, they're freed. Second, you can't share a created object across batches. If you store the reference returned by sp_OACreate in a table, then later retrieve it from another batch, the object it points to will be long gone.
This mechanism is just SQL Server's way of providing some very basic clean up for user objects created within its process space. External consumers of the MemToLeave pool are notorious troublemakers within the server, so it makes sense to have some type of rudimentary tracking and clean up facility for them.
Note that you could theoretically use post event handlers yourself (e.g., in an xproc), but these have been deprecated since SQL Server 2000, and support for them has not been included in the xproc header files since 7.0. This is the reason I didn't make use of them in the chapter on xproc arrays in my book The Guru's Guide to SQL Server Stored Procedures, XML, and HTML.