Partager via

Leaky code?

  • Article

Here's an excerpt from an sp_OA proc code example in the SQL Server 2000 Books Online:

DECLARE @object int
DECLARE @hr int
DECLARE @property varchar(255)
DECLARE @return varchar(255)
DECLARE @src varchar(255), @desc varchar(255)

-- Create an object.
EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

-- Set a property.
EXEC @hr = sp_OASetProperty @object, 'HostName', 'Gizmo'
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

/* other code */

-- Destroy the object.
EXEC @hr = sp_OADestroy @object
IF @hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @object, @src OUT, @desc OUT
SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc
RETURN
END

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.

Comments

  • Anonymous
    October 14, 2005
    Just to take this a little bit further, can we infer that calling sp_OADestroy is actually not necessary in most cases, given that the object will be destroyed anyway? Or perhaps I should rephrase this the other way around: When do we need to explicitly call sp_OADestroy?
  • Anonymous
    October 14, 2005
    You should always call sp_OADestroy to free up objects you create when you're done with them. The clean-up code inside the server is just a safety net. This is especially true if you have a batch that runs for a long time after creating and using COM objects. If the objects don't need to be left lying around in memory, go ahead and get rid of them and free up the resources. Failing to do this could cause undo memory pressure and even negatively affect performance of your code or that of other connections in extreme cases.
  • Anonymous
    June 01, 2009
    PingBack from http://paidsurveyshub.info/story.php?id=62846